Hibernate sessions management in Spring Boot JPA application with HikariCP data source

Question

In Spring Boot application with HikariCP dataSource I've execute HQL queries with helper class:

public class QueryExecutor {
    private Session session;

    @Autowired
    private SessionFactory sessionFactory;

    public QueryExecutor getConnection() {
        session = sessionFactory.openSession();
        session.beginTransaction();

        return this;
    }

    public void closeConnection() {
        session.getTransaction().commit();
        session.close();
    }

    public List execute(String hql, Long limit, Long offset) {
        getConnection();

        Query query = session.createQuery(hql);

        List list = query.list();

        closeConnection();

        return list;
    }

It works ok, but when I start using class widely, application starts freezes because Hibernate Session is closed randomly and transaction wait 30 seconds (default timeout value for HikariCP transactions) before get Session is closed error (or currentPersistenceContext is null if I used getCurrentSession() instead openSesssion()).

First of all, I changed open session to getCurrentSession function. But I also need to specify context with @PersistenceContext or hibernate.current_session_context_class=thread in hibernate.cfg.xml. I read that this property better to use with default value. Also I specify hibernate.connection.release_mode=AFTER_TRANSACTION. But that isn't solve a problem.

After all, I changed class like that:

@PersistenceContext
    private EntityManager entityManager;

    @Transactional
    public List execute(String hql, Long limit, Long offset) {
        Query query = entityManager.createQuery(hql);    
        return query.getResultList();
    }

and use javax.persistence.Query instead Hibernate queries. Now it works ok. But is that a correct modifications? All functions worked with execute method of QueryExecutor annotated with @Transactional. As I uderstood, in that case no beginTransaction() needed. But need I close entityManager after execute() ?

SessionFactory used with Hibernate without JPA and EntityManager used with Hibernate JPA technologies?

How can I solve problem without using EntityManager?

Answer 1

You don't need to close transactions manually if you use @Transactional annotation.

But if you use it, I will reccomend you try to use JPA Repositories and wrap in @Transactional annotation the methods of business logic only.

In which case you will no longer need EntityManager and you will be able to create custom complex queries with JpaSpecificationExecutor and JPA Criteria API Queries.