Jan
18
LIKE clause with PreparedStatement
Posted by | Posted in Java | Posted on 18-01-2008
Tagged Under : Java, JDBC
How to use a LIKE clause with PreparedStatement object? I had this requirement and all the methods that I tried were not working. Java was not giving me any compile time nor run time errors. However the result was not coming as expected. I did some search on that and finally I got the solution. When we use PreparedStatement with LIKE clause, give the percentage sign in your setXXX method.
For eg:
String firstName ="A"; String query = "SELECT NAME, EMAIL FROM USER WHERE USERID LIKE ?"; PreparedStatement stmt = con.prepareStatement(query); stmt.setString(1, "%"+firstName+"%"); ResultSet rst = stmt.executeQuery(); |
The above code is tested in WebSpere Application Server + Oracle and Tomcat + MySQL environments and is working fine.
You are brilliant. I got the same problem, and your solution helps me to solve it.
Thanks so much
An
hi,
i have the same problem and i tried the same as you have mentioned here.
pstSearch = conn.prepareCall(“SELECT FName,LName FROM Employee where FName like ? “, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
pstSearch.setString(1, name );
rs=pstSearch.executeQuery();
but this couses an exception
java.sql.SQLException: Unable to obtain result set row count. From SELECT FNAME,LNAME FROM EMPLOYEE WHERE FNAME LIKE ?
I dont know what to do.
plz help.
Try using prepareStatement instead of prepareCall. Probably that could be the problem. PrepareCall is used for executing stored procedures.
Thanks…buddy!!!!!!!
It is worked for me…..
Thanks, I was using this
setString(1, “‘%”+firstName+”%’”);
and always got empty list… It was hard (for me) to spot the problem.. single quotes (‘) were my problem
It worked well, nice work: thankyou
Thanks a lot – worked a treat!
great …it worked perfectly