[FIXED] Spring data JPA left join on two unrelated tables

Issue

I have following two entities where there is no relationship b/w them.I would like to perform left join get data from both the tables.As spring repository provides return result on only one table how can i modify my code to get data from both the tables.

@Table(value = "customer")
data class Customer(
    val id: Int,
    val name: String,
    val groupNumber: String,
)

@Table(value = "customer_policy")
data class Customer_Policy(
    val audt_id: Int,
    val policyNUmber: String,
    val groupNumber: String,
)

Here is my query

Select c.name,cp.policuNumber from Customer c left join customer_policy on c.groupNumber = cp.groupNumber

How Can i define an interface repository which returns the above result.

Solution

In Spring Data JPA you have the annotation @Query where you can add your query straight in the interface methods.

Your repository interface would look something like this:

import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.stereotype.Repository

@Repository interface CustomerRepo: JpaRepository<CustomResponseEntity, Long> {

  @Query("Select c.name,cp.policuNumber from Customer c left join customer_policy cp on c.groupNumber = cp.groupNumber")
  fun findSomething():
    List<CustomResponseEntity>
}

Here you have a deeper explanation: Query annotation and examples

Answered By - Paplusc
Answer Checked By - Clifford M. (JavaFixing Volunteer)