Spring JPA Join two tables without third class

Question

Is there a way to join two tables in Spring JPA without using association class.

I have two MySQL DB tables : employees(id,.....,department_id) departments(id,.....) And I'm searching for a way to join these tables using only my employee and department classes.

Currently, I managed to join two tables but with the third association class. My current implementation is:

Employee class:

@Entity(name = "Employee")
@Table(name = "employees")
@JsonInclude(Include.NON_NULL)
public class Employee implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @JsonIgnore
    @Column(name = "employee_id")
    private Long employeeId;

    @Column(name = "first_name")
    private String firstName;

    @Column(name = "last_name")
    private String lastName;

    @Column(name = "phone_number")
    private String phoneNumber;

    @Column(name = "hire_date")
    private Double hireDate;

    @Column(name = "job_id")
    private Long jobId;

    @Column(name = "salary")
    private Double salary;

    @Column(name = "commission_pct")
    private Double commissionPct;

    @Column(name = "employees")
    private Long employees;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "id", insertable = false, updatable = false)
    @Fetch(FetchMode.JOIN)
    private Department department;

}

Department class:

@Entity(name = "Department")
@Table(name = "departments")
@JsonInclude(Include.NON_NULL)
public class Department implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)

    @Column(name = "department_name")
    private String departmentName;

    @Column(name = "department_id")
    private long departmentId;

    @Column(name = "manager_id")
    private Double managerId;
    @Column(name = "location_id")
    private Double locationId;


}

Association class:

public class DeptEmpDto {
    private long departmentId;
    private String departmentName;
    private Double managerId;
    private Double locationId;
    private long employeeId;
    private String firstName;
    private String lastName;
    private String phoneNumber;
    private Double hireDate;
    private Long jobId;
    private Double salary;
    private Double commissionPct;

}

Repository:

public interface IEmployeeRepository extends JpaRepository<Employee, Long> {

    @Query("SELECT new com.concretepage.entity.DeptEmpDto(d.departmentId,d.departmentName,d.managerId,d.locationId,e.employeeId,e.firstName,e.lastName,e.phoneNumber,e.hireDate,e.jobId,e.salary,e.commissionPct FROM Employee e INNER JOIN Department d ON d.id = e.jobId")
    List<DeptEmpDto> fetchEmpDeptDataInnerJoin();

Possible duplicate of [Hibernate Query to join two table using Jparepository](https://stackoverflow.com/questions/43636720/hibernate-query-to-join-two-table-using-jparepository) — Alexpandiyan Chokkan, Jun 24 '19 at 08:12
What do you mean by 'association class'? You have already joined the tables via `@ManyToOne` — Alan Hay, Jun 24 '19 at 09:17

score 0 · Answer 1 · answered Jun 24 '19 at 08:40

0

You can use it in Employee class

@ManyToMany(fetch = FetchType.EAGER)
@JoinTable(
        name = "DeptEmp",
        joinColumns = @JoinColumn(name = "emp_id",referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "dep_id",referencedColumnName = "id")
)
private Set<Departments> departments = new HashSet<>();

Look at this about JPA

answered Jun 24 '19 at 08:40

Pawel Nackiewicz

219
3
10

i`m searching a way to do it in repository class and only to use employee class . For example to use something like this : @Query("SELECT new com.concretepage.entity.Employee(employeeId,firstName,lastName,salary) FROM Employee") but to add department fields too – Mariqn Marinov Jun 24 '19 at 11:25
When you use my option you will add a new filed to your class Employee. After that your ORM will update your class in database. After that you can get department by select Employee – Pawel Nackiewicz Jun 24 '19 at 11:58

score 0 · Answer 2 · answered Jun 24 '19 at 09:47

0

Logically an employee can't work in two departements so your relationship is correct

But you can do that with a @ManyToMany annotation.

answered Jun 24 '19 at 09:47

AyoubMK

526
1
4
19

Spring JPA Join two tables without third class

2 Answers2