Java后端PreparedStatement无报错但Oracle数据库未更新排查

尝试更新无主键的Oracle数据库表，已将所有列加入WHERE子句排除主键缺失问题，也排查过双引号使用问题，但Java代码始终无法正常更新数据。在Oracle Developer中执行对应SQL脚本可正常更新，但Java控制台无报错，仅输出更新行数为0，打印的SQL语句和参数显示完全正确。

重写updateRows方法，通过JdbcTemplate构建PreparedStatement执行更新，传入的CrudObject包含新旧行数据：

@Override
public void updateRows(CrudObject crudObject, String schema, String tableName) {
    System.out.println("Received crudObject: " + crudObject);
    try {
        // Prepare the SQL query
        for (CrudRows row : crudObject.getCrudRows()) {
            // For each row, construct the SQL query
            StringBuilder sqlBuilder = new StringBuilder();
            MapSqlParameterSource params = new MapSqlParameterSource();

            sqlBuilder.append("UPDATE ").append(schema).append(".").append(tableName).append(" SET ");

            List<String> setParts = new ArrayList<>();
            for (CrudNewRowData columnData : row.getCrudNewRowData()) {
                setParts.add(columnData.getColumnName() + " = :" + "new_" + columnData.getColumnName());

                String columnNewDataValue = columnData.getColumnNewData();
                columnNewDataValue = "'" + columnNewDataValue + "'";
                params.addValue("new_" + columnData.getColumnName(), columnNewDataValue);
            }

            sqlBuilder.append(String.join(", ", setParts));

            // Construct WHERE clause
            List<String> whereParts = new ArrayList<>();
            for (CrudOldRowData columnData : row.getCrudOldRowData()) {
                whereParts.add(columnData.getColumnName() + " = :" + "old_" + columnData.getColumnName());

                String columnOldDataValue = columnData.getColumnOldData();
                columnOldDataValue = "'" + columnOldDataValue + "'";
                params.addValue("old_" + columnData.getColumnName(), columnOldDataValue);
            }

            sqlBuilder.append(" WHERE ").append(String.join(" AND ", whereParts));

            // Execute the SQL update operation
            String sqlQuery = sqlBuilder.toString();
            String parameterValues = params.getValues().toString();

            System.out.println("Complete SQL: " + sqlQuery + " | Parameters: " + parameterValues);

            int rowsUpdated = this.jdbcTemplate.update(sqlBuilder.toString(), params);
            System.out.println("Rows updated: " + rowsUpdated);
        }
    } catch (Exception e) {
        e.printStackTrace();
        throw new RuntimeException(e.getMessage());
    }
}

{
    "schemaName": "ABCD",
    "tableName": "STUDENT",
    "apiKey": "student",
    "crudMethod": "PUT",
    "crudRows": [
        {
            "crudNewRowData": [
                {"columnName": "PART_NUMBER", "columnNewData": "123456"},
                {"columnName": "CONTROL_NUMBER", "columnNewData": "78910"},
                {"columnName": "ACC_NUMBER", "columnNewData": "4"}
            ],
            "crudOldRowData": [
                {"columnName": "PART_NUMBER", "columnOldData": "123456"},
                {"columnName": "CONTROL_NUMBER", "columnOldData": "78910"},
                {"columnName": "ACC_NUMBER", "columnOldData": "3"}
            ]
        }
    ]
}

UPDATE ABCD.STUDENT
SET PART_NUMBER = '123456', CONTROL_NUMBER = '78910', ACC_NUMBER='4'
WHERE PART_NUMBER = '123456' AND CONTROL_NUMBER = '78910' AND ACC_NUMBER='3';

Received crudObject: CrudObject(schemaName=ABCD, tableName=STUDENT, apiKey=Student, crudMethod=PUT, crudRows=[CrudRows(crudNewRowData=[CrudNewRowData(columnName=PART_NUMBER, columnNewData=123456), CrudNewRowData(columnName=CONTROL_NUMBER, columnNewData=78910), CrudNewRowData(columnName=ACC_NUMBER, columnNewData=4)], crudOldRowData=[CrudOldRowData(columnName=PART_NUMBER, columnOldData=123456), CrudOldRowData(columnName=CONTROL_NUMBER, columnOldData=78910), CrudOldRowData(columnName=ACC_NUMBER, columnOldData=3)])])

Complete SQL: UPDATE ABCD.STUDENT SET PART_NUMBER = :new_PART_NUMBER, CONTROL_NUMBER = :new_CONTROL_NUMBER, ACC_NUMBER = :new_ACC_NUMBER WHERE PART_NUMBER = :old_PART_NUMBER AND CONTROL_NUMBER = :old_CONTROL_NUMBER AND ACC_NUMBER = :old_ACC_NUMBER | Parameters: {new_PART_NUMBER='123456', new_CONTROL_NUMBER='78910', new_ACC_NUMBER='4', old_PART_NUMBER='123456', old_CONTROL_NUMBER='78910', old_ACC_NUMBER='3'}
Rows updated: 0

问题根源 #

代码中手动给参数值添加了单引号：

columnNewDataValue = "'" + columnNewDataValue + "'";
// 以及
columnOldDataValue = "'" + columnOldDataValue + "'";

使用JdbcTemplate的MapSqlParameterSource时，JDBC会自动根据数据库字段类型处理参数的格式，不需要手动添加单引号。手动添加后，参数值变成了带单引号的字符串（比如'3'），如果数据库中ACC_NUMBER是数字类型，实际存储的值是3，此时WHERE条件中:old_ACC_NUMBER匹配的是字符串'3'，而非数字3，导致无法找到匹配行，更新行数为0。

修复方案 #

移除手动添加单引号的代码，直接将原始值传入参数：

// 替换原有新增字段参数代码
params.addValue("new_" + columnData.getColumnName(), columnData.getColumnNewData());

// 替换原有WHERE条件参数代码
params.addValue("old_" + columnData.getColumnName(), columnData.getColumnOldData());

额外建议 #

• 如果字段类型是数字，建议在CrudObject中存储对应类型的值（而非字符串），避免类型转换问题；
• 可以开启JdbcTemplate的SQL日志（比如通过Spring Boot的logging.level.org.springframework.jdbc=DEBUG），查看最终执行的SQL和参数绑定情况，便于排查问题。

内容的提问来源于stack exchange，提问作者sam lee