Too many connections spring boot jdbc

Question

I know this is going to be the repetitive question , but I feel my question is bit different.

I have JdbcDAO classes like

    @Component
    public class JdbcUserDAO implements UserDAO {
    @Autowired
    MyJdbc myJdbc;
   }

I have defined the MyJdbc class as follows :

@Component
public class MyJdbc {

@Autowired
    protected JdbcTemplate jdbc;

}

In the MyJdbc class I am defining the insert and batchupdate and calling them through jdbc variable. Will it create too many connections exceptions.

I have defined the jdbc parameters in application.properties file :

spring.datasource.url=#databaseurl
spring.datasource.username=#username
spring.datasource.password=#password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.test-on-borrow=true
spring.datasource.max-active=100
spring.datasource.max-wait=10000
spring.datasource.min-idle=10
spring.datasource.validation-query=SELECT 1
spring.datasource.time-between-eviction-runs-millis= 5000
spring.datasource.min-evictable-idle-time-millis=30000
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.test-on-return=false

I am getting the exception :

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

I have done many changes to the application.properties file for various constant but it didn't work. My db is hosted on AWS RDS.

But for updating the blob image values I do :

blob= myJdbc.jdbc.getDataSource().getConnection().createBlob();
            blob.setBytes(1, str.getBytes());
            pstmt = myJdbc.jdbc.getDataSource().getConnection().prepareStatement("update user_profile set profileImage=? where user_profile.id in ( select id from user_login where email=?)");

The error is from MySQL, not your app, do you know how many connections your RDS instance is able to cope with? My guess is that it is less than 100. — Dave Bower, Apr 21 '16 at 08:58
Do you have some reason for using plain Jdbc API instead of Spring Data or JPA? — Aritz, Apr 21 '16 at 09:12
@DaveBower : I did show global variables like 'max_connections'; It shows 66 — Chetan, Apr 21 '16 at 09:19
Then there's a much simpler way to manage the persistence layer in spring-boot. Go with [spring data JPA](https://spring.io/guides/gs/accessing-data-jpa/). — Aritz, Apr 21 '16 at 09:23
Then make sure the connections are being closed everytime you use them. If I where you I would try to setup a connection pool at least. — Aritz, Apr 21 '16 at 09:27
@XtremeBiker : Spring boot closes the connections , or do we need to specify the connection close. — Chetan, Apr 21 '16 at 09:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109780/discussion-between-chetan-and-xtreme-biker). — Chetan, Apr 21 '16 at 09:33
@XtremeBiker : Does it mean I need add querytimeout before querying — Chetan, Apr 21 '16 at 09:35
Check in your DB engine how many connections you've got opened. If there are a lot of them, problem is connections aren't being closed so you need to call `close` explicitly. — Aritz, Apr 21 '16 at 09:38
Prefer using `JdbcTemplate#execute(ConnectionCallback)` to execute sql statement in a connection. `myJdbc.jdbc.getDataSource().getConnection()` is not safe because it can interfer with an existing transaction and you must close the connection once used. — Nicolas Labrot, Apr 21 '16 at 09:45
Your code is flawed. It opens *2* additional connections next to the Spring managed one. Never obtain a connection like this? Wrap the code in a `ConnectionCallback` if you really need a connection don't try to get it yourself!. — M. Deinum, Apr 21 '16 at 09:54
@M.Deinum : Is there any example of it , how does it open 2 additional connections ? — Chetan, Apr 21 '16 at 10:17
Because you are doing `getConnection` 2 times, each leading to a new connection outside of the scope and management of spring. — M. Deinum, Apr 21 '16 at 10:20

M. Deinum · Answer 1 · 2016-04-21T10:27:38.837

blob= myJdbc.jdbc.getDataSource().getConnection().createBlob();
blob.setBytes(1, str.getBytes());
pstmt = myJdbc.jdbc.getDataSource().getConnection().prepareStatement("update user_profile set profileImage=? where user_profile.id in ( select id from user_login where email=?)");

The problem is with your code. That code opens 2 additional connections to the database without closing them. You are opening connections yourself then you should also close them. However it is better to use a ConnectionCallback in those cases.

myJdbc.execute(new ConnectionCallback() {
    public Object doInConnection(Connection con) throws SQLException, DataAccessException {
        blob = con.createBlob();
        blob.setBytes(1, str.getBytes());
       pstmt = con.prepareStatement("update user_profile set profileImage=? where user_profile.id in ( select id from user_login where email=?)");
       return null;
    }
});

However it is even easier to use Spring JDBCs Blob support (see the reference guide). That way you don't need to mess around with connections and blobs yourself.

final String query = "update user_profile set profileImage=? where user_profile.id in ( select id from user_login where email=?)";
myJdbc.jdbc.execute(query, new AbstractLobCreatingPreparedStatementCallback(lobHandler) { 1
    protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException {
        byte[] bytes = str.getBytes();
        ps.setString(2, email);
        lobCreator.setBlobAsBinaryStream(ps, 1, str.getBytes()); 
    }
});

I will try this one , but am I using my customized DAO classes to query is it the right way or that is also opening up the multiple connections. — Chetan, Apr 21 '16 at 10:23
Use the jdbc template... Every place you do a `getCOnnection` yourself (and basically bypassing spring and its transaction and resource management) yu are managing connections yourself. Use the JdbcTemplate correctly. Work with the framework not around it. — M. Deinum, Apr 21 '16 at 10:28
As seen in JDBC blob support , how to read back the stored value of the blob and convert back to string. — Chetan, Apr 21 '16 at 16:45

Too many connections spring boot jdbc

1 Answers1