Mysql Drop Table as PreparedStatement not working for me

12
votes

This prepared statement seems like valid SQL to me.

PreparedStatement dropTable = cnx.prepareStatement(
    "DROP TABLE IF EXISTS ?");
dropTable.setString(1, "features");
dropTable.execute();

But when I run this, I get the error:

Exception in thread "main" 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 ''features'' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:532) 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:1031) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356) at doriangray.db.TestSetup.main(TestSetup.java:62)

Does anyone see the problem here? I'm stumped.

4
javamysqljdbcprepared-statement
I'm not into Java, but all prepared statements libraries I know don't support dynamic table names. - Pekka

4 Answers

11
votes

MySQL doesn't support prepared statements with variable table names, so you have to do this the old fashioned way, by generating SQL:

PreparedStatement dropTable = cnx.prepareStatement(
String.format("DROP TABLE IF EXISTS %s", "features"));
dropTable.execute();

In this case you might as well use regular statements since you don't gain anything by using prepared statements.

2
votes

I think your code prepares this statement:

DROP TABLE IF EXISTS 'features'

while you want:

DROP TABLE IF EXISTS features
1
votes

PreparedStatements are used for making database compile the query (make the execution plan) once, so executing the same query with different parameters happens faster.

In short in a PreparedStatement you can not have a wildcard for database objects. Including table name, column name, different where clauses and ....

0
votes

You can't use a prepared statement to drop tables with a dynamic table names. However, if you don't know the table names until run-time, and they could have spaces in them, then you'll need to enclose them in back-ticks:

for (String tableName : tableNames) {
    statement.executeUpdate("DROP TABLE IF EXISTS `" + tableName + "`");
}