嵌套异常是 java.sql.SQLException: Invalid parameter index 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12181490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
nested exception is java.sql.SQLException: Invalid parameter index 1
提问by user1631032
I get following Error, but I can not understand why:
我收到以下错误，但我不明白为什么：
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [UPDATE da_tracking SET ins_name= xyz, ins_dev_scripted = False WHERE ins_ID = 12]; nested exception is java.sql.SQLException: Invalid parameter index 1.
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; 错误的 SQL 语法 [UPDATE da_tracking SET ins_name= xyz, ins_dev_scripted = False WHERE ins_ID = 12]; 嵌套异常是 java.sql.SQLException: Invalid parameter index 1。
------------------------ dao class-----
public int save(DboBean record) {
// TODO Auto-generated method stub
String sql = "UPDATE da_tracking"
+ " SET ins_name= " + record.getDboDevName()+ ","
+ " ins_dev_scripted = " + record.getDevScripted()
+ " WHERE ins_ID = " + record.getDboId();
Object[] params = new Object[] {record.getDboDevName(), record.getDevScripted()};
int[] types = new int[]{Types.VARCHAR, Types.BIT};
return jdbcTemplate.update(sql, params, types);
}
----------------------------Junit-----
bean.setDboDevName("xyz");
bean.setDboId(12);
int rowsAffected = objDao.save(bean);
System.out.println("Object is updated [" + bean.getDboId() + ", " + bean.getDboDevName() +
", " + bean.getDevScripted() + "]");
Do you know why?? My delete & read Methods work.
你知道为什么吗？？我的删除和阅读方法有效。
回答by Adam Batkin
The error you are seeing is because you are passing variables in to the paramsand typesarrays, but you haven't put placeholders for those bind variables in your queries:
您看到的错误是因为您将变量传递给params和types数组，但您没有在查询中为这些绑定变量放置占位符：
String sql = "UPDATE da_tracking"
+ " SET ins_name= ?,"
+ " ins_dev_scripted = ?"
+ " WHERE ins_ID = ?"
Object[] params = new Object[] {record.getDboDevName(), record.getDevScripted(), record.getDboId()};
int[] types = new int[]{Types.VARCHAR, Types.BIT, Types.INTEGER};
return jdbcTemplate.update(sql, params, types);
Internally, Spring is doing something like this:
在内部，Spring 正在做这样的事情：
PreparedStatement stmt = conn.prepareStatement("...your sql...");
stmt.setString(1, dboDevName); // this will fail, since there is no bind variable
// with index 1
...
You should NEVER build SQL queries by concatenating external data. In the best case, it will result in queries that will randomly fail if someone puts a strange quote or escape character in the data, and in the worst case, a major security hole which will compromise your system.
您永远不应该通过连接外部数据来构建 SQL 查询。在最好的情况下，如果有人在数据中添加奇怪的引号或转义字符，它将导致查询随机失败，在最坏的情况下，会导致一个主要的安全漏洞，这将危及您的系统。
回答by Garbage
public int save(DboBean record) {
String sql = "UPDATE da_tracking"
+ " SET ins_name= ?"+","
+ " ins_dev_scripted = ?"
+ " WHERE ins_ID = ?";
Object[] params = new Object[] {record.getDboDevName(), record.getDevScripted(), record.getDboId()};
int[] types = new int[]{Types.VARCHAR, Types.BIT, Types.INTEGER}; // Change 3rd parameter type here
return jdbcTemplate.update(sql, params, types);
}
----------------------------Junit-----
bean.setDboDevName("xyz");
bean.setDboId(12);
int rowsAffected = objDao.save(bean);
System.out.println("Object is updated [" + bean.getDboId() + ", " + bean.getDboDevName() +
", " + bean.getDevScripted() + "]");
Does this work?
这行得通吗？
