SQL Query throws exception

Question

I wrote the following code snipped with a SQL query which gets me the storeID of a particular store in the DB:

private int inDB(String storename, String phone, int zipcode)
 {
 try
 {
 String select = "SELECT storeID FROM Store WHERE storename = ? OR phone = ? AND City_zipcode = ?;";
 PreparedStatement pst = dbc.prepareStatement(select);
 pst.setString(1, storename);
 pst.setString(2, phone);
 pst.setInt(3, zipcode);
 ResultSet rs = pst.executeQuery(select);
 while(rs.next())
 {
 return rs.getInt("storeID");
 }
 }
 catch (Exception e)
 {
 e.printStackTrace();
 }
 return -1;
 }

Unfortunately this code throws a massive exception... And I try to figure out for a while whats the problem.

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 '? OR phone = ? AND City_zipcode = ?' at line 1
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
 at com.mysql.jdbc.Util.getInstance(Util.java:360)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
 at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
 at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1446)
 at db.Store.inDB(Store.java:297)

As you can see, I'm using the MySQL db driver. All the other methods which are nearly the same are working. It must be the query which is causing the problems. But when I try to run it in phpMyAdmin it's absolutely no problem.

I hope someone of you can help me out...

Kind regards //Jonas

• Hichamov: Why don't you try whithout the ;
• Hichamov: If you are in a JEE context, I totally recommend using Criteria builder instead of native queries. It is the most easy to maintain
• TacoVox: Even with the ; its throwing the exception. All my other queries are using it as well...

Answer 1

Error is here change it

ResultSet rs = pst.executeQuery(select);

TO

ResultSet rs = pst.executeQuery();

you all ready set the query statement to preparedStatement

Answer 2

I do believe that ? is a reserved keyword for mysql...

Read this, http://dev.mysql.com/doc/refman/5.1/en/regexp.html

• Scherling: It's a prepared statement, the ? should be replaced by his parameters before the actual sql is executed. Read more about Prepared statements here: docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html