I am playing around with hibernate and I have downloaded the following test DB https://dev.mysql.com/doc/employee/en/sakila-structure.html
I have a named query on the employee class of:
@NamedNativeQuery(
name="complexQuery",
query="select * from employees inner join salaries on employees.emp_no=salaries.emp_no where salaries.from_date < 19870101 "
"AND employees.emp_no = 10064;",
resultClass=Employee.class
)
I have mapped the employees to salaries via:
@OneToMany(
cascade = CascadeType.ALL,
fetch = FetchType.EAGER
)
@JoinColumn(name = "emp_no", nullable = false, insertable=false, updatable=false)
private Set<Salary> salaries = new HashSet<>();
I expected that the following hibernate queries would include the where statement of
salaries.from_date < 19870101
however I noticed that actually the hibernate query for the salaries selects all rows for that employee id:
Hibernate:
select
*
from
employees
inner join
salaries
on employees.emp_no=salaries.emp_no
where
salaries.from_date < 19870101
AND employees.emp_no = 10064;
Hibernate:
select
salaries0_.emp_no as emp_no1_4_0_,
salaries0_.from_date as from_dat2_4_0_,
salaries0_.emp_no as emp_no1_4_1_,
salaries0_.from_date as from_dat2_4_1_,
salaries0_.salary as salary3_4_1_,
salaries0_.to_date as to_date4_4_1_
from
salaries salaries0_
where
salaries0_.emp_no=?
Is there anyway to have the auto generated salaries query also include
where
salaries0_.from_date < ?
AND salaries0_.emp_no=?
EDIT: I am also getting the issue for a named query:
@NamedQuery(
name="complexQuery",
query="select e "
"from Employee e, Salary s "
"where e.id = 10064 "
"AND s.id.fromDate < 19900101"
"AND s.id.empNo = 10064"
)
CodePudding user response:
You are not joining Employee and Salary!
That's the correct statement
@NamedQuery(
name="complexQuery",
query="select e "
"from Employee e join e.salaries s "
"where e.id = 10064 "
"AND s.id.fromDate < 19900101"
)