时间戳

首页 > 解决方案 > 如何在jdbc中为事务设置局部变量

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

问题描述

我正在开发一个项目，我需要在其数据库中初始化会话变量。如果我直接使用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();
}

但问题仍然存在。如何SET打电话JDBC

标签： javapostgresqljdbc

解决方案

我认为您需要在DataSourcenot上执行此操作Connection。

我postgresql知道的唯一方法是下变频。就像是：

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

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

推荐阅读