我使用Spring Boot、Apache DBCP 2、PostgreSQL 10,并通过以下方式生成JdbcTemplate
@Bean
public JdbcTemplate getJdbcTemplate() {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
Properties props = new Properties();
props.setProperty(PGProperty.USER.getName(), user);
props.setProperty(PGProperty.PASSWORD.getName(), password);
props.setProperty(PGProperty.APPLICATION_NAME.getName(), applicationName);
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:postgresql://" + host + "/db",
props);
PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);
ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory);
poolableConnectionFactory.setPool(connectionPool);
PoolingDataSource<PoolableConnection> dataSource = new PoolingDataSource<>(connectionPool);
return new JdbcTemplate(dataSource);
}
我通过访问DB
@Autowired
private JdbcTemplate jdbc;
...
UUID uuid1 = UUID.randomUUID();
UUID uuid2 = UUID.randomUUID();
Array array = jdbc.getDataSource().getConnection().createArrayOf("UUID",
new Object[] { uuid1, uuid2 });
jdbc.query("SELECT * FROM my_table WHERE id = ANY (?)", //
ps -> ps.setArray(1, array), //
rs -> {
...
});
每次调用此select查询时,都会创建到DB的新会话。
如果我输入准备好的语句并通过
jdbc.query("SELECT * FROM my_table "
+ "WHERE id IN ('aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa', 'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb')",
rs -> {
...
});
一切正常:不会创建DB的新会话。
如何正确使用准备好的语句,以避免创建新会话?