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.

Answer 1

So I ended up doing that:

Created view in db from the query:

1. 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;

2. Created corresponding entity CompanyScore with composite primary id as comp_id and emp_id and created view as table.

3. 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.