How to reuse the same connection with a Spring's JdbcTemplate?

Question

I have the following code:


    @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");
    }

I expect the two execute() lines to reuse the same connection. However, the log output says:

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

The above example runs quite fast but I have a larger piece of code that does basically the same thing and hangs for a long time on Creating new JDBC DriverManager Connection. I never get an error but it makes the code run very slowly. Can I somehow refactor the above code to just use the same connection?

Thanks

score 35 · Answer 1 · edited Oct 28 '16 at 15:45

35

Spring provides a special DataSource that allows you to do this: SingleConnectionDataSource

Changing your code to this should do the trick:

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

For use in multi-threaded applications, you can make the code re-entrant by borrowing a new connection from the pool and wrapping it around the database-intensive section of code:

// ... 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
}

edited Oct 28 '16 at 15:45

Alex R

11,364
15
100
180

answered Feb 10 '11 at 18:52

Axel Fontaine

34,542
16
106
137

2

This can't be used because SingleConnectionDataSource is not thread safe from its java doc "Obviously, this is not multi-threading capable". And since individual test methods are executed in their own thread by junit the OP has to setup the SingleConnectionDataSource in each method which defeats the perf he is looking for – Aravind Yarram Feb 10 '11 at 19:02
4

The use case described in the question in no way alludes to multi-threading support being a requirement. – Axel Fontaine Feb 10 '11 at 19:13
5

Isn't using the same connection in multiple threads not thread-safe, by definition? – matt b Feb 10 '11 at 19:40
You can make it thread-safe by creating a connection when needed, and then re-using it within the thread. See my edit. – Alex R Oct 28 '16 at 15:37
i compared DriverManagerDataSource, SingleConnectionDataSource , BasicDataSource (commons.dbcp) and your suggestion SingleConnectionDataSource is the laziest one – Tiago Medici Feb 06 '20 at 12:55
Is the connection returned from `dao.getDataSource().getConnection()` guaranteed to be not shared with other database requests even when using Hikari connection pool? Will Hikari not hand this connection out to other threads while we are in the `try` block? – fap Dec 01 '22 at 09:53

score 22 · Accepted Answer · answered Feb 10 '11 at 18:58

22

Here's an example using 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");

The log4j output is:-

[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

answered Feb 10 '11 at 18:58

limc

39,366
20
100
145

That's an easy change and it seemed to have worked. Is it thread-safe? – User1 Feb 10 '11 at 20:16
2

It is thread-safe, because this is what a connection pool is all about. :) I used this in my test cases too, granted I wire the `dataSource` instead of programmatically creating it. – limc Feb 10 '11 at 20:22
2

It's time for a big celebration. I buy beer. :) – limc Feb 10 '11 at 21:20
Weird, on another machine it didn't help the speed at all. I'm using H2 embedded..any hints? – User1 Feb 10 '11 at 22:56
How does that exactly ensure the same connection is used? Couldn't the DataStore provided by dbcp dependency return a new connection to the JdbcTemplate? Sort of the purpose of a pool of connections isn't it? – Nicholi Mar 13 '12 at 23:51
it is thread-safe, so long as you don't use a sub-classed version of it: https://issues.apache.org/jira/browse/DBCP-285 – Matt S. Oct 08 '15 at 23:55
The answer with Spring's SingleConnectionDataSource class is more idiomatic in the context of Spring. Not to mention that there's absolutely no need to rely on DBCP classes when pooling is not desired in the first place. – Alex Jul 26 '22 at 09:16

coderz · Answer 3 · 2016-08-18T14:21:27.720

In one word, Spring JDBCTemplate DriverManagerDataSource does not support connection pool. If you want to use connection pool, DBCP and C3P0 are both good choices.

Let's go through JDBCTemplate source code to see why...

