File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL query" Watch "SQL query" New topic
Author

SQL query

venkatesh pendharkar
Ranch Hand

Joined: Apr 29, 2006
Posts: 106
Hi
I am developing a WEB PhoneBook Catalogue for a comany intranet in JSP & MSACCESS.In this we can add employee information by following attributes:-empl Id,first Name,lastName,department,site,designation,extension,mob no,email Id.So there will be a USER database in which all these fileds will be present & employeeId will be the primary key.
We can search the employee info as follows-
These are the attributes by which I need to make search:
empl Id,first Name,lastName,department,site,designation & for each of this there will be a textbox in my JSP.
So if the user puts only firstName (eg.John)in the textbox & enetrs Search then all the employess with name John in the entire database will be displayed.
If the user enters firstName(eg.John) & Department(eg.HR) then all John in HR department will be displayed.
Like this depending on the values enetered by the user,dnamically databse will be searched.
Now my question is how can I write a single SQL query for this.my query will be something like this:-"select * from user where emplId='"+s1+"' AND firstName='"+s2+"' AND lastName='"+s3+"' AND department='"+s=4+"'....so on
& s1,s2,s3...will be the strings which are taken from databse as the values enetred by the user.
But the problem is if user only enters firstName i.e. "s1" then all other Strings "s2","s3"...will be null & so in the query null value will be passed.
So can anyone help me how to write a dynamic SQL query for where clause for MSACCESS so that I don need to hardcode SQL queries for differnet combination of user Input???
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Originally posted by venkatesh pendharkar:
Hi



Now my question is how can I write a single SQL query for this.my query will be something like this:-"select * from user where emplId='"+s1+"' AND firstName='"+s2+"' AND lastName='"+s3+"' AND department='"+s=4+"'....so on
& s1,s2,s3...will be the strings which are taken from databse as the values enetred by the user.
But the problem is if user only enters firstName i.e. "s1" then all other Strings "s2","s3"...will be null & so in the query null value will be passed.
So can anyone help me how to write a dynamic SQL query for where clause for MSACCESS so that I don need to hardcode SQL queries for differnet combination of user Input???


Here's some pseudocode.



You can use a StringBuffer to append the clauses.
It's also better to use parameter binding in stead of inserting parameters in your query string.

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
venkatesh pendharkar
Ranch Hand

Joined: Apr 29, 2006
Posts: 106
Thanks a lot Jan for your relpy,this is a really nice idea.
vijeta singh
Greenhorn

Joined: Feb 11, 2007
Posts: 1
Note: I am not a MSAccess user, below solution are based on Oracle DB
There are two way to resolve this issue:
1)While building the dynamic sql statement you can use if else to check Not null value of any attribute and build where clause based on that like
sql_statement = 'select * from table_name where 1=1 '
If first_name IS NOT NULL THEN
sql_statement = sql_statement + 'and first_name = '+ param_first_name
else if last_name is not null then
sql_statement = sql_statement + ' and last_name = '+param_last_name
else if phone_no is not null then
....
...
end if;


2)Try to use NVL function ( NVL is sql function not sure that it will work in MS access but definitly you will something resembling to it)

select * from table_name
where first_name = nvl(param_first_name,first_name)
and last_name = nvl(param_last_name ,last_name)
and phone_number = nvl(param_phone_Number,phone_number)
...

Please refer
http://www.orafaq.com/forum/t/46536/0/
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL query
 
Similar Threads
Converting a Java Class to XML when Class contains Non-Primitive Types
In struts2 delete is not Working
Marshalling XML with Spring (How to handle referenced classes)
problem with @FieldResult, mapping does not seem to work
Sql Where clause and/or issues