Hibernate：Criteria：复杂查询：fetch / join / update&delete / subquery / native SQL / 存储过程

当前系列： JDBC&Hibernate

加载：fetch

在Criteria中仍然存在自动/指定加载关联对象的场景。

log演示：criteria时Bed和Teacher的默认/自定义的eager和lazy 加载

但我们也可以在root中指定JoinType：

root.fetch(Teacher_.students);
//除非特殊情况，一般不用自己指定JoinType，让Hibernate决定即可
root.fetch(Teacher_.students, JoinType.LEFT);

JOIN

因为可以建立双向的关联关系，配合fetch，可以由Hibernate自动完成很大一部分的JOIN工作。比如：查出学生atai（注意：只知道名字不知道id）的所有成绩

可以在拿到atai对象后：

criteria.where(builder.equal(root.get(Student_.name), "atai"));
Student atai = query.getSingleResult();

瞬间完成：

atai.getScores();

这背后

要么是eager fetch，在SELECT Student的时候就JOIN了Score；
要么是lazy load fetch，在SELECT Student查到atai之后，在SELECT Score

但如果没有双向关联呢，且我们想一次性的得到所有数据呢？那就使用JOIN：

仍然先确定一个root（比如Score）

Root<Score> root = criteria.from(Score.class);

用root去关联（join）另一张表/实体Student。指定关联点就是Score_.candidate，注意返回值Join<X,Y>
```
Join<Score, Student> score2student = root.join(Score_.candidate);
```
设置where条件，要使用Student_.name，只能从Join<X,Y> score2student中引出
```
criteria.where(builder.equal(score2student.get(Student_.name), "atai"));
```

join()方法中一样可以传递JoinType，默认是inner join

Join<Score, Student> score2student = 
		root.join(Score_.candidate, JoinType.LEFT);

多对多JOIN

比如Student和Teacher之间直接@ManyToMany关联（没有关系对象），而我们要一次性取出所有老师数量大于3的学生，怎么办？

你可能想着这样写：

criteria.where(builder.greaterThan(
		builder.count(root.get(Student_.teachers)), 3L));

但很遗憾，Hibernate还没法解析。报异常：

jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax;

因为生成的SQL是这样的：

and count(.)>3

我们还是不得不JOIN：

SetJoin<Student,Teacher> join = root.join(Student_.teachers);
criteria.groupBy(root);
criteria.having(builder.greaterThan(
		builder.count(root.get(Student_.id)), 3L));

join的是一个“集合”，你可能会感觉到有点奇怪，但没办法，就这样的，你可以将其理解成“一种指示”，Hibernate自然会去解析……

再JOIN一个类

比如统计每个老师每个科目教出来的学生平均成绩，就需要把Teacher也弄进来：

SetJoin<Student, Teacher> student2teacher = score2student.join(Student_.teachers);

注意这里join()的参数要求只能是Student的attribute。所以必须要有一个Student到Teacher的引用，这样就会逼我们加上不想用的双向引用。但这里还是有一个平衡的办法：只假如private的引用字段，不包含其getter和/或setter……

JOIN之后返回的值当然不再是任何一个entity，所以我们要使用Tuple和multiselect：

Path<String> pTeacherName = student2teacher.get(Teacher_.name);
Path<String> pScoreName = root.get(Score_.name);
final String alsAvgPoint = "avgPoint";
criteria.groupBy(pTeacherName,pScoreName);		
criteria.multiselect(pTeacherName,pScoreName,				
		builder.avg(root.get(Score_.point)).alias(alsAvgPoint));

scores.forEach(t -> System.out.println(
		t.get(pTeacherName) + " :" + t.get(pScoreName) + " " + t.get(alsAvgPoint)));

subquery

比如：找出年龄大于等于18岁的同学的成绩

说明：subquery能实现的功能，很多时候JOIN也能够实现，此处仅为展示语法所需

首先就要构建利用Student构建一个子查询：

Subquery<Student> subquery = criteria.subquery(Student.class);
Root<Student> subroot = subquery.from(Student.class);
subquery.where(builder.ge(subroot.get(Student_.age), 18));
//子查询中select不能省略！
subquery.select(subroot);

可以和非子查询对比：一样的流程方法等，非常类似。除了select()，非子查询可以省略，子查询不行。

select()后面还可以传各种Path()，形成投影。比如：

//非子查询
CriteriaQuery<Integer> criteria = builder.createQuery(Integer.class);
criteria.select(root.get(Score_.id));

//子查询
Subquery<String> subquery = criteria.subquery(String.class);
subquery.select(subroot.get(Student_.name));

然后，利用子查询做条件在Score中查询：

criteria.where(root.get(Score_.candidate).in(subquery));

log演示SQL语句：……

Update和Delete

除了查询，criteria还可以update和delete哟！但不要忘记udpate和Delete都要在transaction中（按JPA要求）……

Delete

和之前学习的CriteriaQuery略有不同的是将Query改成了Delete：

CriteriaDelete<Bed> criteriaDelete = builder.createCriteriaDelete(Bed.class);

以及最后的调用方法executeUpdate()：

em.createQuery(criteriaDelete).executeUpdate();

其他的都是一样的，比如设置删除条件：

Root<Bed> root = criteriaDelete.from(Bed.class);
criteriaDelete.where(builder.ge(builder.length(root.get(Bed_.name)),5));

Update

只需将之前的Delete改成Update，并用set()方法指定如何update：

Path<String> path = root.get(Bed_.name);
criteriaUpdate.set(path, builder.concat("17bang-", path));

上述criteria，能完成企业应用80-95%的功能。

如果还有不能用criteria实现的，还可以使用：

原生SQL

准备一个简单的SQL语句

String sql = "SELECT * FROM Bed";

直接EnityManager对象调用createNativeQuery()方法传SQL语句，并调用getResultList()，返回一个Object[]的List：

List<Object[]> students = em.createNativeQuery(sql).getResultList();

注意：不要和createQuery()混淆，如果调用createQuery()方法，要求传递的是和SQL类似的Java Persistence Query Language (JPQL)

createNativeQuery()方法还可以重载，指定查询结果生成的entity：

List<Bed> students = em.createQuery(sql, Bed.class).getResultList();

参数传递

有时候为了复用，我们还希望能够给SQL设置参数……

参数用冒号（:）引导：

String sql = "SELECT * FROM Bed WHERE id > :id";

调用setParameter()方法：

List<Bed> list = em.createNativeQuery(sql, Bed.class)
	.setParameter("id", 3)  //设定参数
	.getResultList();

存储过程

建议：总是作为最后选项

首先建立一个很简单的存储过程：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_count_scores`( 
   IN studentId integer,  
   OUT scoreCount integer  
)
BEGIN
	SELECT COUNT(id) INTO scoreCount  
	FROM score   
	WHERE candidate_id = studentId;
END$$
DELIMITER ;

Hibernate中一样可以调用存储过程：

createStoredProcedureQuery()方法创建一个StoredProcedureQuery：

StoredProcedureQuery query = em.createStoredProcedureQuery("sp_count_scores");

无论输入输出参数，一样的注册registerStoredProcedureParameter()，只是用枚举ParameterMode区分：

输入参数用setParameter()传值

query.registerStoredProcedureParameter(0, Integer.class, ParameterMode.IN);
query.setParameter(0, 5);

输出参数用getOutputParameterValue()取值

final String pScoreCount = "scoreCount";
query.registerStoredProcedureParameter(pScoreCount, Integer.class, ParameterMode.OUT);
System.out.println(query.getOutputParameterValue(pScoreCount));

注意