CODEWITHSUNDEEP
jpaspring-data-jpajpql
Witek
Witek

Reputation: 55

How to intercept and modify JPA generated SQL statement?

I wonder if there a way to optimize the process of generating JPA Query (JPQL), instead of using native one.

for simplicity my model looks like below:

@Entity
@Table(name = "b_books")
public class Book {
    
    @Id
    @Column(name = "book_id", updatable = false, nullable = false, unique = true)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="book_generator")   
    @SequenceGenerator(name="book_generator", sequenceName = "books_seq", allocationSize=1)
    private Integer id;
    
    @Column(name = "book_titl_tx")
    private String title;
    
    private int yearOfPublication;
    
    @ManyToMany(fetch = FetchType.LAZY, cascade = {CascadeType.MERGE, CascadeType.PERSIST})
    @JoinTable(name = "b_book_authors",
               joinColumns = @JoinColumn(name = "book_id"), 
               inverseJoinColumns = @JoinColumn(name = "auth_id"))
    private List<Author> authors = new ArrayList<Author>();

    //getter, setters, etc
}


@Entity
@Table(name = "b_authors")
public class Author {

    @Id
    @Column(name = "auth_id", nullable = false, unique = true)
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="authors_generator")
    @SequenceGenerator(name="authors_generator", sequenceName = "authors_seq", allocationSize=1)
    private Integer id;

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

    @ManyToMany(mappedBy = "authors")
    private List<Book> books = new ArrayList<Book>();

    //getters, setters, etc
}

On database side I have three tables:

I'd like to query the DB for number of books per each author

public interface AuthorRepository extends JpaRepository<Author, Integer>{

@Query(value = "SELECT a.lastName AS lastName, " + 
               "       COUNT(a) AS booksCount" +
               "  FROM Author AS a " +
               "  LEFT JOIN a.books AS b" +
               " GROUP BY a.lastName")
List<IAuthorInfo> getAuthorInfo();
}

I'm using the interface-based projection

public interface IAuthorInfo {

    String getLastName();
    Integer getBooksCount();
}

And now to the main question: with this model JPA generated query has one redundant JOIN, how to get rid of it?

ACTUAL:

SELECT
    author0_.auth_last_name_tx   AS col_2_0_,
    COUNT(author0_.auth_id) AS col_3_0_
FROM
    admin.b_authors        author0_
    LEFT OUTER JOIN admin.b_book_authors   books1_ ON author0_.auth_id = books1_.auth_id
    LEFT OUTER JOIN admin.b_books          book2_ ON books1_.book_id = book2_.book_id
GROUP BY
    author0_.auth_last_name_tx

EXPECTED

SELECT
    author0_.auth_last_name_tx   AS col_2_0_,
    COUNT(author0_.auth_id) AS col_3_0_
FROM
    admin.b_authors        author0_
    LEFT OUTER JOIN admin.b_book_authors   books1_ ON author0_.auth_id = books1_.auth_id
GROUP BY
    author0_.auth_last_name_tx

Upvotes: 3

Views: 406

Answers (2)

Witek
Witek

Reputation: 55

Thanks, it removed this LEFT JOIN a.books AS b indeed.

Now it generates the SQL:

SELECT author0_.auth_last_name_tx AS col_2_0_,
       (SELECT COUNT(books1_.auth_id)
          FROM b_book_authors books1_
         WHERE author0_.auth_id = books1_.auth_id) AS col_3_0_
  FROM b_authors author0_
 GROUP BY author0_.auth_last_name_tx;

which suprisingly works on H2 (mem) database for unit tests but for Oracle requires adding Author.ID to JPQL

SELECT author0_.auth_id,
       author0_.auth_last_name_tx AS col_2_0_,
       (SELECT COUNT(books1_.auth_id)
          FROM b_book_authors books1_
         WHERE author0_.auth_id = books1_.auth_id) AS col_3_0_
  FROM b_authors author0_
 GROUP BY author0_.auth_id,
       author0_.auth_last_name_tx;

Upvotes: 0

Guillaume
Guillaume

Reputation: 14656

You can use the size() function:

SELECT a.lastName AS lastName, size(a.books) AS booksCount FROM Author AS a GROUP BY a.lastName

I've not tested but I would imagine that removing the LEFT JOIN a.books AS b from your query would solve this issue.

Upvotes: 1

Related Questions