• Entries (RSS)
  • Comments (RSS)

LIKE clause with PreparedStatement

Posted by | Posted in Java | Posted on 18-01-2008

Tagged Under : ,

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.

Share

Read More

Comments

9 comments posted onLIKE clause with PreparedStatement

  1. You are brilliant. I got the same problem, and your solution helps me to solve it.
    Thanks so much
    An

  2. 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.

  3. Try using prepareStatement instead of prepareCall. Probably that could be the problem. PrepareCall is used for executing stored procedures.

  4. Thanks…buddy!!!!!!!
    It is worked for me…..

  5. 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 :)

  6. It worked well, nice work: thankyou

  7. Thanks a lot – worked a treat!

  8. great …it worked perfectly :D

  9. It´s really help me!

    Thanks.

Post a Comment