我正在做一款问答游戏。有提问者窗口。这对于保存问题很有用。但是当想要更新文本字段之一并按保存时,就会发生错误。语法有问题?!
void insertCell(String tableNamer, String column, String value, int id) throws ClassNotFoundException, SQLException{
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:file:C:/Users/Juris Puneiko/IdeaProjects/for_my_testings/src/sample/DB/Questions/For_Private/Easy", "Juris", "1");
PreparedStatement ps = conn.prepareStatement("UPDATE ? SET ? = ? where ID = ?");
ps.setString(1, tableNamer);
ps.setString(2, column);
ps.setString(3, value);
ps.setInt(4, id);
ps.executeUpdate();
ps.close();
conn.close();
}
org.h2.jdbc.JdbcSQLException:SQL 语句中的语法错误“UPDATE ?[*] SET ? = ? WHERE ID = ?”;预期的“标识符”;SQL 语句:更新?放 ?= ? 其中 ID = ? [42001-196]
这是什么 >>> [*]?
这是什么意思?
占位符只能用于大多数 SQL 数据库中的值,不能用于表名或列名等标识符:
"UPDATE myTable SET myCol = ? where ID = ?" -- OK
"UPDATE ? SET ? = ? where ID = ?" -- not OK
The reason is that those parameters are also used for prepared statements, where you send the query to the database once, the database "prepares" the statement, and then you can use this prepared statement many times with different value parameters. this can improve DB performance because DB can compile and optimize the query and then use this processed form repeatedly - but to be able to do this, it needs to know names of the tables and columns involved.
To fix this, you only leave the ?
s in for the values, and you concatenate the tableNamer
and column
manually:
"UPDATE " + tableNamer + " SET " + column + " = ? where ID = ?"
但请记住,通过这样做,tableNamer
而column
现在可能容易受到SQL注入。确保您不允许用户提供或影响它们,否则请清理用户输入。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句