JSP-JDBC

Dear gurus,
I have one requirement.
I am in the process of creating one search application for matrimonial and wherein i am getting around 5 to 7 parameters from the users.Now,i should create an Dyanamic SQL statement based on the parameters given by the users.There is a possiblity that the user may enter all 7 parameters or less also.
Presently i have created lot of SQL statements based on various combinations..But i feel that is not the right way of doing it..
Backend i am using oracle8i
Can any one give me some suggestions on this??
Most urgent..

Ramamoorthy

Hi RamaMoorthy,
Use stringtokenizer to get all the parameters sent by the user and store it in a variable . Finally pass it to the SQL statement.
Deepa.

also, think abt using JavaBeans to do the JDBC work.
- satya

Originally posted by Deepa Balasubramanayam:
Hi RamaMoorthy,
Use stringtokenizer to get all the parameters sent by the user and store it in a variable . Finally pass it to the SQL statement.
Deepa.

Thank you deepa..but my query is i do not know what the user is going to enter the fields..may be he can enter 5 fields and leave balance 2 ..based on the fields he enters,a sql query to be written and datas to be fetched from the table.
For your info..i give below the fields the user has to enter..
1.sex
2.study
3.region
4.religion
5.age group
6.caste
7.preferences - except the first one,in the balance the user can choose any six..so,accordingly the sql query to be written..
I hope i have explained you clearly and hoping to get your guidance..
Ram

Originally posted by Ramamoorthy:

For your info..i give below the fields the user has to enter..
1.sex
2.study
3.region
4.religion
5.age group
6.caste
7.preferences - except the first one,in the balance the user can choose any six..so,accordingly the sql query to be written..

In its most basic and straightforward form:

Now this can be refined a lot, as it involves quite a bit of code repetition. One thing you could do is represent each parameter by an object which examines the request, extracts the relevant parameter and returns an SQL snippet. The repeated code could be put in the class (say, SqlParameterParser); the above code would simply loop over an array of these SqlParameterParser objects.
- Peter

