Spring Boot使用NamedParameterJdbcTemplate执行多表插入遇PostgreSQL参数错误

错误原因 #

PostgreSQL JDBC驱动不会解析PL/pgSQL匿名块（DO $$ ... $$）内部的参数占位符，它会把这些占位符视为PL/pgSQL的局部变量，而非JDBC层面需要绑定的参数。当你用NamedParameterJdbcTemplate将命名参数（如:name）替换成?后，驱动扫描SQL时找不到需要绑定的JDBC参数，但程序仍尝试绑定参数，最终触发org.postgresql.util.PSQLException: 列索引越界: 1，列数: 0错误。

解决方案 #

方案一：改用存储过程（推荐，无SQL注入风险） #

1. 创建带输入参数的存储过程，封装多表插入逻辑：

CREATE OR REPLACE PROCEDURE insert_student_details(
    p_name VARCHAR,
    p_age INT,
    p_color VARCHAR,
    p_animal VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_student_id INT;
BEGIN
    -- 插入student表并获取返回的id
    INSERT INTO student(name, age) VALUES(p_name, p_age) RETURNING id INTO v_student_id;
    -- 插入关联表
    INSERT INTO favourite_color(student_id, color) VALUES(v_student_id, p_color);
    INSERT INTO favourite_animal(student_id, animal) VALUES(v_student_id, p_animal);
END $$;

2. 在Spring Boot中调用存储过程：

import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import java.util.HashMap;
import java.util.Map;

// 注入NamedParameterJdbcTemplate
private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void insertStudentDetails() {
    Map<String, Object> params = new HashMap<>();
    params.put("name", "张三");
    params.put("age", 22);
    params.put("color", "绿色");
    params.put("animal", "狗");

    // 调用存储过程
    namedParameterJdbcTemplate.execute(
        "{call insert_student_details(:name, :age, :color, :animal)}",
        params,
        (cs) -> {
            cs.execute();
            return null;
        }
    );
}

方案二：直接使用JdbcTemplate绑定位置参数（适合简单场景） #

如果不想创建存储过程，可以将匿名块中的命名参数替换为?，直接用JdbcTemplate绑定位置参数（驱动会识别这些?为JDBC参数）：

import org.springframework.jdbc.core.JdbcTemplate;

// 注入JdbcTemplate
private final JdbcTemplate jdbcTemplate;

public void insertStudentDetails() {
    String sql = """
        DO $$
        DECLARE
            v_student_id INT;
        BEGIN
            INSERT INTO student(name, age) VALUES(?, ?) RETURNING id INTO v_student_id;
            INSERT INTO favourite_color(student_id, color) VALUES(v_student_id, ?);
            INSERT INTO favourite_animal(student_id, animal) VALUES(v_student_id, ?);
        END $$;
        """;
    jdbcTemplate.update(sql, "张三", 22, "绿色", "狗");
}

注意事项 #

• 方案一使用存储过程，代码更易维护，且避免SQL注入风险，适合复杂业务逻辑。
• 方案二虽然无需创建存储过程，但参数顺序必须严格对应，可读性较差，适合简单场景。

内容的提问来源于stack exchange，提问作者Souvik Dey