spring boot - datasource not returning used connections

Question

I'm using spring boot with hibernate on PostgreSQL.

DB: PostgreSQL version : 9.6.8

SPRING-boot version :

My Problem:

After two request GET calls , the connections are not available from pool(or new one).

Can some one point out what went wrong? So Below exception is thrown

    java.sql.SQLTransientConnectionException: SpringBootJPAHikariCP - Connection is not available, request timed out after 3000ms.
        at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:667) ~[HikariCP-2.7.8.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:183) ~[HikariCP-2.7.8.jar:na]
        at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:148) ~[HikariCP-2.7.8.jar:na]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-2.7.8.jar:na]
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
Caused by: java.sql.SQLTransientConnectionException: SpringBootJPAHikariCP - Connection is not available, request timed out after 3000ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:667) ~[HikariCP-2.7.8.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:183) ~[HikariCP-2.7.8.jar:na]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:148) ~[HikariCP-2.7.8.jar:na]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-2.7.8.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]

I have set below connection pool properties

    spring.datasource.hikari.minimumIdle=250
spring.datasource.hikari.maximumPoolSize=5
spring.datasource.hikari.idleTimeout=3000
spring.datasource.hikari.poolName=SpringBootJPAHikariCP
spring.datasource.hikari.maxLifetime=500
spring.datasource.hikari.connectionTimeout=3000

DAO class:

    @Component
public class NickNameDAO {


    @Autowired
    private EntityManagerFactory entityManagerFactory;
    
    
    @Transactional
    public List<NickName> getUserDetails() {
        
        
        
        SessionFactory session = AutoWiringUtils.getSessionFactory(entityManagerFactory);
        
        Criteria criteria = session.openSession().createCriteria(NickName.class);
        List<NickName> returnValues = criteria.list();
        return returnValues;
    }
    
    
}

Controller Class:

public class MainController {
@GetMapping(path="/all")
    public @ResponseBody Iterable<NickName> getAllUsers() {
        // This returns a JSON or XML with the users
        return nickNameService.getUserDetails();
    }
}

Service class:

@Service
public class NickNameService {
    
    @Autowired
    private NickNameDAO nickNameDAO;
    
    public  List<NickName> getUserDetails() {
        return nickNameDAO.getUserDetails();

    }

}

session creater class

public class AutoWiringUtils {
    public static SessionFactory getSessionFactory(EntityManagerFactory entityManagerFactory) {
        
        
         
        if (entityManagerFactory.unwrap(SessionFactory.class) == null) {
            throw new NullPointerException("factory is not a hibernate factory");
        }
        return entityManagerFactory.unwrap(SessionFactory.class);
    }

}

Update :1

When using session.getCurrentSession I'm facing below exception

@Component
public class NickNameDAO {
    @Autowired
    private EntityManagerFactory entityManagerFactory;
    
    @Transactional
    public List<NickName> getUserDetails() {
        SessionFactory session = AutoWiringUtils.getSessionFactory(entityManagerFactory);
        Criteria criteria = session.getCurrentSession().createCriteria(NickName.class);
        List<NickName> returnValues = criteria.list();
        return returnValues;
    }
}
javax.persistence.TransactionRequiredException: no transaction is in progress
    at org.hibernate.internal.SessionImpl.checkTransactionNeeded(SessionImpl.java:3466) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.internal.SessionImpl.doFlush(SessionImpl.java:1426) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1422) ~[hibernate-core-5.2.16.Final.jar:5.2.16.Final]
    at org.springframework.orm.hibernate5.SessionFactoryUtils.flush(SessionFactoryUtils.java:147) ~[spring-orm-5.0.5.RELEASE.jar:5.0.5.RELEASE]

That is some mess! You are using @Transactional annotation opening a transation with the entityManager and then opening a session unwrapping the entityManagerFactory! just choose one for the sake of that poor connection pooler — Zeromus, May 09 '18 at 14:56
e.g. if you want to use criteria just remove the @Transactional (that is spring data jpa) and dont create an entity manager (jpa) at all and just unwrap the Session (hibernate specific) from the emf — Zeromus, May 09 '18 at 14:58
ok then can you help how to get SessionFactory without creating entity manager. — theRoot, May 09 '18 at 15:00
SessionFactory session = AutoWiringUtils.getSessionFactory(entityManagerFactory); is fine just dont get the EntityManager or begin the trasnaction with it em.getTransaction().begin() — Zeromus, May 09 '18 at 15:01
Well no because you are opening sessions yourself. Why have you made that contraption? Just use the `EntityManager` or at least never use `openSession` but use `getCurrentSession` . However imho you shouldn't be messing around with the plain Hibernate API at all. — M. Deinum, May 09 '18 at 15:20
@M.Deinum update the question , I tried to use getCurrentSession and I'm facing exception — theRoot, May 09 '18 at 16:49
Which indicates you are missing a setting in your configuration. But why are you using the plain Hibernate API in the first place. Instead just use plain JPA. — M. Deinum, May 09 '18 at 17:29
how can i query criteria without SessionFactory object? I tried to follow this(https://stackoverflow.com/questions/44278066/how-to-use-criteria-queries-in-spring-boot-data-jpa-application) — theRoot, May 10 '18 at 06:13
@Zeromus seems criteria is depreciated . Thanks will try to use your link — theRoot, May 10 '18 at 07:28

spring boot - datasource not returning used connections

0 Answers0