Guice-persist: inconsistent data from EntityManagers

Question

I have a problem with the persistence in a java web application. We use Ninjaframework (6.8.1) which uses Guice as dependency injection framework and for persisting objects. I encountered two problems which seem to be pointing to the same error.

• The data is sometimes wrong after an update operation although they are correct in the database. That means after editing e.g. a customer entity, the show method delivers either the new data or the old one, usually the data appear alternating.
• After a long time, I run into an application error, stating that too many connections to the database backend are used.

It looks like the issue in this question [1] is discussed, but unfortunately I am not sure how to solve the issues.

The listed code shows how we access the data via an EntityManager:

package dao.organization.customer;

import java.math.BigInteger;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Path;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Selection;
import javax.persistence.criteria.Subquery;

import com.fasterxml.jackson.databind.JsonNode;
import com.google.inject.Inject;
import com.google.inject.Provider;
import com.google.inject.persist.Transactional;

import edu.stanford.nlp.util.Triple;
import models.Account;
import models.Organization;
import models.Customer;
import models.CustomerAndCustomerLocationLink;
import models.CustomerLocation;
import ninja.i18n.Messages;
import ninja.postoffice.Mail;
import ninja.postoffice.Postoffice;

public class CustomerDao {

    @Inject
    Provider<EntityManager> entityManagerProvider;
    
    @Inject
    Provider<Mail> mailProvider;

    @Inject
    Postoffice postoffice;
    
    Messages msg;

    @Inject
    CustomerDao(Messages msg) {
        this.msg = msg;
    }

    /**
     * Get single result without throwing NoResultException, you can of course just catch the
     * exception and return null, it's up to you.
     */
    private static <T> T getSingleResult(TypedQuery<T> query) {
        query.setMaxResults(1);
        List<T> list = query.getResultList();
        if (list == null || list.isEmpty()) {
            return null;
        }
        return list.get(0);
    }
    
    @Transactional
    public Long addCustomerToDB(Customer customer) {
        EntityManager entityManager = entityManagerProvider.get();
        entityManager.persist(customer);
        return customer.id;
    }
    
    // read-only
    @Transactional
    public Customer getCustomerByID(Long id) {
        EntityManager entityManager = entityManagerProvider.get();
        TypedQuery<Customer> q = entityManager.createQuery("SELECT x FROM Customer x WHERE id = :idParam", Customer.class);
        Customer customer = getSingleResult(q.setParameter("idParam", id));
        return customer;
    }

    @Transactional
    public void deleteByID(Long id) {
        Customer customer = getCustomerByID(id);
        EntityManager entityManager = entityManagerProvider.get();
        TypedQuery<Customer> q = entityManager.createQuery("SELECT x FROM Customer x WHERE id = :customerID", Customer.class);
        Customer a = getSingleResult(q.setParameter("customerID", customer.id));
        entityManager.remove(a);
    }

    @Transactional
    public void editCustomer(Customer oldCustomerObj, pojo.controllers.organization.Customer customer_dict) {
        EntityManager em = entityManagerProvider.get();
        TypedQuery<Customer> q = em.createQuery("SELECT x FROM Customer x WHERE id = :idParam", Customer.class);
        Customer oldCustomer = getSingleResult(q.setParameter("idParam", oldCustomerObj.id));
        
        oldCustomer.customer_number = customer_dict.customer_number;
        
        oldCustomer.customer_name = customer_dict.customer_name;
        oldCustomer.customer_name_hint = customer_dict.customer_name_hint;
        
        oldCustomer.street = customer_dict.street;
        oldCustomer.house_number = customer_dict.house_number;
        oldCustomer.postal_code = customer_dict.postal_code;
        oldCustomer.city = customer_dict.city;
        oldCustomer.country = customer_dict.country;

        oldCustomer.contact_email = customer_dict.contact_email;
        oldCustomer.contact_phone = customer_dict.contact_phone;
        oldCustomer.comment = customer_dict.comment;
        
        em.flush();
    }

