jpa条件查询：使用数组运算符编写查询

vltsax25 于 2021-07-12 发布在 Java

关注(0)|答案(2)|浏览(307)

我有以下实体

Book {
  private String title;

  @ManyToMany
  @JoinTable(
     name = "book_should_read_user",
     joinColumns = @JoinColumn(name = "book_id"),
     inverseJoinColumns = @JoinColumn(name = "user_id")
  )
  private Set<User> shouldRead; // the users who should read the book

  @OneToMany(mappedBy = "book")
  private Set<BookReadAction> actions; // all the read actions performed on the book
}

BookReadAction {
  @ManyToOne
  private Book book;

  @ManyToOne
  private User user;
}

现在我想查询should read集合中所有用户已经阅读的所有书籍。postgres中的以下sql查询实现了以下功能：

select * 
from book 
where id in (
   select distinct id 
   from (
      select book.id id, 
             array_agg(book_should_read_user.user_id) suggested_readers, 
             array_agg(distinct book_read_action.user_id) read_by 
     from book b
         inner join book_should_read_user on book.id = book_should_read_user.book_id
         inner join book_read_action on book.id = book_read_action.book_id
     group by book.id) subquery
   where suggested_readers <@ read_by)

但是，我想以编程方式添加这个子句，所以我宁愿使用jpa criteriaapi。尽管我做了一些尝试，我还是挣扎着。是否可以在jpa criteria api中从此查询构建 predicate ？

Java sql Hibernate jpa criteria-api

来源：https://stackoverflow.com/questions/64872612/jpa-criteria-query-writing-query-with-array-operators

2条答案

按热度按时间

z5btuh9x1#

您不能准确地将您编写的查询编写为hql，而是等效的查询：

select * 
from book b
where exists (
     select 1
     from book b
         inner join book_should_read_user on book.id = book_should_read_user.book_id
         inner join book_read_action on book.id = book_read_action.book_id
     where b.id = book.id
     group by book.id
     having array_agg(book_should_read_user.user_id) <@ array_agg(distinct book_read_action.user_id)
)

要使用hql或jpa标准api编写此查询，您需要提供 <@ 运算符或通过 SQLFunction 你可以用你的hibernate方言注册。像这样：

public class ArrayContainsFunction implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        SessionFactoryImplementor sfi = (SessionFactoryImplementor) mapping;
        return sfi.getTypeHelper().basic(Integer.class);
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "array_agg(" + args.get(0) + ") <@ array_agg(" + args.get(1) + ") and 1=";
    }
}

在注册它时，您应该能够在hql中这样使用它 ... HAVING array_contains(shouldRead.id, readAction.id)

赞(0）回复(0）举报 2021-07-12

6rqinv9w2#

使用criteriaapi，不能实现子查询as from子句。
https://www.objectdb.com/java/jpa/query/jpql/from#from_and_join_in_criteria_queries
你必须重新构造你的查询

赞(0）回复(0）举报 2021-07-12