java - 如何在jdbc中为事务设置局部变量

Question

我正在处理一个项目，我需要在该项目的数据库中初始化一个 session 变量。如果我直接使用 sql，初始化是用 SET 语句完成的

set local app.user_id to "0000";

我尝试用 Connection#setClientInfo() 初始化它但失败了

try(Connection connection = getDataSource().getConnection()) {
    boolean isAutoCommit = connection.getAutoCommit();
    try {
        Properties properties = new Properties();
        properties.put("app.user_id", "0000");

        connection.setAutoCommit(false);
        connection.setClientInfo(properties);

        String query = "insert into positions (name, description) values (?, ?)";

        try(PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, position.getName());
            statement.setString(2, position.getDescription());

            statement.executeUpdate();
        }

        connection.commit();
    }
    catch (SQLException e) {
        e.printStackTrace();
        connection.rollback();
    }
    finally {
        connection.setAutoCommit(isAutoCommit);
    }
}

我得到PSQLException(插入查询依赖于参数，它没有通过)

org.postgresql.util.PSQLException: ERROR: unrecognized configuration parameter "app.user_id"

如果我使用 PreparedStatement，我会得到 PSQLException 消息 ERROR: syntax error at or near "$1"

try(Connection connection = getDataSource().getConnection()) {
    boolean isAutoCommit = connection.getAutoCommit();
    try {
        connection.setAutoCommit(false);
        try(PreparedStatement statement = connection.prepareStatement("set local app.user_id to ?")) {
            statement.setString(1, "0000");
            statement.execute();
        }

        String query = "insert into positions (name, description) values (?, ?)";

        try(PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, position.getName());
            statement.setString(2, position.getDescription());

            statement.executeUpdate();
        }

        connection.commit();
    }
    catch (SQLException e) {
        e.printStackTrace();
        connection.rollback();
    }
    finally {
        connection.setAutoCommit(isAutoCommit);
    }
}

唯一的方法是直接执行具有固定值的查询。但在这样做时，我不得不使用串联来构建查询。我不想这样做。

try(Connection connection = getDataSource().getConnection()) {
    boolean isAutoCommit = connection.getAutoCommit();
    try {
        connection.setAutoCommit(false);
        try(Statement statement = connection.createStatement()) {
            statement.execute("set local app.user_id to 0000");
        }

        String query = "insert into positions (name, description) values (?, ?)";

        try(PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setString(1, position.getName());
            statement.setString(2, position.getDescription());

            statement.executeUpdate();
        }

        connection.commit();
    }
    catch (SQLException e) {
        e.printStackTrace();
        connection.rollback();
    }
    finally {
        connection.setAutoCommit(isAutoCommit);
    }
}

初始化这些参数的正确方法是什么？我使用 PostgreSQL 11、JDBC 4.2(带有驱动程序 42.2.5)和 DBCP 2.5

编辑

我是通过调用 set_config 来完成的。

try(PreparedStatement statement = connection.prepareStatement("select set_config(?, ?, true)")) {
    statement.setString(1, "app.user_id");
    statement.setString(2, "0000");

    statement.execute();
}

但问题依然存在。如何在JDBC

中调用 SET

Answer 1

我认为您需要在 DataSource 而不是 Connection 上执行此操作。

在 postgresql 中，我所知道的唯一方法是下转换。像这样的东西:

DataSource myDS = getDataSource();
if (DataSource instanceof BaseDataSource.class) {
    BaseDataSource pgDS = (BaseDataSource) myDS;   // expose setProperty method
    pgDS.setProperty("app.user_id", "0000");
}

您将其放置在您的应用程序中的位置显然取决于您的问题中未提供的许多细节。