    // read-only
    @Transactional
    public List<Customer> getCustomerList(int page_size, int page_number, String order_by_field, String order_asc_or_desc, String search_expression) {
        EntityManager entityManager = entityManagerProvider.get();
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = cb.createQuery(Customer.class);
        Root<Customer> customer = criteriaQuery.from(Customer.class);
        criteriaQuery.select(customer);
        
        System.out.println("search_expression: " + search_expression);
        
        if(search_expression != null && !search_expression.equals("")) {
            
            Predicate id;
            Predicate customer_number;
            
            try {
                id = cb.equal(customer.get("id").as(Long.class), Long.parseLong(search_expression));            
                customer_number = cb.equal(customer.get("customer_number").as(Integer.class), Integer.parseInt(search_expression));
            } catch (NumberFormatException e) {
                System.out.println(e.getMessage());
                // or without args is always false
                id = cb.or();
                customer_number = cb.or();
            }
            
            Predicate customer_name = cb.like(customer.get("customer_name"), search_expression);
            Predicate customer_name_hint = cb.like(customer.get("customer_name_hint"), search_expression);
            Predicate street = cb.like(customer.get("street"), search_expression);
            Predicate house_number = cb.like(customer.get("house_number"), search_expression);
            Predicate postal_code = cb.like(customer.get("postal_code"), search_expression);
            Predicate city = cb.like(customer.get("city"), search_expression);
            Predicate country = cb.like(customer.get("country"), search_expression);
            Predicate contact_email = cb.like(customer.get("contact_email"), search_expression);
            Predicate contact_phone = cb.like(customer.get("contact_phone"), search_expression);
            Predicate comment = cb.like(customer.get("comment"), search_expression);
            
            Predicate exp1 = cb.or(
                    id,
                    customer_number,
                    customer_name,
                    customer_name_hint,
                    street,
                    house_number,
                    postal_code,
                    city,
                    country,
                    contact_email,
                    contact_phone,
                    comment
            );
            
            criteriaQuery.where(exp1);
        }
        
        if(order_by_field != null && order_asc_or_desc.equalsIgnoreCase("DESC")) {
            criteriaQuery.orderBy(cb.desc(customer.get(order_by_field)));
        } else if(order_by_field != null) {
            criteriaQuery.orderBy(cb.asc(customer.get(order_by_field)));
        }
        
        Query q = entityManager.createQuery(criteriaQuery);

        System.out.println(order_by_field);
        q.setFirstResult(page_number);
        q.setMaxResults(page_size);

        @SuppressWarnings("unchecked")
        List<Customer> customerResultList = q.getResultList();

        System.out.println(customerResultList.toString());
        
        for (Customer a: customerResultList) {
            System.out.println(a.properties.toString());
        }
        
        return customerResultList;
    }
    
    // read-only
    @Transactional
    public Long getCustomerCount() {
        EntityManager entityManager = entityManagerProvider.get();
        Query query = entityManager.createNativeQuery(
                "SELECT COUNT(id) FROM Customer");
        Long count = ((BigInteger) query.getSingleResult()).longValue();
        return count;
    }