Hi Ramamoorthy,
I am also working on a similar application and I have used a StringBuffer to dynamically generate the query. I am giving part of my code :
if ( (!(first_name.equals(""))) | | (!(last_name.equals(""))) | | (!(personal_email.equals(""))) | | (!(phonefax_no.equals(""))) | | (!(aim_name.equals(""))) ) {
sb.append("SELECT PLAYER_ID, LAST_NAME, FIRST_NAME, PERSONAL_EMAIL FROM MJST_PLAYER_PROFILE WHERE ");

boolean bFirstColumnCheck = true;
if (!(first_name.equals(""))) {
sb.append(" LOWER ( FIRST_NAME ) LIKE '"+( first_name.toLowerCase() ).trim()+"%'");
bFirstColumnCheck = false;
msg.append(" First Name");
}
if (!(last_name.equals(""))) {

if (bFirstColumnCheck) {
sb.append(" LOWER ( LAST_NAME ) LIKE '"+( last_name.toLowerCase() ).trim()+"%'");
bFirstColumnCheck = false;
msg.append(" Last Name");
} else {
sb.append(" AND LOWER ( LAST_NAME ) LIKE '"+( last_name.toLowerCase() ).trim()+"%'");
msg.append(" and Last Name");
}
}

if (!(personal_email.equals(""))) {
if (bFirstColumnCheck) {
sb.append(" LOWER ( PERSONAL_EMAIL ) LIKE '"+( personal_email.toLowerCase() ).trim()+"%'");
bFirstColumnCheck = false;
msg.append(" Personal E-Mail");
} else {
sb.append(" AND LOWER ( PERSONAL_EMAIL ) LIKE '"+( personal_email.toLowerCase() ).trim()+"%'");
msg.append(" and Personal E-Mail");
}
}
if (!(phonefax_no.equals(""))) {
if (bFirstColumnCheck) {
sb.append(" NOTIFY_PHONE_ONE LIKE '"+sb1.toString()+"%' OR NOTIFY_PHONE_TWO LIKE '"+sb1.toString()+"%' OR NOTIFY_FAX LIKE '"+sb1.toString()+"%' ");
bFirstColumnCheck = false;
msg.append(" Phone \\ Fax Number");
} else {
sb.append(" AND NOTIFY_PHONE_ONE LIKE '"+sb1.toString()+"%' OR NOTIFY_PHONE_TWO LIKE '"+sb1.toString()+"%' OR NOTIFY_FAX LIKE '"+sb1.toString()+"%' ");
msg.append(" and Phone \\ Fax Number");
}
}
if (!(aim_name.equals(""))) {
if (bFirstColumnCheck) {
sb.append(" LOWER ( PLAYER_ID ) LIKE '"+sb2.toString().toLowerCase()+"%'");
bFirstColumnCheck = false;
msg.append(" AIM Name");
} else {
sb.append(" AND LOWER ( PLAYER_ID ) LIKE '"+sb2.toString().toLowerCase()+"%'");
msg.append(" and AIM Name");
}
}
sb.append(" ORDER BY LAST_NAME");
dbQuery = sb.toString();

Hope this would be of help to you

Another way to do it, with just one query, would be
to consider using LIKE predicates. This way, on
the ones whose value you didn't care about, you could
use COLUMN LIKE '%', which matches everything
except for NULLs. Another trick I've found is to
introduce extra variables for BOOLEANs, which will
work if the query engine supports short-circuiting
(and supports BOOLEANs in expressions).
Using LIKES:
SELECT * FROM MARRIAGETABLE
WHERE SEX LIKE ?
AND CASTE LIKE ?
AND -- repeat for the other fields
Then when you set the variables you would say:
if (sex is null | | sex.length() = 0) sex="%";
ps.setString(1,sex);
if (caste is null | | caste.length() = 0) caste="%";
ps.setString(2,caste);
// repeat for the other parameters
...
ps.executeQuery();
Using Booleans:
SELECT * FROM MARRIAGETABLE
WHERE ((CAST ? AS BOOLEAN) OR SEX = ?)
AND ((CAST ? AS BOOLEAN) OR CASTE = ?)
AND -- repeat for the other fields
Then when you set the variables you would say:
boolean sexbool =(sex is null | | sex.length() = 0);
ps.setBoolean(1,sexbool);
ps.setString(2,sex);
boolean castebool =(caste is null | | caste.length() = 0);
ps.setBoolean(2,castebool);
ps.setString(2,caste);
// repeat for the remaining fields
...
ResultSet rs=ps.executeQuery();
You might want to try either of these, and see how they
perform. LIKEs may not be too bad if the optimizer
can handle the fact that you switch between matching
everything and matching nothing.
Hope this helps,
Ames Carlson
ames.carlson-at-informix.com

"Ramamoorthy",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please log in with a new name which meets the requirements.
Thanks.

Thank you peter Haan,
Really your approach was very helpful to us in solving the problem.Now our program is working fine.
Once again thanking you,
Rama moorthy

Originally posted by Peter den Haan:
[B] ... StringBuffer whereClause = new StringBuffer(); String sex = request.getParameter("sex"); if (sex != null && sex.length() > 0) addToWhereClause(whereClause, "sex='" + sex + "'"); String study = request.getParameter("study"); if (study != null && study.length() > 0) addToWhereClause(whereClause, "study='" + study + "'"); ... etcetera ... // comment out the following for an egalitarian society String caste = request.getParameter("caste"); if (caste != null && caste.length() > 0) addToWhereClause(whereClause, "caste='" + caste + "'"); Statement stmt = con.createStatement(); ResultSet rslt = stmt.executeQuery("select * from foobar where " + whereClause); ... private void addToWhereClause(StringBuffer whereClause, String toAdd) { if (whereClause.length() > 0) whereClause.append(" and "); whereClause.append("(" + toAdd + ")"); }
Now this can be refined a lot, as it involves quite a bit of code repetition. One thing you could do is represent each parameter by an object which examines the request, extracts the relevant parameter and returns an SQL snippet. The repeated code could be put in the class (say, SqlParameterParser); the above code would simply loop over an array of these SqlParameterParser objects.
- Peter[/B]