Java QueryRunner执行含DO $$块的SQL查询报错：参数数量不匹配

PostgreSQL的DO $$ ... $$匿名代码块属于无参数的匿名过程，JDBC驱动无法识别块内部的?作为预编译参数。当你通过QueryRunner传入2个参数时，数据库端判定该语句不需要参数，因此抛出Wrong number of parameters: expected 0, was given 2错误。

方案一：封装为带参数的PL/pgSQL函数（推荐） #

将业务逻辑封装成带参数的函数，通过调用函数传递参数，完全符合JDBC预编译规范，同时避免SQL注入风险。

1. 创建函数 #

CREATE OR REPLACE FUNCTION delete_related_records(p_name text, p_description text)
RETURNS void AS $$
DECLARE
    declared_id integer;
BEGIN
    -- 先查询获取目标ID
    SELECT id INTO declared_id 
    FROM db.table 
    WHERE name = p_name AND description = p_description;

    -- 执行批量删除
    DELETE FROM db.table2 WHERE id = declared_id;
    DELETE FROM db.table3 WHERE id = declared_id;
    DELETE FROM db.table4 WHERE id = declared_id;
    DELETE FROM db.table5 WHERE id = declared_id;
END;
$$ LANGUAGE plpgsql;

2. Java代码调用 #

修改QueryRunner的调用逻辑，调用上述函数：

import org.apache.commons.dbutils.QueryRunner;
import java.sql.Connection;
import java.sql.DriverManager;

public class DbTest {
    public void executeDelete(String name, String description) throws Exception {
        // 正确获取数据库连接（示例，实际建议用数据源）
        Connection connection = DriverManager.getConnection("jdbc:postgresql://host:port/dbname", "user", "password");
        
        // 调用自定义函数
        String sql = "SELECT delete_related_records(?, ?)";
        new QueryRunner().query(connection, sql, name, description);
        
        connection.close();
    }
}

方案二：在DO块中使用动态SQL+USING子句（临时方案） #

如果无法创建函数，可以通过动态SQL的方式在DO块内部传递参数，但部分JDBC驱动对这种方式的支持可能不稳定：

修改后的SQL语句：

DO $$
DECLARE
    declared_id integer;
BEGIN
    -- 通过动态SQL传递参数，$1、$2对应USING后的参数
    EXECUTE 'SELECT id FROM db.table WHERE name = $1 AND description = $2'
    INTO declared_id
    USING ?, ?;

    DELETE FROM db.table2 WHERE id = declared_id;
    DELETE FROM db.table3 WHERE id = declared_id;
    DELETE FROM db.table4 WHERE id = declared_id;
    DELETE FROM db.table5 WHERE id = declared_id;
END $$

Java代码可沿用原调用逻辑，但需修复代码中的错误：

• 方法名execute query改为合法的executeQuery
• 参数拼写错误descripton改为description
• 不要直接new Connection()，需通过JDBC规范方式（如DriverManager.getConnection()）获取连接

建议使用try-with-resources自动管理连接，避免资源泄漏：

public void executeDelete(String name, String description) throws Exception {
    String sql = "SELECT delete_related_records(?, ?)";
    try (Connection connection = DriverManager.getConnection("url", "user", "pwd")) {
        new QueryRunner().query(connection, sql, name, description);
    }
}

内容的提问来源于stack exchange，提问作者Junjie Wang