Home > database >  Hibernate JoinColumn annotation not restricting on named query where clause
Hibernate JoinColumn annotation not restricting on named query where clause

Time:02-14

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"
            )
  • Related