java - JPA2 - 使用 Criteria API 作为动态查询递归获取连接实体的子实体

Question

我想了解如何使用 Criteria API 而不是 JPQL 应用某些条件，特别是如果 Criteria 可用于通过连接层次结构以与 JPQL 相同的方式从连接中递归获取子实体。

更新

Tiny 和 Chris 的评论促使我首先明确我想要实现的目标:

我的示例有 4 个实体，如下图所示。 Enitem 与 Ensources 具有多对一关系。 Entopics 与 Enitems 具有 OneToMany 关系。 Entopics 与 SegmentsNew 具有 OneToMany 关系。

我正在构建一个搜索页面，用户可以通过在搜索条件中输入尽可能多或尽可能少的内容来查找所选项目。在下面的示例中，搜索“公司法”应返回公司法部分中的所有项目(即使未输入任何其他内容)。

我的实体:

项目:

@Entity
@Table(name = "enitem")

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "itemid")
private Integer itemid;
@Size(max = 500)
@Column(name = "itemname")
private String itemname;
@Column(name = "daterec")
@Temporal(TemporalType.DATE)
private Date daterec;
@Lob
@Size(max = 65535)
@Column(name = "itemdetails")
private String itemdetails;
private String enteredby;
@OneToMany(mappedBy = "items")
private Collection<Endatamaster> endatamasterCollection;
@JoinColumn(name = "topicid", referencedColumnName = "topicid")
@ManyToOne
private Entopic topics;
@JoinColumn(name = "sourceid", referencedColumnName = "sourceid")
@ManyToOne
private Ensource source;

内元:

public class Entopic implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "topicid")
private Integer topicid;
@Size(max = 500)
@Column(name = "topicname")
private String topicname;
@Size(max = 500)
@Column(name = "description")
private String description;
@Column(name = "locksec")
private boolean locksec;
@JoinColumn(name = "segmentid", referencedColumnName = "SEGMENTID")
@ManyToOne
private Segmentnew segments;
@JoinColumn(name = "marketid", referencedColumnName = "marketid")
@ManyToOne
private Enmarkets markets;
@OneToMany(mappedBy = "topics")
private Collection<Enitem> enitemCollection;

资源:

@Entity
@Table(name = "ensource")
@NamedQueries({
@NamedQuery(name = "Ensource.findAll", query = "SELECT e FROM Ensource e")})
public class Ensource implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "sourceid")
private Integer sourceid;
@Size(max = 500)
@Column(name = "sourcename")
private String sourcename;
@Size(max = 500)
@Column(name = "description")
private String description;

@JoinColumn(name = "typeid", referencedColumnName = "typeid")
@ManyToOne
private Enitemtype entype;

新分割:

public class Segmentnew implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Column(name = "SEGMENTID")
private Integer segmentid;
@Size(max = 255)
@Column(name = "SEGMENTNAME")
private String segmentname;
@OneToMany(mappedBy = "segments")
private Collection<Entopic> entopicCollection;
@JoinColumn(name = "sectorId", referencedColumnName = "SECTORID")
@ManyToOne
private Sectorsnew sectors;

所需的 JPQL 字符串表示形式为:

Select e FROM Enitems e WHERE e.topics.topicid = :topicid 
AND e.source.sourceid = :sourceid; AND e.topics.segments.segmentid = :segmentid  

搜索页面使用 JSf Primefaces:

<p:panelGrid columns="2">

                    <p:outputLabel value="Sectors: "/>
                    <p:selectOneMenu value="#{sectorBean.sectorid}"
                                     filter="true">
                        <f:selectItem itemValue="0" itemLabel="NULL"/>
                        <f:selectItems value="#{sectorBean.secList}"
                                       var="sect"
                                       itemLabel="#{sect.sectorname}"
                                       itemValue="#{sect.sectorid}"/>
                        <f:ajax listener="#{segmentsBean.segFromSec}" render="segs"/>
                    </p:selectOneMenu>

                    <p:outputLabel value="Segments: "/>
                    <p:panel id="segs">

                        <p:selectOneMenu value="#{queryBean.segmentid}"
                                         rendered="#{not empty segmentsBean.menuNormList}">

                            <f:selectItems value="#{segmentsBean.menuNormList}"
                                           var="segs"
                                           itemLabel="#{segs.segmentname}"
                                           itemValue="#{segs.segmentid}"/>
                        </p:selectOneMenu>

                    </p:panel>

                    <p:outputLabel value="Topics: "/>
                    <p:selectOneMenu value="#{queryBean.topicid}" filter="true">
                        <f:selectItem itemValue="" itemLabel="NULL"/>
                        <f:selectItems value="#{clienTopicBean.publicTopMenu}"
                                       var="pubs"
                                       itemLabel="#{pubs.topicname}"
                                       itemValue="#{pubs.topicid}"/>


                    </p:selectOneMenu>

                    <p:outputLabel value="Type: "/>
                    <p:selectOneMenu value="#{typeBean.typeid}" filter="true">
                        <f:selectItem itemValue="" itemLabel="NULL"/>
                        <f:selectItems value="#{typeBean.menuList}"
                                       var="type"
                                       itemLabel="#{type.typename}"
                                       itemValue="#{type.typeid}"/>

                        <f:ajax listener="#{sourceBean.sourceFromType}" render="src"/>
                    </p:selectOneMenu>

                    <p:outputLabel value="Sources: "/>
                    <p:panel id="src">

                        <p:selectOneMenu value="#{queryBean.sourceid}"
                                         rendered="#{not empty sourceBean.sourceListNorm}">

                            <f:selectItems value="#{sourceBean.sourceListNorm}"
                                           var="srcs"
                                           itemLabel="#{srcs.sourcename}"
                                           itemValue="#{srcs.sourceid}"/>
                        </p:selectOneMenu>

                    </p:panel>
                </p:panelGrid>