    // read-only
    @Transactional
    public Triple<Long, List<Customer>, Object> getCustomerListViaCustomerLocationID(Long customer_location_id, int page_size, int page_number, String order_by_field, String order_asc_or_desc, String search_expression) {
        EntityManager entityManager = entityManagerProvider.get();
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = cb.createQuery(Customer.class);
        Root<Customer> customer = criteriaQuery.from(Customer.class);
        criteriaQuery.select(customer);
        
        Subquery<CustomerAndCustomerLocationLink> subquery = criteriaQuery.subquery(CustomerAndCustomerLocationLink.class);
        Root<CustomerAndCustomerLocationLink> dept = subquery.from(CustomerAndCustomerLocationLink.class);
        subquery.select(dept.get("customer_id")).where(cb.equal(dept.get("customer_location_id"), customer_location_id));   
        
        
        CriteriaBuilder cbForCounter = entityManager.getCriteriaBuilder();
        CriteriaQuery<Long> criteriaQueryCounter = cbForCounter.createQuery(Long.class);
        criteriaQueryCounter.select(cbForCounter.count(criteriaQueryCounter.from(Customer.class)));
        Subquery<CustomerAndCustomerLocationLink> subqueryForCount = criteriaQueryCounter.subquery(CustomerAndCustomerLocationLink.class);
        Root<CustomerAndCustomerLocationLink> deptForCount = subqueryForCount.from(CustomerAndCustomerLocationLink.class);
        subqueryForCount.select(deptForCount.get("customer_id")).where(cbForCounter.equal(deptForCount.get("customer_location_id"), customer_location_id)); 

        
        
        Predicate subQueryPredicate = cb.in(customer.get("id")).value(subquery);
        
        if(search_expression != null && !search_expression.equals("")) {
            
            Predicate id;
            Predicate customer_number;
            
            try {
                id = cb.equal(customer.get("id").as(Long.class), Long.parseLong(search_expression));            
                customer_number = cb.equal(customer.get("customer_number").as(Integer.class), Integer.parseInt(search_expression));
            } catch (NumberFormatException e) {
                System.out.println(e.getMessage());
                // or without args is always false
                id = cb.or();
                customer_number = cb.or();
            }
            
            Predicate customer_name = cb.like(customer.get("customer_name"), search_expression);
            Predicate customer_name_hint = cb.like(customer.get("customer_name_hint"), search_expression);
            Predicate street = cb.like(customer.get("street"), search_expression);
            Predicate house_number = cb.like(customer.get("house_number"), search_expression);
            Predicate postal_code = cb.like(customer.get("postal_code"), search_expression);
            Predicate city = cb.like(customer.get("city"), search_expression);
            Predicate country = cb.like(customer.get("country"), search_expression);
            Predicate contact_email = cb.like(customer.get("contact_email"), search_expression);
            Predicate contact_phone = cb.like(customer.get("contact_phone"), search_expression);
            Predicate comment = cb.like(customer.get("comment"), search_expression);
            
            Predicate exp1 = cb.or(
                    id,
                    customer_number,
                    customer_name,
                    customer_name_hint,
                    street,
                    house_number,
                    postal_code,
                    city,
                    country,
                    contact_email,
                    contact_phone,
                    comment
            );
            
            
            Predicate both = cb.and(exp1, subQueryPredicate);
            
            criteriaQuery.where(both);
            criteriaQueryCounter.where(both);
            
        } else {
            criteriaQuery.where(subQueryPredicate);
            criteriaQueryCounter.where(subQueryPredicate);
        }
        
        if(order_by_field != null && order_asc_or_desc.equalsIgnoreCase("DESC")) {
            criteriaQuery.orderBy(cb.desc(customer.get(order_by_field)));
        } else if(order_by_field != null) {
            criteriaQuery.orderBy(cb.asc(customer.get(order_by_field)));
        }
        
        Query q = entityManager.createQuery(criteriaQuery);
        Query qCounter = entityManager.createQuery(criteriaQueryCounter);

        System.out.println(order_by_field);
        q.setFirstResult(page_number);
        q.setMaxResults(page_size);

        @SuppressWarnings("unchecked")
        List<Long> counterList = qCounter.getResultList();
        System.out.println(counterList);
        
        assert(counterList.size() == 1);
        
        Long counter = counterList.get(0);
        
        @SuppressWarnings("unchecked")
        List<Customer> customerResultList = q.getResultList();

        System.out.println(customerResultList.toString());
        
        for (Customer a: customerResultList) {
            System.out.println(a.properties.toString());
        }
        
        return new Triple<Long, List<Customer>, Object>(counter, customerResultList, null);
        //return customerResultList;
    }
}

I tried to use the read-only methods via @UnitOfWork, but it is not possible to create nested UnitOfWOrk-methods. Using Transactional allows nested use of the functions. Initially, I tried to use the methods without any annotation, but this also did not work.

It is possible to supply more code examples, although the most DAO objects look like the attached code listing. Maybe this information is also important: I still have some methods that use the entityManager and the provider without being in a UnitOfWork or in a Transactional-annotated method. According to the JavaDoc it should be ok to do this although it is not recommended (Basically used because UnitOfWork is not nestable).

I have no idea how to deal with these issues, it would be great if someone with more experience is able to help. I would even rather deactivate the first level cache from guice before ending up with inconsistent data.

[1] JPA & Guice-persist: permanently opened connection issue?