如何重用与Spring的JdbcTemplate相同的连接？

java jdbc spring

2022-09-01 13:43:25

我有以下代码：


    @Test
    public void springTest() throws SQLException{
        //Connect to the DB.
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUrl("jdbc:h2:/data/h2/testa");
        dataSource.setUsername("");
        dataSource.setPassword("");
        JdbcTemplate jt=new JdbcTemplate(dataSource);
        jt.execute("SELECT 1");
        jt.execute("SELECT 1");
    }

我希望两个 execute（）行重用相同的连接。但是，日志输出显示：

2011-02-10 12:24:17 DriverManagerDataSource [INFO] Loaded JDBC driver: org.h2.Driver
2011-02-10 12:24:17 JdbcTemplate [DEBUG] Executing SQL statement [SELECT 1]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Fetching JDBC Connection from DataSource
2011-02-10 12:24:17 DriverManagerDataSource [DEBUG] Creating new JDBC DriverManager Connection to [jdbc:h2:/data/h2/testa]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Returning JDBC Connection to DataSource
2011-02-10 12:24:17 JdbcTemplate [DEBUG] Executing SQL statement [SELECT 1]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Fetching JDBC Connection from DataSource
2011-02-10 12:24:17 DriverManagerDataSource [DEBUG] Creating new JDBC DriverManager Connection to [jdbc:h2:/data/h2/testa]
2011-02-10 12:24:17 DataSourceUtils [DEBUG] Returning JDBC Connection to DataSource

上面的示例运行得相当快，但我有一段更大的代码，它基本上做同样的事情，并在上挂起了很长时间。我从来没有遇到过错误，但它使代码运行得非常慢。我可以以某种方式重构上面的代码以仅使用相同的连接吗？Creating new JDBC DriverManager Connection

谢谢

答案 1

Spring提供了一个特殊的数据源，允许您执行此操作：SingleConnectionDataSource。

将代码更改为此代码应该可以解决问题：

SingleConnectionDataSource dataSource = new SingleConnectionDataSource();
....
// The rest stays as is

为了在多线程应用程序中使用，可以通过从池中借用新连接并将其包装在数据库密集型代码部分来使代码重新进入：

// ... this code may be invoked in multiple threads simultaneously ...

try(Connection conn = dao.getDataSource().getConnection()) {
    JdbcTemplate db = new JdbcTemplate(new SingleConnectionDataSource(conn, true));

    // ... database-intensive code goes here ... 
    // ... this code also is safe to run simultaneously in multiple threads ...
    // ... provided you are not creating new threads inside here
}

答案 2

下面是一个使用 Apache DBCP 的示例：-

BasicDataSource dbcp = new BasicDataSource();
dbcp.setDriverClassName("com.mysql.jdbc.Driver");
dbcp.setUrl("jdbc:mysql://localhost/test");
dbcp.setUsername("");
dbcp.setPassword("");

JdbcTemplate jt = new JdbcTemplate(dbcp);
jt.execute("SELECT 1");
jt.execute("SELECT 1");

log4j 输出为：-

[DEBUG] [JdbcTemplate] [execute:416] - Executing SQL statement [SELECT 1]
[DEBUG] [DataSourceUtils] [doGetConnection:110] - Fetching JDBC Connection from DataSource
[DEBUG] [DataSourceUtils] [doReleaseConnection:332] - Returning JDBC Connection to DataSource
[DEBUG] [JdbcTemplate] [execute:416] - Executing SQL statement [SELECT 1]
[DEBUG] [DataSourceUtils] [doGetConnection:110] - Fetching JDBC Connection from DataSource
[DEBUG] [DataSourceUtils] [doReleaseConnection:332] - Returning JDBC Connection to DataSource