无效的参数索引1

无效的参数索引1

我收到以下错误消息,但我不明白为什么:


  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() + "]");


你知道为什么吗??
我的删除和阅读方法有效。

最佳答案

您看到的错误是因为您正在将变量传递到paramstypes数组中,但是尚未在查询中放置这些绑定变量的占位符:

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查询。在最好的情况下,如果有人在数据中添加奇怪的引号或转义字符,它将导致查询随机失败,在最坏的情况下,这将导致严重的安全漏洞,从而损害您的系统。

09-28 02:02