这就是我正在尝试使用 CAPI 的方法:

public List<Enitem> superQ(Integer topicid, Integer sourceid,
        Integer segmentid) {
    em.clear();

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery cq = cb.createQuery(Enitem.class);
    Root<Enitem> rt = cq.from(Enitem.class);

    cq.select(rt);
    cq.distinct(true);

    List<Predicate> criteria = new ArrayList<Predicate>();
    Predicate whereClause = cb.conjunction();
    // if () {
    Path<Entopic> topJoin = rt.get("topics");


    if (topicid != 0) {

        ParameterExpression<Integer> p
                = cb.parameter(Integer.class, "topicid");
        whereClause = cb.and(whereClause, cb.equal(topJoin.get("topicid"), p));
    }
    if (segmentid != 0) {

        ParameterExpression<Integer> p
                = cb.parameter(Integer.class, "segmentid");
        whereClause = cb.and(whereClause, cb.equal(topJoin.get("segments").get("segmentid"), p));
    }
    //}

    if (sourceid != 0) {
        ParameterExpression<Integer> p
                = cb.parameter(Integer.class, "sourceid");
        whereClause = cb.and(whereClause, cb.equal(rt.get("source").get("sourceid"), p));
    }

//    if(whereClause.getExpressions().isEmpty()) {
//        throw new RuntimeException("no criteria");
//    }
    cq.where(whereClause);

    TypedQuery q = em.createQuery(cq);
    if (topicid != 0) {
        q.setParameter("topicid", topicid);
    }
    if (segmentid != 0) {
        q.setParameter("segmentid", segmentid);
    }

    if (sourceid != 0) {
        q.setParameter("sourceid", sourceid);
    }

    return q.getResultList();

}

声明 if(entityName != 0) 而不是 if(entityName != null) 很重要，我之前就是这样做的，这导致应用程序要求用户填充所有参数。这可能是因为 null 的整数值实际上是数字零？

生成的SQL:

SELECT DISTINCT t1.itemid, t1.daterec, t1.ENTEREDBY, t1.itemdetails, t1.itemname, 
t1.sourceid, t1.topicid FROM enitem t1 LEFT OUTER JOIN entopic t0 ON (t0.topicid 
= t1.topicid) LEFT OUTER JOIN ensource t2 ON (t2.sourceid = t1.sourceid) WHERE 
(((t0.topicid = ?) AND (t2.sourceid = ?)) AND (t0.segmentid = ?))

应用程序的行为是动态的，用户只需在搜索页面中输入任何单个值，就会返回与该值相对应的列表。 我现在遇到的问题是，如果我执行第二个查询，即使我已经在方法开始时清除了 EntityManager，也会返回相同的结果。因此，该应用程序仅在重新启动后才能工作。我需要引用实体吗？

Answer 1

使用 From 和 join 都可以为您提供可以转换为根接口(interface)的对象，从而允许链接连接。
尝试这样的事情:

public List<Enitem> superQ(Integer topicid, Integer sourceid,
      Integer segmentid) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery cq = cb.createQuery(Enitem.class);
    Root<Enitem> rt = cq.from(Enitem.class);

    cq.select(rt);
    cq.distinct(true);

    List<Predicate> criteria = new ArrayList<Predicate>();
    Predicate whereClause = cb.conjunction();
    if ((topicid != null)||(segmentid != null)){
      Path<Entopic> topJoin =rt.get("topics");
      if(topicid != null) {
          ParameterExpression<Integer> p =
                  cb.parameter(Integer.class, "topicid");
          whereClause = cb.and(whereClause, cb.equal(topJoin.get("topicid"), p));
      }
      if(segmentid != null) {
        ParameterExpression<Integer> p =
                cb.parameter(Integer.class, "segmentid");
        whereClause = cb.and(whereClause, cb.equal(topJoin.get("segments").get("segmentid"), p));
      }
    }

    if(sourceid != null) {
      ParameterExpression<Integer> p =
               cb.parameter(Integer.class, "sourceid");
      whereClause = cb.and(whereClause, cb.equal(rt.get("source").get("sourceid"), p));
    }


    if(whereClause.getExpressions().isEmpty()) {
        throw new RuntimeException("no criteria");
    }
    cq.where(whereClause);
}

除非指定了需要连接的参数，否则这将生成没有任何连接的内容，并且将像提供的 JPQL 一样使用内部连接。