代码之家  ›  专栏  ›  技术社区  ›  Czarek

Hibernate,使用distinct,在count中包含多个列

  •  2
  • Czarek  · 技术社区  · 8 年前

    如何将下面显示的SQL查询重写为HQL?我想用 javax.persistence.Query

    SELECT COUNT(DISTINCT(book_id, author_id)) FROM dbo.book bo LEFT JOIN dbo.author au ON au.id = bo.author_id LEFT JOIN dbo.publisher pu ON pu.id = bo.publisher_id WHERE pu.id = 777

    我的尝试无效:

    SELECT COUNT(DISTINCT(bl.id, o.id)) FROM PRDAuthor as o LEFT JOIN FETCH o.bookList as bl WHERE bl.publisher.id = 777

    编辑:我正在附加我的实体

    普道托

    public class PRDAuthor {
    
        // ...
        @OneToMany(orphanRemoval = true, mappedBy = "author", cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
        @BatchSize(size = 100)
        private List<PRDBook> bookList = new ArrayList<>();
        // ...
    }
    

    PRDBook

    public class PRDBook {
    
        // ...
        @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
        @BatchSize(size = 30)
        private PRDAuthor author;
    
        @ManyToOne(cascade = CascadeType.MERGE, fetch = FetchType.LAZY)
        @BatchSize(size = 30)
        private PRDPublisher publisher;
    
        // ...
    }
    

    PRD发布者

    public class PRDPublisher {
    
        // ...
        @OneToMany(orphanRemoval = true, mappedBy = "publisher", cascade = {CascadeType.ALL}, fetch = FetchType.LAZY)
        @BatchSize(size = 100)
        private List<PRDBook> bookList = new ArrayList<>();
        // ...
    }
    
    1 回复  |  直到 8 年前
        1
  •  1
  •   Javasick Kumar Vivek Mitra    8 年前

    只能按一个字段计数 试试这个(未测试):

    SELECT COUNT(DISTINCT(*)) FROM PRDAuthor as o 
    LEFT JOIN FETCH o.bookList as bl
    WHERE bl.publisher.id = 777