No matter call update, queryForObject and other methods, they finally will call execute method:

    @Override
    public <T> T execute(ConnectionCallback<T> action) throws DataAccessException {
        Assert.notNull(action, "Callback object must not be null");

        Connection con = DataSourceUtils.getConnection(getDataSource());
        try {
            Connection conToUse = con;
            if (this.nativeJdbcExtractor != null) {
                // Extract native JDBC Connection, castable to OracleConnection or the like.
                conToUse = this.nativeJdbcExtractor.getNativeConnection(con);
            }
            else {
                // Create close-suppressing Connection proxy, also preparing returned Statements.
                conToUse = createConnectionProxy(con);
            }
            return action.doInConnection(conToUse);
        }
        catch (SQLException ex) {
            // Release Connection early, to avoid potential connection pool deadlock
            // in the case when the exception translator hasn't been initialized yet.
            DataSourceUtils.releaseConnection(con, getDataSource());
            con = null;
            throw getExceptionTranslator().translate("ConnectionCallback", getSql(action), ex);
        }
        finally {
            DataSourceUtils.releaseConnection(con, getDataSource());
        }
    }

It calls DataSourceUtils.getConnection method to get connection and DataSourceUtils.releaseConnection to release connection.

From DataSourceUtils source code, we see Connection con = dataSource.getConnection(); and con.close();.

Which means get connection operation is defined by implementing DataSource interface, and close connection operation is defined by implementing Connection interface. This allows other DataSource/Connection implementations easily inject to Spring JDBCTemplate.

The DataSource implementation in Spring JDBCTemplate is DriverManagerDataSource. From:

protected Connection getConnectionFromDriverManager(String url, Properties props) throws SQLException {
    return DriverManager.getConnection(url, props);
}

And

public static void doCloseConnection(Connection con, DataSource dataSource) throws SQLException {
    if (!(dataSource instanceof SmartDataSource) || ((SmartDataSource) dataSource).shouldClose(con)) {
        con.close();
    }
}

We see each time it returns a new connection, and close current connection. That's why it does not support connection pool.

While in DBCP, the DataSource implementation is PoolingDataSource, we see getConnection() is from a connection pool; the Connection implementation is PoolableConnection, we see close() method is not to close connection, instead it returns the connection to connection pool.

That's the magic!

score 6 · Answer 4 · edited Oct 06 '11 at 20:47

6

You need the calls to be wrapped in a single transaction. Typically you'd do this with Spring's AOP + @Transactional annotation in an application. You can also do it programmatically with a PlatformTranactionManager, a TransactionTemplate and wrapping the code to execute in a TransactionCallback. See the transaction documentation.

edited Oct 06 '11 at 20:47

reevesy

3,452
1
26
23

answered Feb 10 '11 at 19:07

ColinD

108,630
30
201
202

score 5 · Answer 5 · edited Dec 08 '15 at 02:12

5

Looking at the Spring's code this is my understanding at a high level.

You are creating a DriverManagerDataSource. This internally uses DataSourceUtils to get a connection. And it only reuses the connection if there is an active transaction in progress. So if you run both the executes in a single transaction then it will use the same connection. Or you can also use pooling with 1 connection so that a single connection is created and reused.

edited Dec 08 '15 at 02:12

Turophile

3,367
1
13
21

answered Feb 10 '11 at 18:48

Aravind Yarram

78,777
46
231
327

1

@shams Links updated. And you can also see my answer from source code level :) – coderz Dec 08 '15 at 01:46

score 0 · Answer 6 · answered Nov 11 '16 at 08:54

0

I know this is situational (depending on which feature set you want to use), but you could simply use the JdbcTemplate.batchUpdate methods.

answered Nov 11 '16 at 08:54

skirsch

1,640
12
24

1

I think this is useful for Inserts and Updates, but not Selects unless there's a clever way in which you could obtain results from this... but in that case, a more well defined SQL statement would probably be more efficient. – J E Carter II Jan 05 '18 at 18:49

How to reuse the same connection with a Spring's JdbcTemplate?

6 Answers6

Linked