我收到以下错误消息,但我不明白为什么:
org.springframework.jdbc.BadSqlGrammarException:
PreparedStatementCallback;错误的SQL语法[UPDATE da_tracking SET
ins_name = xyz,ins_dev_scripted = False,其中ins_ID = 12];嵌套的
异常是java.sql.SQLException:无效的参数索引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() + "]");
你知道为什么吗??
我的删除和阅读方法有效。
最佳答案
您看到的错误是因为您正在将变量传递到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);
在内部,Spring正在执行以下操作:
PreparedStatement stmt = conn.prepareStatement("...your sql...");
stmt.setString(1, dboDevName); // this will fail, since there is no bind variable
// with index 1
...
您永远不要通过连接外部数据来构建SQL查询。在最好的情况下,如果有人在数据中添加奇怪的引号或转义字符,它将导致查询随机失败,在最坏的情况下,这将导致严重的安全漏洞,从而损害您的系统。