Hi,
I have two entities which are associated by a bidirectional one-to-many-association usind JPA:
Code:
@Entity
@Table("DEPARTMENT")
class Departmant{
@Id
@Column("NAME")
private String name;
@OneToMany(mappedBy="department",fetch=FetchType.LAZY)
private List<Professor> professors;
}
@Entity
@Table("PROFESSOR")
class Professor{
@Id
@ManyToOne
@JoinColumn("department_name")
private Department department;
@Id
@Column("EMP_ID")
private Integer employeeId;
}
DDL:
CREATE TABLE DEPARTMENT (VARCHAR NAME PRIMARY KEY);
CREATE TABLE PROFESSOR (VARCHAR DEPARTMENT_NAME PRIMARY KEY, INTEGER EMP_ID PRIMARY KEY);
In my current use-case I have to select all professors for a given department. The departments aren't needed, I just want to print the employeeIds.
My first guess was
Code:
SELECT p FROM professor where p.department=:depName
Obviously this was wrong, because :depName has the wrong type. Second try was
Code:
SELECT p FROM professor p JOIN department d WHERE d.name=:depName
This works, but results in a unneccesary JOIN:
Code:
SELECT department_name, emp_id FROM professor p JOIN department d on p.department_name=d.name WHERE d.name=:depName
but I expected
Code:
SELECT department_name, emp_id FROM professor WHERE department_name=:depName
Can anybody tell me, how to do the JPQL-query without the unnecessary JOIN in the generated SQL?
Login
FAQ
Search