问题描述
我收到错误您的SQL语法出错了;请查看与您的MySQL服务器版本对应的手册，以便在第1行的''orderr'附近使用正确的语法 - 所以我假设错误是我使用了两个'，但在我的代码中我没有使用任何'。注意，该表实际上命名为orderr。
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());
}
}
}
推荐答案
在大多数数据库中，你不能参数化对象名称，比如表名，在MySQL中你理论上可以认为MySQL Connector / J默认不使用服务器端参数，而是重写它查询在发送到服务器之前。但是，该值将作为带引号的字符串插入，并且对象名称不能是带引号的字符串，因此它仍然无效。
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.
所以 INSERT INTO？或 SELECT ... FROM？将无效，因为它会产生 INSERT INTO'theTable'或 SELECT ... FROM'theTable'。
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.
这篇关于JDBC PreparedStatement导致MySQL语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！