CaveatEmptorUtil.printObjectArray(em.createQuery("SELECT count(s), s.messageVersion FROM Sender as s GROUP BY s.messageVersion ORDER BY s.messageVersion", Object[].class).getResultList());
Hibernate:
select
count(sender0_.id) as col_0_0_,
sender0_.messageVersion_id as col_1_0_,
messagever1_.id as id1_5_,
messagever1_.currentDate as currentD2_5_,
messagever1_."text" as text3_5_,
messagever1_."version" as version4_5_
from
Sender sender0_
inner join
MessageVersion messagever1_
on sender0_.messageVersion_id=messagever1_.id
group by
sender0_.messageVersion_id
//JPQL查询
em.createQuery("SELECT count(m),m.text FROM MessageVersion m GROUP BY m.text having m.text like 'c%' ORDER BY m.text", Object[].class)
.getResultList();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = cb.createQuery(Object[].class);
Root<MessageVersion> rootMV = criteriaQuery.from(MessageVersion.class);
criteriaQuery.multiselect(cb.count(rootMV.<String>get("text")), rootMV.<String>get("text"));
criteriaQuery.groupBy(rootMV.<String>get("text"));
//子句都是criteriaQuery的方法, having也不例外. having的参数也是一个Predicate, 所以可以用条件查询API
criteriaQuery.having(cb.like(rootMV.<String>get("text"), "c%"));
criteriaQuery.orderBy(cb.asc(rootMV.<String>get("text")));
//SQL语句 SELECT sender.* FROM sender INNER JOIN messageversion m on sender.messageversion_id = m.id WHERE m.text = 'cony'
//查询Sender对应的MessageVersion类的text=cony的查询
TypedQuery<Sender> joinSender = em.createQuery("SELECT s FROM Sender as s WHERE s.messageVersion.text = 'cony'", Sender.class);
//生成的SQL语句是:
Hibernate:
select
sender0_.id as id1_6_,
sender0_.messageVersion_id as messageV3_6_,
sender0_.name as name2_6_
from
Sender sender0_ cross
join
MessageVersion messagever1_
where
sender0_.messageVersion_id=messagever1_.id
and messagever1_."text"='cony'
//SQL 查询SELECT * FROM messageversion inner join sender s on messageversion.id = s.messageversion_id ORDER BY messageversion.id
//JPA查询, 内连接无需显式编写连接语句
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<MessageVersion> messageVersionRoot = criteriaQuery.from(MessageVersion.class);
Root<Sender> senderRoot = criteriaQuery.from(Sender.class);
criteriaQuery.multiselect(messageVersionRoot, senderRoot).where(
criteriaBuilder.equal(messageVersionRoot.get("id"),senderRoot.get("messageVersion"))
);
criteriaQuery.orderBy(criteriaBuilder.asc(messageVersionRoot.<Long>get("id")));
现在将连接方式修改一下, 看看结果:
// SQL查询 SELECT * FROM messageversion LEFT JOIN sender s on messageversion.id = s.messageversion_id ORDER BY messageversion.id
//JPQL查询
em.createQuery("SELECT m,s FROM MessageVersion as m LEFT JOIN Sender s on m = s.messageVersion ORDER BY m.id", Object[].class);
CaveatEmptorUtil.printObjectArray(em.createQuery("SELECT m,s FROM MessageVersion as m LEFT JOIN Sender s on m = s.messageVersion ORDER BY m.id", Object[].class).getResultList());
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<MessageVersion> messageVersionRoot = criteriaQuery.from(MessageVersion.class);
Join<MessageVersion, Sender> join = messageVersionRoot.join("senders", JoinType.LEFT);
criteriaQuery.multiselect(messageVersionRoot, join);
CaveatEmptorUtil.printObjectArray(em.createQuery(criteriaQuery).getResultList());
// s.name小于等于8的项目不参加连接
// SQL查询 SELECT * FROM messageversion LEFT JOIN sender s on messageversion.id = s.messageversion_id and length(s.name) > 8 ORDER BY messageversion.id;
//JPA查询, 相比原来的多了红色部分
CaveatEmptorUtil.printObjectArray(em.createQuery("SELECT m,s FROM MessageVersion as m LEFT JOIN Sender s on (m = s.messageVersion) and length(s.name) > 8 ORDER BY m.id", Object[].class).getResultList());
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<MessageVersion> messageVersionRoot = criteriaQuery.from(MessageVersion.class);
Join<MessageVersion, Sender> join = messageVersionRoot.join("senders", JoinType.LEFT);
//Join对象的ON方法, 注意只需要编写上边红色的条件, 而不需要编写m = s.messageVersion对应的语句,因为这是隐含的.
join.on(criteriaBuilder.gt(
criteriaBuilder.length(join.<String>get("name")), 8
)
);
criteriaQuery.multiselect(messageVersionRoot, join);
criteriaQuery.orderBy(criteriaBuilder.asc(messageVersionRoot.<Long>get("id")));
//SQL查询: SELECT * FROM sender, users WHERE sender.name = users.username ORDER BY sender.id
//或者写成: SELECT * FROM sender JOIN users on sender.name = users.username ORDER BY sender.id
//JPQL查询, 一个使用连表, 一个使用从笛卡尔积中使用WHERE, 本质相同
em.createQuery("SELECT s,u FROM Sender AS s JOIN User AS u ON s.name = u.username ORDER BY s.id", Object[].class);
em.createQuery("SELECT s,u FROM Sender AS s, User AS u WHERE s.name = u.username ORDER BY s.id", Object[].class);
//编程方式就无须使用连表, 直接按照WHERE条件来查询即可
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
Root<Sender> senderRoot = criteriaQuery.from(Sender.class);
Root<User> userRoot = criteriaQuery.from(User.class);
//直接进行投影然后使用WHERE即可, 无须使用连表API
criteriaQuery
.multiselect(senderRoot, userRoot)
.where(
criteriaBuilder.equal(
senderRoot.<String>get("name"),
userRoot.<String>get("username")
)
)
.orderBy(criteriaBuilder.asc(senderRoot.<Long>get("id")));
em.createQuery("SELECT m FROM MessageVersion as m WHERE (SELECT count(s) FROM Sender as s WHERE s.messageVersion = m) >1 ORDER BY m.id", MessageVersion.class);
SELECT *
FROM messageversion
WHERE 5 > all (SELECT length(sender.name) FROM sender WHERE sender.messageversion_id = messageversion.id)
ORDER BY messageversion.id;
可以看到子查询是查出一个向量, Sender表的name列的长度, 然后使用 5 > all 限定比较, 表示向量中的所有长度都必须小于5. JPQL的查询也很简单, 将SQL翻译一下即可:
em.createQuery("SELECT m FROM MessageVersion as m WHERE 5 > all (SELECT length(s.name) FROM Sender as s WHERE s.messageVersion = m) ORDER BY m.id", MessageVersion.class);
