Menu
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.
288
Closing PreparedStatement Object A simple call to the close() method will do the job. If you close the Connection object first, it will close the PreparedStatement object as well. However, you should always explicitly close the PreparedStatement object to ensure proper cleanup.
Reusing a PreparedStatementOnce a PreparedStatement is prepared, it can be reused after execution. You reuse a PreparedStatement by setting new values for the parameters and then execute it again.
setString(int parameterIndex, String x) Sets the designated parameter to the given Java String value. void. setTime(int parameterIndex, Time x) Sets the designated parameter to the given java.
Prepared statements are much faster when you have to run the same statement multiple times, with different data. Thats because SQL will validate the query only once, whereas if you just use a statement it will validate the query each time.
I think your code prepares this statement:
DROP TABLE IF EXISTS 'features'
while you want:
DROP TABLE IF EXISTS features
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.
23
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 ....
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
