jpa - 如何拦截和修改JPA生成的SQL语句？

Question

我想知道是否有一种方法可以优化生成 JPA 查询(JPQL)的过程，而不是使用原生的。

为简单起见，我的模型如下所示:

@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
}

在数据库端我有三个表:

• B_BOOKS
• B_AUTHORS
• 持有多对多关系的 B_BOOK_AUTHORS

我想在数据库中查询每个作者的图书数量

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();
}

我正在使用基于界面的投影

public interface IAuthorInfo {

    String getLastName();
    Integer getBooksCount();
}

现在回答主要问题:使用此模型 JPA 生成的查询有一个冗余 JOIN，如何摆脱它？

实际:

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

预期

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

Answer 1

您可以使用 size() 函数:

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

我没有测试过，但我想从您的查询中删除 LEFT JOIN a.books AS b 会解决这个问题。