PostgreSQL更新语句列索引越界异常排查求助

云上老周

2026-7-18

解决PostgreSQL更新操作中的列索引越界异常

问题根源

你的代码中SQL语句存在两处关键语法错误，导致PreparedStatement识别的占位符数量与你设置的参数数量不匹配，从而抛出org.postgresql.util.PSQLException: The column index is out of range: 5, number of columns: 4异常：

缺失赋值逻辑与空格：client_email字段未编写= ?的赋值语句，丢失了对应占位符；同时该字段与WHERE关键字之间缺少空格，会触发SQL语法错误。
拼接空格遗漏：UPDATE clients与SET之间没有空格，拼接后会形成非法的UPDATE clientsSET语句。

修正后的代码

public Client updateClient (int clientId, AddUpdateDTO client) throws SQLException {
    try (Connection con = JDBCUtility.getConnection()){
        String sql = "UPDATE clients "
                + "SET client_first_name = ?, "
                + "client_last_name = ?, "
                + "client_phone_number = ?, "
                + "client_email = ? "
                + "WHERE client_id = ?;";            
        PreparedStatement pst = con.prepareStatement(sql);
        
        pst.setString(1, client.getFirstName());
        pst.setString(2, client.getLastName());
        pst.setString(3, client.getPhoneNumber());
        pst.setString(4, client.getEmail());
        pst.setInt(5, clientId);
        
        int updatedAmount = pst.executeUpdate();
        
        if (updatedAmount != 1) {
            throw new SQLException("Unable to find client record with Id of " + clientId);
        }
    }
    
    return new Client(clientId, client.getFirstName(), client.getLastName(), client.getPhoneNumber(), client.getEmail());       
}