时间戳

首页 > 解决方案 > 如何拦截和修改JPA生成的SQL语句？

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

问题描述

我想知道是否有一种方法可以优化生成 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
}

在数据库方面，我有三个表：

我想向数据库查询每位作者的书籍数量

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

标签： jpaspring-data-jpajpql

解决方案

您可以使用以下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从您的查询中删除 可以解决这个问题。

推荐阅读