aspose file tools*
The moose likes Servlets and the fly likes JSP-JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "JSP-JDBC" Watch "JSP-JDBC" New topic
Author

JSP-JDBC

Ramamoorthy
Greenhorn

Joined: Mar 13, 2001
Posts: 19
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
Deepa Balasubramanayam
Ranch Hand

Joined: Mar 29, 2001
Posts: 39
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.
Madhav Lakkapragada
Ranch Hand

Joined: Jun 03, 2000
Posts: 5040

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

Take a Minute, Donate an Hour, Change a Life
http://www.ashanet.org/workanhour/2006/?r=Javaranch_ML&a=81
Ramamoorthy
Greenhorn

Joined: Mar 13, 2001
Posts: 19
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
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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
ravi janap
Ranch Hand

Joined: Nov 04, 2000
Posts: 389
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


SCJP, SCJD, SCWCD, SCBCD, SCEA
Ames Carlson
Greenhorn

Joined: Apr 23, 2001
Posts: 9
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
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
"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.

Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Ramamoorthy
Greenhorn

Joined: Mar 13, 2001
Posts: 19
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]
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]

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JSP-JDBC
 
Similar Threads
How to find out number of years worked using sql
DDL & DML executor
sql stmt to get dates
sql query based on variable number of input parameters
Context Share between 2 Web Apps