java - 使用查询生成器查询从另一个表链接的表中的所有行

Question

我有一个表，其中有几行变量。此行之一链接到另一个具有更多行的表。

说白了，1个表叫做Connection。变量是:

name, groupname, etc.

组名应链接到名为 ConnectionGroup 的第二个表。变量是:

name, id.

我的想法是按名称查询 ConnectionGroup 表。 Connection和ConnectionGroup的抽象类是这样的:

public abstract class Connection_ {

    public static volatile SingularAttribute<Connection, String>name;
    public static volatile SingularAttribute<Connection, String>host;
    public static volatile SetAttribute<Connection, ConnectionGroup>connectionGroups;
}

public abstract class ConnectionGroup_ {

    public static volatile SingularAttribute<ConnectionGroup, String> name;
    public static volatile SingularAttribute<ConnectionGroup, Long> id;

}

要查询这个，我假设我必须连接这两个表，然后只查询它们。这是我尝试过的代码:

@PersistenceContext
private EntityManager em;

public List<Connection> retrieveAll( String groupFilter, int start, int length) {
  ServiceUtil.requireAdmin();
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
  Root<Connection> c = q.from(Connection.class);
  Join<Connection, ConnectionGroup> join = c.join(Connection_.connectionGroups);
  q.select(c);

  c.fetch(Connection_.connectionGroups).fetch(ConnectionGroup_.id);
  Predicate groupPredicate = cb.equal(
  c.get(Connection_.connectionGroups), "%" + groupFilter + "%");
  q.where(groupPredicate);

  List<Connection> results = em.createQuery(q).setFirstResult(start)
            .setMaxResults(length).getResultList();
  for (Connection conn : results) {
      logger.info( "getconnectionGroups =["+ conn.getConnectionGroups() + "]");
      for (ConnectionGroup conngroup : conn.getConnectionGroups()) {
            logger.info("connectiongroups = [" + conngroup.getName() + "]");
      }
  }
}

我尝试过的事情，改变了这一点:

Predicate groupPredicate = cb.equal(
  c.get(Connection_.connectionGroups), "%" + groupFilter + "%");

对此:

Predicate groupPredicate = cb.equal(join.get(ConnectionGroup_.name),
            "%" + groupFilter + "%");

改变这个:

Join<Connection, ConnectionGroup> join = c
            .join(Connection_.connectionGroups);

对此:

Join<Connection, ConnectionGroup> join = c.join("connectionGroups");

当我尝试这些方法时，我不断收到异常无法加入基本类型的属性

我也尝试将代码更改为:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
Root<Connection> c = q.from(Connection.class);
Join<Connection, ConnectionGroup> join = (Join<Connection, ConnectionGroup>) c.fetch(Connection_.connectionGroups);
q.select(c);

Predicate groupPredicate = cb.equal(join.get(ConnectionGroup_.name), "%" + groupFilter + "%");
q.where(groupPredicate);
List<Connection> results = em.createQuery(q).setFirstResult(start)
            .setMaxResults(length).getResultList();

返回异常:

查询指定的联接提取，但提取的关联的所有者不在选择列表中

我使用这些网站作为编写代码的引用:

JPA CriteriaBuilder using fetch joins and result objects

JPA 2 Criteria Fetch Path Navigation

wiki.eclipse.org

developer.com

如何从 ConnectionGroup 表中查询名称？我的做法有错吗？

Answer 1

如果您尝试通过字符串匹配按名称进行过滤，则 CriteriaBuilder.like() 比 CriteriaBuilder.equal() 更适合使用。

要实现您想要查询的内容，您可以使用 ff. JP QL查询:

SELECT DISTINCT conn FROM Connection conn JOIN conn.connectionGroups connGrp
WHERE connGrp.name LIKE :groupFilter

将 JP QL 转换为 CriteriaQuery，您将获得:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Connection> q = cb.createQuery(Connection.class);
Root<Connection> conn = q.from(Connection.class);
Join<Connection, ConnectionGroup> connGrp = conn.join(Connection_.connectionGroups);
q.select(conn).distinct(true);
ParameterExpression<String> param = cb.parameter(String.class, "%"+ groupFilter + "%");
q.where(cb.like(connGrp.get(ConnectionGroup_name), param));