JPA nativeQuery returns cached resultList

Question

I have following classes:

Company.class:

public class Company {
    @JoinTable(name = "company_employee", joinColumns = @JoinColumn(name = "company_id") , inverseJoinColumns = @JoinColumn(name = "employee_id") )
    @ManyToMany(fetch = FetchType.LAZY)
    private Set<Employee> employees;

    @Column(name = "score")
    private BigDecimal score;
}

and Employee.class

public class Employee {
         @ManyToMany(fetch = FetchType.EAGER, mappedBy="employees")
         private Set<Company> companies;
}

The Score column of Company is always null in the db and never updated via dao, because there is other table containing score for each unique pair Company-Employee. I need the value of Score, only for the case when I fetch Employee by id, so this case all Company instances in the Set should contain score, thus I will get Employee-Company score pairs where employee is fetched Employee. I have following code to achieve that:

public Employee get(Long id) {
    Employee emp = (Employee) dao.find(id);
    List<Company> compList = compnanyService.getByEmpId(id);
    Set<Company> compSet = new HashSet<Company>(compList);
    emp.setCompanies(compSet);
    return emp;
}

And Company Dao contains method:

public List<Company> getByEmpId(Long id) {
        final Query query = this.entityManager.createNativeQuery("select company.comp_id, ...some other fields, score.score from company join score on company.company_id=score.company_id where score.employee_id=:employee_id",
                Company.class);
        query.setParameter("employee_id", id);
        List<Company> comps = query.getResultList();
        return comps;
}

The problem is that getByEmpId(id) gives a ResultList where company.score is null though executed in the db it is not null.

I suspected that there is some caching intervening, so I tried to remove some columns from the native query, and it should have invoked an exception with "no column found" (or alike) message while mapping, but this method still gives List<Company> with all fields on their places though Hibernate prints out my native query in the console with all changes I make. What am I doing wrong here and how to achieve what I need? Thank you.

Why are you using a native query to retrieve the related companies from an Employee Id if you already have a bidirectional @ManyToMany between Employee and Company?It seems that you don't need it. — yersan, Dec 28 '15 at 11:32
Because score is stored in different table, and I need join to retrieve it. Is there other way? — Battle_Slug, Dec 28 '15 at 13:55
You have an Employee entity with a list of Company entities related with that employee: Set companies; If you are able to find the Employee entity by its id, you already have the related Company entities for that Employee. For this reason, I don't understand why you are retrieving from data base a list of Company entities in your public Employee get(Long id) {..} method. — yersan, Dec 28 '15 at 18:01
I changed text of my post to make it more clear. Please read again. Please pay attention to the text of native SQL query. It uses join to substitute Company's score field to Score's score field. Sorry, I have no idea how to increase the extent of clearness further. — Battle_Slug, Dec 28 '15 at 19:02

score 1 · Answer 1 · answered Dec 28 '15 at 14:12

It might be associated with first level cache, which can be out of sync when using native SQL queries. From here:

If you bypass JPA and execute DML directly on the database, either through native SQL queries, JDBC, or JPQL UPDATE or DELETE queries, then the database can be out of synch with the 1st level cache. If you had accessed objects before executing the DML, they will have the old state and not include the changes. Depending on what you are doing this may be ok, otherwise you may want to refresh the affected objects from the database.

So you can try using refresh method from EntityManager.

I think refresh will not work as it doesn't suppose using custom native query, and direct table of Company still contains null in score. Again: the goal is to substitute company.score with score.score, that's why there is native query — Battle_Slug, Dec 28 '15 at 18:54

score 0 · Accepted Answer · answered Dec 28 '15 at 17:17

So I ended up doing that:

Created view in db from the query:

CREATE VIEW companyscore AS select company.comp_id, score.emp_id ...some other fields, score.score from company join score on company.comp_id=score.comp_id;
Created corresponding entity CompanyScore with composite primary id as comp_id and emp_id and created view as table.

Changed Employee entity to:

public class Employee {

    @OneToMany(fetch = FetchType.EAGER) 
    @JoinColumn(name = "emp_id")
     private Set<CompanyScore> companies;

}

This way I not only have score field always consistent, but I can choose set of fields to show as the whole Company class is quite extensive and I don't need all the fields for this particular case.

JPA nativeQuery returns cached resultList

2 Answers2