Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Database searching

 
An Dao
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to search a field in DB that contains the word user typped in. However, I was not successful. It returns less records than I expect. The following is my SQL to do the search:
psSql = dbConn.prepareStatement(
"SELECT id, email " +
" FROM user " +
"WHERE email like (?) " +
" OR email like (?) " +
" OR email like (?) " +
" OR email like (?) " +
"ORDER BY email" );
psSql.setString( 1, email );
psSql.setString( 2, "%" + email + "%" );
psSql.setString( 3, "%" + email );
psSql.setString( 4, email + "%" );
rsSql = psSql.executeQuery();
I tried this SQL "select email from user where email like '%TEST% " in SQL Plus of Oracle and it works perfectly fine, it returns me 19 records; whereas the above Java program returns me only 5 records!!! Is there any suggestion how to do the search in DB?
Thank you.
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
just a hunch. I believe that the preparedStatement escapes special characters. '%' is a special character so it may be escaped. Are the records returned to your resultset an exact match? I would think so, because the query should be literally looking for "%" + email + "%". You may have to change this to a statement object to be used correctly(but check for single quotes before you execute the query when using statements).
Jamie
 
An Dao
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,
Thanks for your help. When I used PreparedStatement, what it returns to me is the partial match, not the exact match only ( 2T@test.com would match the "test"). However, I changed it to statement and it worked!!! Thanks again.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic