小贝子编程

Postgres 9.6.17 和 JDBC 42.2.1 如何将连接模式切换到'extended'



我有一个Postgres 9.6.17 DBMS和一个简单的Java独立程序(没有Spring(，该程序连接到DBMS并发出查询。根据Postgres文档，查询可以在"简单模式"或"扩展模式"下执行(其中解析和查询重写一次性完成(，但绑定会重复发生。

我有一张简单的customer(cust_id integer, first_name varchar(100), last_name varchar(100))表。我正在执行的代码如下：

Properties props = new Properties();
....//typical properties such as user, password, database etc
props.setProperty("preferQueryMode", "extendedForPrepared");
Connection con = DriverManager.getConnection(url, props);
PreparedStatement st = con.prepareStatement("select * from ecommerce.customer where cust_id = ?");
st.setInt(1, 22);
ResultSet rs = st.executeQuery();

我希望这将在JDBC客户端和DBMS后端之间产生一个"扩展查询模式"协议。但它一直产生一种"简单查询模式"的交互。我通过为postgres驱动程序启用JDBC日志记录来验证这一点。我还查看了JDBC驱动程序中Query接口的实现，我能找到的只有以下3个实现：

  1. BatchedQuery
  2. CompositeQuery
  3. SimpleQuery

尽管名称为CompositeQuery，但它所做的只是将涉及多个语句的查询拆分为SimpleQuery对象的列表。

所以我的问题是，我该怎么做才能使连接到扩展模式时使用的协议？

默认情况下，PostgresJDBC驱动程序只有在准备好的语句使用5次后才会开始使用服务器端语句。来自JDBC驱动程序属性文档：

prepareThreshold=int

在切换到使用服务器端准备的语句之前，确定所需的PreparedStatement执行数。默认值为五，这意味着在第五次执行同一PreparedStatement对象时开始使用服务器端准备好的语句。有关服务器端准备语句的更多信息，请参阅"服务器准备语句"部分。

但该属性可以更改。

这是我测试的程序：

Properties props = new Properties();
props.setProperty("user", "test");
props.setProperty("password", "test");
props.setProperty("loggerLevel", "TRACE");
props.setProperty("prepareThreshold", "1");
try (Connection con = DriverManager.getConnection("jdbc:postgresql:test", props);
PreparedStatement stat = con.prepareStatement("select * from app_user where username = ?"))
{
for (String username : List.of("user1", "user2", "user3"))
{
stat.setString(1, username);
try (ResultSet rs = stat.executeQuery())
{
if (rs.next())
System.out.println("User " + username + " has ID: " + rs.getString(1));
}
}
}

当prepareThreshold设置为1时，日志显示：

Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@33065d67, maxRows=0, fetchSize=0, flags=16
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST:  FE=> Parse(stmt=S_1,query="select * from app_user where username = $1",oids={1043})
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST:  FE=> Bind(stmt=S_1,portal=null,$1=<'user1'>)
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal
FINEST:  FE=> Describe(portal=null)
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST:  FE=> Execute(portal=null,limit=0)
...
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@35dab4eb, maxRows=0, fetchSize=0, flags=16
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST:  FE=> Bind(stmt=S_1,portal=null,$1=<'user2'>)
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST:  FE=> Execute(portal=null,limit=0)
...
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@2d901eb0, maxRows=0, fetchSize=0, flags=16
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST:  FE=> Bind(stmt=S_1,portal=null,$1=<'user3'>)
Feb 18, 2020 1:56:59 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST:  FE=> Execute(portal=null,limit=0)

显示查询解析发生一次，然后有重复绑定。

将其与未设置prepareThreshold时进行比较：

Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@33065d67, maxRows=0, fetchSize=0, flags=17
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST:  FE=> Parse(stmt=null,query="select * from app_user where username = $1",oids={1043})
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST:  FE=> Bind(stmt=null,portal=null,$1=<'user1'>)
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal
FINEST:  FE=> Describe(portal=null)
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST:  FE=> Execute(portal=null,limit=0)
...
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@8519cb4, maxRows=0, fetchSize=0, flags=17
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST:  FE=> Parse(stmt=null,query="select * from app_user where username = $1",oids={1043})
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST:  FE=> Bind(stmt=null,portal=null,$1=<'user2'>)
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal
FINEST:  FE=> Describe(portal=null)
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST:  FE=> Execute(portal=null,limit=0)
...
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@35dab4eb, maxRows=0, fetchSize=0, flags=17
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendParse
FINEST:  FE=> Parse(stmt=null,query="select * from app_user where username = $1",oids={1043})
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendBind
FINEST:  FE=> Bind(stmt=null,portal=null,$1=<'user3'>)
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendDescribePortal
FINEST:  FE=> Describe(portal=null)
Feb 18, 2020 2:01:25 PM org.postgresql.core.v3.QueryExecutorImpl sendExecute
FINEST:  FE=> Execute(portal=null,limit=0)

其中有3个单独的语句解析和绑定。然而，在5次查询之后，它们应该开始被重用。

关于查询模式是"简单"还是"扩展"，看起来日志总是打印出"简单执行"，但不同的是，真正的简单模式参数不会绑定在准备好的语句中，而是整个查询将以文本形式发送。在本例中，如果preferQueryMode属性设置为simple，则日志显示：

Feb 18, 2020 2:06:19 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@33065d67, maxRows=0, fetchSize=0, flags=1,041
Feb 18, 2020 2:06:19 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery
FINEST:  FE=> SimpleQuery(query="select * from app_user where username = 'user1'")
...
Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@28261e8e, maxRows=0, fetchSize=0, flags=1,041
Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery
FINEST:  FE=> SimpleQuery(query="select * from app_user where username = 'user2'")
...
Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl execute
FINEST:   simple execute, handler=org.postgresql.jdbc.PgStatement$StatementResultHandler@d737b89, maxRows=0, fetchSize=0, flags=1,041
Feb 18, 2020 2:06:20 PM org.postgresql.core.v3.QueryExecutorImpl sendSimpleQuery
FINEST:  FE=> SimpleQuery(query="select * from app_user where username = 'user3'")

请注意这与默认值之间的区别-没有参数，每个查询都会内联发送"user1"、"user2"one_answers"user3"值。

我认为您真正想要的是立即重用服务器端语句并重新绑定参数。在这种情况下，将prepareThreshold设置为1即可完成此任务。

相关内容

最新更新


热门标签：

javascript python java c# php android html jquery c++ css ios sql mysql arrays asp.net json python-3.x ruby-on-rails .net sql-server django objective-c excel regex ruby linux ajax iphone xml vba spring asp.net-mvc database wordpress string postgresql wpf windows xcode bash git oracle list vb.net multithreading eclipse algorithm macos powershell visual-studio image forms numpy scala function api selenium