antonio antonio - 15 days ago 5
Java Question

JPA Query and Relations

I'm developing an HRM application so I'm confused to how manage entities over JPA.

My case resuming in is a set of tables in a multilingual context:
-employees
-departments
-languages
-departments_languages

enter image description here

following my database tables:

Employee Entity class:

@Entity
@Table(name="employees")
@NamedQuery(name="Employee.findAll", query="SELECT e FROM Employee e")
public class Employee implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="employeed_id", unique=true, nullable=false)
private int employeedId;

@Column(nullable=false, length=255)
private String address;

@Temporal(TemporalType.DATE)
@Column(nullable=false)
private Date birthday;

...

//bi-directional many-to-one association to Department
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="department_id", nullable=false)
private Department department
}


Department Entity class

@Entity
@Table(name="departments")
@NamedQuery(name="Department.findAll", query="SELECT d FROM Department d")
public class Department implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="department_id", unique=true, nullable=false)
private int departmentId;

@Temporal(TemporalType.TIMESTAMP)
@Column(name="date_added", nullable=false)
private Date dateAdded;

@Column(name="location_id", nullable=false)
private int locationId;

@Column(nullable=false)
private byte status;

//bi-directional many-to-one association to DepartmentsLanguage
@OneToMany(mappedBy="department")
private Set<DepartmentsLanguage> departmentsLanguages;

//bi-directional many-to-one association to Employee
@OneToMany(mappedBy="department",fetch=FetchType.LAZY)
private Set<Employee> employees;

...
}


DepartmentLanguage Entity

@Entity
@Table(name="departments_languages")
@NamedQuery(name="DepartmentsLanguage.findAll", query="SELECT d FROM DepartmentsLanguage d")
public class DepartmentsLanguage implements Serializable {
private static final long serialVersionUID = 1L;

@EmbeddedId
private DepartmentsLanguagePK id;

@Column(length=255)
private String description;

@Column(length=255)
private String name;

//bi-directional many-to-one association to Department
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="department_id", nullable=false, insertable=false, updatable=false)
private Department department;

//bi-directional many-to-one association to Language
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="language_id", nullable=false, insertable=false, updatable=false)
private Language language;

...
}


Firtly, I ask you if is a correct way and how can I perform JPA queries as:

SELECT * FROM employees
INNER JOIN departments_languages using(department_id)
WHERE employeed_id = 1 and language_id = 1


From this query I need Employee informations, with department name (assuming that languageId is 1 )

Performing a query from eclipse JPA console return an Employee object with all relationship, but both all correlated results

Can you show me a sample query for this kind of design please

Answer

Solved realizing query using fetch join, for example:

Query query = entityManager.createQuery("select e from Employee as e "
    + "JOIN FETCH e.department d "
    + "JOIN FETCH d.departmentsLanguages dl "
    + "where e.employeedId = :employeeId "
    + "and dl.language = :languageId");

Also you can use EntiyGraph feature to both improve performance in a great JPA style ;)

Comments