CODEWITHSUNDEEP
javaspringjpaspring-data-jpa
levant pied
levant pied

Reputation: 4501

Spring Data JPA conditional entity join

I have unusual table setup that I want mapped in JPA. Here's a simplified example:

book
  company_id int
  book_id    int
  series_id  int

borrow
  company_id int
  book_id    int
  type       char

The problem is borrow.book_id is overloaded:

The relation is logically many borrow to one book. The immediate use case is: get all borrow rows given a list of books. How should this be mapped in Spring Data JPA?

Upvotes: 1

Views: 2556

Answers (1)

dexter_
dexter_

Reputation: 381

You can try something like below in your Borrow entity mapping for Book - 

@ManyToOne
@JoinColumnsOrFormulas(
    { @JoinColumnOrFormula(
        formula = @JoinFormula(
         value = "case " +
                 "when type == 'B' then book_id" +
                 "when type == 'S' then series_id " +
                 "else 1" +
                 "end", 
         referencedColumnName="book_id")) }
)

But you need to use the @ManyToOne annotation, even though this seems to be a @OneToOne association. The join formula won't work on OneToOne. The downside with this approach would be, hibernate will unnecessarily create 2 joins which could be done using only 1 using a native query

If you are using spring data JPA then in your repository you could use method like -

@Query(value="sql stmt with conditional join and IN clause", nativeQuery = true)
List<Idto> findAllBorrowByBook(List<int> bookIds);

where "Idto" is an interface to map your resultset.

Upvotes: 5

Related Questions