JDBC search function ?

[Alvin chew]

hi, if i have 3 fields which use as search keys to find database's data in form,

say field 1 for company name , field2 for company code, and company registration no.

how can i make 3 fields which is flexible enough to detect what to search,

for example , if user keyin only company code , then the search function will baseon search criteria company code ,

if however, user keyin company name and company code, then search will based on two criteria.

and last question, the sql query to let user say keyin only a character like "A" in company code , then it will return all company code with "A" character include in whole company code from database

i have use following query in java code as follow but it give unnecessary return data

 
SELECT * FROM `mc_company` where mc_company_cd like + '%comcode%' + and (mc_comname like '%comname%' or mc_regno like '%comregno%')

comcode, comname and comregno is variable that user have keyin in form's search field.

thank you very much for guidance

[Srinivasa Raghavan]

Normally i followed this way.

StringBuffer condition = new StringBuffer();
 
if ( comcode != null )
 condition.append(" CompanyCode like " + comcode + " AND ")
 
if ( comname != null )
 condition.append(" CompanyName like " + comname + " AND " )
 
if ( comregno  != null )
 condition.append(" CompanyRegNo like " + comregno + " AND " )
 
condition.append( "1 = 1" )
 
String query = "Select fields from table where " + condition;

[Alvin chew]

thanks , Srinivasa , but why you put "1=1" ? why we need to include that ?

and if we have only have one field that user have keyin, then how to solve the query word "AND" ?

thank you

[Horatio Westock]

Hi,

I've been doing some similar searching, and the code I use is in this thread.. Some stuff is probably postgres specific, but the general idea is the same.

No idea if it's a good way to go about it, but it does work.

Good luck.

[Jeanne Boyarsky]

Alvin,
The "1=1" is there in case the code, name and reg no are all null (so the statement will compile/run). Personally, I favor using a boolean flag to see if I need the "and". The first clause doesn't get one and all subsequent clauses do.