jdbc mysql in 参数化,JDBC PreparedStatement导致MySQL语法错误

I am getting the error "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 ''orderr'' at line 1" - so I assume the error is that I have used two ' but in my code I have not used any '. NB the table is actually named orderr.

public void insertIntoDatabase(String table, Object... entries) { // take a table and

Connection con = connect(); //add entries

PreparedStatement preparedStatement = null;

PreparedStatement preparedStatement2 = null;

ResultSet rs = null;

StringBuffer columnNames = new StringBuffer();

StringBuffer sbEntries = new StringBuffer();

for (int i = 0; i < entries.length; i++) {

if (entries[i] instanceof Integer)

sbEntries.append((Integer) entries[i]);

else if (entries[i] instanceof String)

sbEntries.append((String) entries[i]);

if (i != entries.length - 1)//if not last entry add

sbEntries.append(" ,"); // a ' ,'.

}

try {

preparedStatement = con.prepareStatement("select * from ? ;");

preparedStatement.setString(1, table);

preparedStatement2 = con

.prepareStatement("Insert into ?( ? ) values ( ? );");

ResultSet resultSet = preparedStatement.executeQuery(); // get the

// number of

// columns

ResultSetMetaData rsmd; // for the table

rsmd = resultSet.getMetaData();

int columnCount = rsmd.getColumnCount();

for (int i = 1; i < columnCount + 1; i++) { // get column names, add to

columnNames.append(rsmd.getColumnName(i)); // to sb

if (i != columnCount)

columnNames.append(" ,");

}

columnCount = rsmd.getColumnCount();

preparedStatement2.setString(1, table);

preparedStatement2.setString(2, columnNames.toString()); //add sb's to statement

preparedStatement2.setString(3, sbEntries.toString());

preparedStatement2.executeUpdate();

} catch (SQLException e) {

System.out.println("2" + e.getMessage());

}

finally{

try {

if (rs != null) {

rs.close();

}

if (preparedStatement != null) {

preparedStatement.close();

}

if(preparedStatement2 != null){

preparedStatement2.close();

}

if (con != null) {

con.close();

}

} catch (SQLException e) {

System.out.print("3" +e.getMessage());

}

}

}

解决方案

In most databases you can't parametrize object names like table names, in MySQL you theoretically can as MySQL Connector/J by default doesn't use server side parameters, but instead it rewrites the query before sending it to the server. However the value will be inserted as a quoted string, and object names cannot be a quoted string, so it still won't work.

So INSERT INTO ? or SELECT ... FROM ? will not work, as it produces INSERT INTO 'theTable' or SELECT ... FROM 'theTable'.

The object names need to be part of the actual query. Do not use parameters for them. Most other databases (or their drivers) would have thrown an exception for having a parameter in this position.