wood burning stoves*
The moose likes JDBC and the fly likes wild cards in MySQL using jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "wild cards in MySQL using jdbc" Watch "wild cards in MySQL using jdbc" New topic
Author

wild cards in MySQL using jdbc

Aaron Jeffries
Greenhorn

Joined: Oct 20, 2009
Posts: 9
Hello everyone.

The following statements works from a MySQL command line: SELECT * FROM drug WHERE otherBrands LIKE '%?%'

When I place it in the follow code there is a bad SQL message (below). If I remove the wildcards, the query runs fine. I've been playing around with variations of the MySQL statement with no luck.

I am breaking a syntax rule in jdbc somewhere?

Thanks so much.




HTTP ERROR 500

Problem accessing /uRateDrugs-webapp/searchResultsByDrugName.html. Reason:

PreparedStatementCallback; bad SQL grammar [SELECT * FROM drug WHERE otherBrands LIKE %?%]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'hands'%' at line 1

Caused by:

org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM drug WHERE otherBrands LIKE %?%]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'hands'%' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:220)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:641)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:678)
at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:721)
at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.queryForObject(SimpleJdbcTemplate.java:169)
at com.acwebsitedesign.uRateDrugs.domain.DrugRepositoryImpl.getDrugByGenericName(DrugRepositoryImpl.java:64)
at com.acwebsitedesign.uRateDrugs.domain.DrugServiceImpl.getDrugByGenericName(DrugServiceImpl.java:18)
at com.acwebsitedesign.uRateDrugs.web.SearchDrugsController.searchByResultsDrugName(SearchDrugsController.java:36)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.doInvokeMethod(HandlerMethodInvoker.java:421)
at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:136)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:326)
at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:313)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:875)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:807)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:571)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:511)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:502)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
at org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
at org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:230)
at org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:536)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:928)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:747)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:218)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:405)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%'hands'%' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.Util.getInstance(Util.java:381)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885)
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:648)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
... 39 more
Aaron Jeffries
Greenhorn

Joined: Oct 20, 2009
Posts: 9
Hello everyone.

I was browsing other forums and found an answer to my question. One solution is to add wild cards to your prepared statement. In my case, it looked something like this:

return simpleJdbcTemplate.queryForObject(sql, mapper, "%" +name +"%");

Hope this helps someone else.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

You should put the wildcard % inside the prepared statement setString(). In your example, " LIKE %?%" will be often be replaced by the JDBC driver by" LIKE %'value'%" which is invalid SQL. In other words, put the % in the name string, not in the SQL string. Although, based on your error, I'm not sure you are using the queryForObject() properly.


My Blog: Down Home Country Coding with Scott Selikoff
Aaron Jeffries
Greenhorn

Joined: Oct 20, 2009
Posts: 9
Thanks Scott.
Ben Morse
Greenhorn

Joined: Dec 01, 2012
Posts: 7

Hey guys, I know this question is a bit different than OP, but how do I protect against this? If I'm using LIKE, and the method above, outside input will be interpreted as a %. How can I prevent this?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Welcome to the ranch, Ben!

A similar problem was recently discussed here: http://www.coderanch.com/t/598241/JDBC/databases/wrong-PreparedStatement#2729042

In short: the operand of the LIKE operator needs to be a varchar, so either a varchar literal (eg. LIKE "ABC%"), or a parameter (eg. LIKE ?), or a varchar expression (eg. LIKE "%" + ? + "%"). I'd go with the second option (LIKE ?) and use Java to wrap the actual value being searched for with percent signs (that is, ps.setString(index, "%" + textToSearchFor + "%")).

Also note that this kind of queries (LIKE with the leading wildcard character) tends to be very ineffective in most databases, because ordinary indexes cannot be used to answer these queries. Some databases support some kind of fulltext indexing, but this varies from DB to DB. I don't know any details about this, but I think it is in some form available in MySQL too.
Ben Morse
Greenhorn

Joined: Dec 01, 2012
Posts: 7

Martin,
Thanks for the welcome! Great information!
 
 
subject: wild cards in MySQL using jdbc