JPA Criteria API任意数量的连接/子查询 [英] JPA Criteria API arbitrary number of joins/subqueries

问题描述

我需要使用以下实体构造一个交叉类型查询（为了清晰起见，减少了）。

I need to construct an intersect-type query with the following entities (reduced down for clarity).

@Entity // and other @ stuff
public class Member {
    @Id
    private Long id;
    private String name;
    ...
}

@Entity
public class Program {
     @Id
     private Long id;
     private Long programName;
     private List<ProgramLevel> levels;
     ...
}

@Entity
public class ProgramLevel {
    @Id
    private Long id
    private String levelName;
}

会员可以属于一个或多个节目，他总是拥有自己的节目等级，由此连接：

A member can belong to one or more programs, and he always has his program level, connected by this:

public class Membership {
    @Id
    private Long id;
    private Long memberId;     // this is the member
    private Long programId;    // in which program is he
    private Long programLevel; // and on what level
    ...
}

示例：I有三门课程，数学，英语，科学。他们每个人都有一些水平，比如，MAth有代数，几何，英语有文学，拼写和语法，Science有实验和理论。

Example: I have three programs, Math, English, Science. Each of them has some levels, like, MAth has algebra, geometry, English has literature, spelling and grammar, Science has experiments and theory.

此外，示例用户Joe会有数学：代数，英语：语法程序和水平。示例用户Max可能会有英语：文学。因此，成员可以有多个程序，但每个程序只有一个级别。

Also, example user Joe would have Math:algebra, English: grammar programs and levels. Example user Max would maybe have English:literature. So, member can have multiple programs, but only one level per program.

现在我需要计算或获取所有与少数程序及其中某些级别相匹配的成员。
示例：我希望所有用户都拥有数学：代数或几何，以及英语：文学或语法和科学：理论。

Now I need to count or fetch all members that match a few programs and some levels in them. Example: I want all users that have Math:algebra or geometry, and English:literature or grammar and Science: theory.

我不是真的进入JPA的东西，所以我卡住了。

I'm not really into JPA stuff, so I'm getting stuck.

在SQL中，我会做一个交叉。如何使用JPA？

In SQL, I'd do an intersect. How would I do it with JPA?

我有这样的事情：

HashMap<Long, List<ProgramLevel>> levels = new HashMap<Long, List<ProgramLevel>>();
// then I fetch the levels map.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = cb.createQuery(Long.class);
Root<Membership> root = query.from(Membership.class);

// this is the part where I'm stuck.

// I figured I could try with multiple inner joins?
for(Map.Entry<Long, List<ProgramLevel>> curentLevel: levels.entrySet()) {
    // cb.equal(root.join ??? what comes here?
    // what what what?
    // root.get("programId") = currentLevel.getKey()
    // AND root.get("programLevelId") IN currentLevel.getValue()

}
...

我如何得到这个？

除了作为多个内连接这样做之外，我不知道它是否可以作为INTERSECT完成（db是PostgreSQL，如果重要的话）？

Besides doing this as multiple inner joins, I don't know if it can be done as INTERSECT (db is PostgreSQL if it matters)?

此外，我不确定该查询中的哪个位置我只是根据这些条件给出了不同的会员资格。

Also, I'm not sure where in that query I'd put the condition to only get me distinct memberships with these conditions.

作为奖励，我将不得不创建一个OR查询。这是我必须匹配所有程序的地方，下一个需要任何程序/级别匹配，以便成员包含在那里。但是一旦我想到这个，我'我希望能够自己计算一个。

As a bonus, I'll have to create an OR query. This one is where I must match ALL programs, the next one needs ANY program/level match for a member to be included there. But once I figure this one, I'll figure that one on my own, I hope.

编辑：可能的（伪）SQL选择看起来像：

a possible (pseudo)SQL select would look like:

SELECT count(membership) FROM membership m
  WHERE (m.program == :program1 and m.programLevel in :programLevels1ArrayOfLevels)
INTERSECT
SELECT count(membership) FROM membership m
  WHERE (m.program == :program2 and m.programLevel in :programLevels2ArrayOfLevels)
INTERSECT...
... /* this would go on for as many (Program, List<ProgramLevel>) pairs I have on input. Which is variable).

或类似的东西：

SELECT count(membership) FROM membership m
JOIN membership m1 ON (m1.program = :program1 AND m1.programLevel IN m1.programLevels1Aray)
JOIN membership m2 ON (m2.program = :program2 AND m2.programLevel IN m1.programLevels2Aray)
/* Continued to as many input pairs I have */

推荐答案

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = cb.createQuery(Tuple.class);
Root<Membership> root = query.from(Membership.class);

Collection<Long> queredPrograms = levels.keySet()
Predicate[] queryPredicates = new Predicate[queredPrograms.size()];
int i = 0;
for(Long programId : queredPrograms) {
    queryPredicates[i++] = cb.and(
        cb.equal(root.get("programId"), programId),
        root.get("programLevel").in(levels.get(programId))
    );
}
criteriaQuery.where(
    cb.and(
        cb.or(queryPredicates),
        root.get("programId").in(queredPrograms)
    )
);

criteriaQuery.groupBy(root.get("programId"));
criteriaQuery.select(cb.tuple(cb.count(cb.distinct(root.get("memberId"))), root.get("programId")));
TypedQuery<Tuple> countSelection = entityManager.createQuery(criteriaQuery);

标准的SQL等效API将是这样的，（将返回每个programId的唯一用户数）

SQL equivalent for criteria API will be like this, (will return unique user count by each programId)

SELECT 
      COUNT(DISTINCT memberId),
      programId
FROM membership
WHERE 
      programId in (:PROGRAMS_ID_LIST)
      AND (
           (programId = :PROGRAM_ID AND programLevel IN (:PROGRAM_LEVEL_LIST))
           ...
           OR (programId = :PROGRAM_ID_N AND programLevel IN (:PROGRAM_N_LEVEL_LIST))
      )
GROUP BY programId

如果你不希望每个程序计数只是从group by中删除programId并选择子句，并在where子句中将OR更改为AND。我希望这会对你有帮助。

If you don't want count by each programId just remove programId from group by and select clauses, and change OR to AND in where clause. I hope this will help you.

这篇关于JPA Criteria API任意数量的连接/子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！