CODEWITHSUNDEEP
javaspring-bootspring-data-jpaspring-data
Andulos
Andulos

Reputation: 475

Multiple table joins in Spring Data JPA (Spring Boot 2)

I am fairly new to Spring Boot. I am trying to join a bunch of tables and get some data back. I have created the entities and repositories for the three tables I am working with. The query I am working with joins all three tables. My first question is

  1. Since the query joins all three tables, which repository class should I keep this method in?
  2. The query has NOLOCK. Does the JQL support it?
  3. The three entities I have are Employee.java, EmployeePosition.java and EmployeeAction.java.

Below is the SQL Query (native query) that I am working with. Can it be converted to Spring Data JPA query language from the native query?

SELECT
           A.EMPL_ID
         , A.HIRE_DT
FROM
           HR.EMPLOYEE A WITH(NOLOCK)
           INNER JOIN
                      HR.EMPLOYEE_POS B WITH(NOLOCK)
                      ON
                                 A.SEQ_NO = B.SEQ_NO
           INNER JOIN
                      HR.EMPLOYEE_ACN C WITH(NOLOCK)
                      ON
                                 A.SEQ_NO = C.SEQ_NO
WHERE
           A.EMPLOYEE_STATUS in ('ACTIVE')
           AND B.JOB_GRP='IT'
           AND C.ACN IN ('HIRE')
ORDER BY
           A.HIRE_DT ASC

Upvotes: 0

Views: 1798

Answers (1)

Avik Kesari
Avik Kesari

Reputation: 289

I highly recommend to use @Query or @Native Query to solve this type of problem in Spring Data JPA.

Just to shed some light on your questions,

  1. You should create a Spring Data JPA repository of Employee. But the other two are still Entity. Repositories basically represent a collection of aggregate roots, which in turn are some of your entities, that you'll access and which enforce business constraints on others. Eg., You have Employee consisting of EmployeePosition, the Employee be the aggregate root as it controls the lifecycle of the EmployeePosition etc. Hence you'd define a repository for the Employee but not for the EmployeePosition. So it's by far not all entities that you'd create repositories for.

  2. You can use @Transactional(isolation = Isolation.READ_UNCOMMITTED). But its at the transaction level. Using @NativeQuery is safer to this type of stuff as its at database level.

  3. Since you have all the relationships defined at Entity so now you can directly use JPA Query Methods to fetch the data. You can use Derived Query to do so. Something along the lines of

List<Employee> findByEmployeePositionAndEmployeeActionAndStatus(EmployeePosition position, EmployeeAction action, String status)

Upvotes: 0

Related Questions