查询使用查询生成器从另一个表进行链接的表中的所有行
问题描述:
我有一个只有几行变量的表。此行中的一行链接到另一个具有更多行的表。查询使用查询生成器从另一个表进行链接的表中的所有行
要说清楚,1个表称为连接。变量是:
name, groupname, etc.
groupname应链接到第二个名为ConnectionGroup的表。变量是:
name, id.
我的想法是按名称查询到ConnectionGroup表。对于连接和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");
当我尝试这些方法,我不断收到一个例外Cannot join to attribute of basic type
我也试图改变代码到这个:
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();
它会返回一个例外:
query specified join fetching, but the owner of the fetched association was not present in the select list
我使用这些网站作为参考编写鳕鱼E:
JPA CriteriaBuilder using fetch joins and result objects
JPA 2 Criteria Fetch Path Navigation
我如何可以查询从ConnectionGroup表的名字吗?我的方法错了吗?
答
如果您想通过字符串匹配使用名称过滤,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));
在JPA表之间的关系被称为关系,'@ OneToMany'和它的朋友。为了更快提供更好的帮助,请说明这些关系是如何定义的。 – perissf