pro JPA2 第九章--条件API 子查询 示例代码
package com.imooc.chapter9;
import com.imooc.domain.*;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.List;
import java.io.PrintWriter;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;
import javax.persistence.Query;
import java.util.Collection;
/**
* @author 向亚林
* 2018/2/25 21:46
*/
@Service
public class Ch9_03_empSearchSubQuery {
//@PersistenceContext(unitName="EmployeeHR")
@PersistenceContext
EntityManager em;
public EntityManager getEntityManager() {
return em;
}
public Employee createEmployee(int id, String name, long salary) {
Employee emp = new Employee(id);
emp.setName(name);
emp.setSalary(salary);
getEntityManager().persist(emp);
return emp;
}
public void removeEmployee(int id) {
Employee emp = findEmployee(id);
if (emp != null) {
getEntityManager().remove(emp);
}
}
public Employee changeEmployeeSalary(int id, long newSalary) {
Employee emp = findEmployee(id);
if (emp != null) {
emp.setSalary(newSalary);
}
return emp;
}
public Employee findEmployee(int id) {
return getEntityManager().find(Employee.class, id);
}
public Collection<Employee> findAllEmployees() {
Query query = getEntityManager().createQuery("SELECT e FROM Employee e");
return (Collection<Employee>) query.getResultList();
}
/**
* 查询参与项目的员工信息 使用In 表达式
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameJPQL1(String projectName) {
Query query = em.createQuery("select e from Employee e where in " +
"(select emp from Project p join p.employees emp where p.name = :projectName)");
query.setParameter("projectName", projectName);
return query.getResultList();
}
/**
* 9.2.8.4 子查询 程序清单9-5
* 使用In 表达式
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameApi1(String projectName) {
//查询语句构建工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Employee> cbQuery = cb.createQuery(Employee.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Employee> employeeRoot = cbQuery.from(Employee.class);
//主查询语句的select(路径表达式)语句,指定查询的结果集
cbQuery.select(employeeRoot);
//存储多个Predicate过滤条件
ArrayList<Predicate> predicates = new ArrayList<>();
/**
* 查询项目参与者的员工信息 使用In 表达式
*/
if (projectName != null) {
//通过条件查询主语句构建子查询语句
Subquery<Employee> subquery = cbQuery.subquery(Employee.class);
//非相关子查询,声明了子查询自己的根
Root<Project> projectRoot = subquery.from(Project.class);
//子查询的连接根用于路径表达式,可级联以最后的目标确定类型
Join<Project, Employee> employeeJoin = projectRoot.join("employees");
subquery.select(employeeJoin)
.where(cb.equal(projectRoot.get("name"),
cb.parameter(String.class, "project")));
predicates.add(cb.in(employeeRoot).value(subquery));
}
//将查询条件从list转换成数组后将由CriteriaBuilder的and或or来构建主查询的where子句表达式
cbQuery.where(cb.and(predicates.toArray(new Predicate[predicates.size()])));
//在表达式完成后设置参数
TypedQuery<Employee> query = em.createQuery(cbQuery);
if (projectName != null) {
query.setParameter("project", projectName);
}
return query.getResultList();
}
/**
* 9.2.8.4 子查询
* 查询参与项目的员工信息
* 当经常有一个以上的方式来实现特定的结果,可以使用exists 表达式,以及把条件表达式转换成子查询的where子句
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameJPQL2(String projectName) {
Query query = em.createQuery("select e from Employee e where exists " +
"(select p from Project p join p.employees emp where emp = e and p.name = :projectName)");
query.setParameter("projectName", projectName);
return query.getResultList();
}
/**
* 9.2.8.4 子查询
* 查询参与项目的员工信息
* 当经常有一个以上的方式来实现特定的结果,可以使用exists 表达式,以及把条件表达式转换成子查询的where子句
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameApi2(String projectName) {
//条件查询构建工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Employee> cbQuery = cb.createQuery(Employee.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Employee> employeeRoot = cbQuery.from(Employee.class);
//主查询语句的select(路径表达式),也是主查询返回路径表达式的结果
cbQuery.select(employeeRoot);
//用于存放多个过滤条件
List<Predicate> predicateList = new ArrayList<>();
if (projectName != null) {
//通过条件查询主语句构建子查询语句
Subquery<Project> subquery = cbQuery.subquery(Project.class);
//非相关子查询,声明了子查询自己的根
Root<Project> projectRoot = subquery.from(Project.class);
//子查询的连接根用于路径表达式,可级联以最后的目标确定类型
Join<Project, Employee> employeeJoin = projectRoot.join("employees");
subquery.select(projectRoot)
//子查询where条件语句,多个条件逗号分隔 (where emp = e and p.name = :projectName)
.where(cb.equal(employeeJoin, employeeRoot),
cb.equal(projectRoot.<String>get("name"), cb.parameter(String.class, "project")));
predicateList.add(cb.exists(subquery));
}
//将查询条件从list转换成Predicate数组后交由CriteriaBuilder的and或or来构建主查询的 while子句表达式
cbQuery.where(cb.and(predicateList.toArray(new Predicate[predicateList.size()])));
//在表达式语句构建完成后设置参数
TypedQuery<Employee> query = em.createQuery(cbQuery);
if (projectName != null) {
query.setParameter("project", projectName);
}
return query.getResultList();
}
/**
* 9.2.8.4 子查询
* 查询参与项目的员工信息 通过Employee根的引用转移到子查询的from子句
* 员工与项目是多对多关系,使用主查询的根Employee.projects.name定位到项目名,从而减少子查询的搜索空间
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameJPQL3(String projectName) {
Query query = em.createQuery("select e from Employee e where exists " +
"(select p from e.projects p where p.name = :projectName)");
query.setParameter("projectName", projectName);
return query.getResultList();
}
/**
* 9.2.8.4 子查询
* 查询参与项目的员工信息 通过Employee根的引用转移到子查询的from子句
* 员工与项目是多对多关系,使用主查询的根Employee.projects.name定位到项目名,从而减少子查询的搜索空间
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameApi3(String projectName){
//条件查询构造工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Employee> cbQuery = cb.createQuery(Employee.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Employee> employeeRoot = cbQuery.from(Employee.class);
//主查询语句的select(路径表达式),也是主查询返回路径表达式的结果
cbQuery.select(employeeRoot);
//存放多个过滤条件
List<Predicate> predicateList = new ArrayList<>();
/**
* 查询参与指定项目的员工信息 通过Employee根的引用转移到子查询的from子句
* 在子查询中使用from()子句中基于父查询的Root对象是不可行的,因为form()方法只接受持久化的类型.
* 解决方案是使用Subquery.correlate(父查询的Root)方法
*/
if (projectName != null) {
//通过条件查询主语句构建子查询语句
Subquery<Project> subquery = cbQuery.subquery(Project.class);
//关联子查询使用Subquery.corrleate
Root<Employee> correlateEmployeeRoot1 = subquery.correlate(employeeRoot);
//子查询的连接根用于路径表达式,可级联以最后的目标确定类型
Join<Employee, Project> projectJoin = correlateEmployeeRoot1.join("projects");
subquery.select(projectJoin)
.where(cb.equal(projectJoin.get("name"),
cb.parameter(String.class,"project")));
//查询条件: 如果子查询结果存在则展示
predicateList.add(cb.exists(subquery));
}
//将查询条件由list转换成数组交由CriteriaBuilder的and或or来构建主查询的where子句表达式
cbQuery.where(cb.and(predicateList.toArray(new Predicate[predicateList.size()])));
//在表达式构建完成后进行参数设置
TypedQuery<Employee> query = em.createQuery(cbQuery);
if (projectName != null) {
query.setParameter("project", projectName);
}
return query.getResultList();
}
/**
* JPQL多态查询,查询设计项目,且参与项目的经理所管理的员工平均工薪>指定值
* 查询失败,不知道如何实现
* @param salary
* @return
*/
public List<Project> findProjectBySalaryJPQL4(Double salary) {
Query query = em.createQuery("select p from Project p join p.employees e " +
"where type (p) = DesignProject and e.directs is not empty and " +
"(select avg(e.salary) from e.directs)>= :salary");
query.setParameter("salary", salary);
return query.getResultList();
}
/**
* 9.2.8.5 IN 表达式
* 查询所在地的国家是"NY","CA" 的员工信息
* JPQL查询语句: select e from Employee e where e.address.state in ('NY', 'CA')
* 为把这个查询转换成条件API查询,需要调用CriteriaBuilder.in(路径,属性名.value("NY").value("CA")),来设置条件
* @return
*/
public List<Employee> findEmployeesByAddressStateApi5() {
//查询构建工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Employee> cbQuery = cb.createQuery(Employee.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Employee> employeeRoot = cbQuery.from(Employee.class);
//主查询语句的select(路径表达式),也是主查询返回路径表达式的结果
cbQuery.select(employeeRoot);
cbQuery.where(cb.in(employeeRoot.get("address").get("state")).value("NY").value("NJ"));
//在表达式构造完成后设置参数和返回查询结果
TypedQuery<Employee> query = em.createQuery(cbQuery);
return query.getResultList();
}
/**
* 9.2.8.6 IN 表达式 使用参数构建in.value(参数值) IN表达式
* 查询所在地的国家是"NY","CA" 的员工信息
* JPQL查询语句: select e from Employee e where e.address.state in ('NY', 'CA')
* 为把这个查询转换成条件API查询,需要调用CriteriaBuilder.in(路径,属性名.value("NY").value("CA")),来设置条件
* @param addressStates 所属国家,参数之间以逗号分隔
* @return
*/
public List<Employee> findEmployeesByAddressStateApi5(String addressStates) {
//查询构建工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Employee> cbQuery = cb.createQuery(Employee.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Employee> employeeRoot = cbQuery.from(Employee.class);
//主查询语句的select(路径表达式),也是主查询返回路径表达式的结果
cbQuery.select(employeeRoot);
//存放多个Predicate过滤条件
List<Predicate> predicateList = new ArrayList<>();
if (addressStates != null) {
CriteriaBuilder.In<Object> in = cb.in(employeeRoot.get("address").get("state").as(String.class));
String[] states = addressStates.split(",");
//把字符串的参数转换成in的value(参数)条件
for(int i = 0; i < states.length; i++) {
in.value(states[i]);
}
predicateList.add(in);
}
//将条件从list转换成数组交给CriteriaBuilder的and或or来来构建主查询的where子语
cbQuery.where(cb.and(predicateList.toArray(new Predicate[predicateList.size()])));
//在表达式构造完成后设置参数返回查询结果
TypedQuery<Employee> query = em.createQuery(cbQuery);
return query.getResultList();
}
/**
* 9.2.8.6 IN 表达式
* 查询指定项目参与人员所在部门的员工信息
* @return
*/
public List<Employee> findEmployeesByProjectNameJPQL6(String projectName){
Query query = em.createQuery("SELECT e FROM Employee e where e.dept in (" +
"select distinct d from Department d join d.employees de join de.projects p " +
"where p.name like :projectName)");
query.setParameter("projectName", projectName);
return query.getResultList();
}
/**
* 9.2.8.6 IN 表达式
* 查询指定项目参与人员所在部门的员工信息
* @param projectName
* @return
*/
public List<Employee> findEmployeesByProjectNameApi5(String projectName) {
//条件查询构建工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Employee> cbQuery = cb.createQuery(Employee.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Employee> emp = cbQuery.from(Employee.class);
//主查询语句的select(路径表达式),也是主查询返回路径表达式的结果
cbQuery.select(emp);
//用于存储多个Predicate过滤条件
List<Predicate> predicateList = new ArrayList<>();
//查询指定项目参与人员所在部门的员工信息
if (projectName != null) {
//通过条件查询主语句构建子查询语句
Subquery<Department> subquery = cbQuery.subquery(Department.class);
//非相关子查询,声明了子查询自己的根
Root<Department> dept = subquery.from(Department.class);
//子查询的连接根用于路径表达式,可级联以最后的目标确定类型
Join<Employee, Project> projectJoin = dept.join("employees").join("projects");
subquery.select(dept)
.distinct(true)//过滤重复的记录
.where(cb.like(projectJoin.<String>get("name"),
cb.parameter(String.class, "project")));
predicateList.add(cb.in(emp.get("dept")).value(subquery));
}
//将条件从list转换成数组交给CriteriaBuilder的and或or来来构建主查询的where子语
cbQuery.where(cb.and(predicateList.toArray(new Predicate[predicateList.size()])));
//参数设置需要在表达式构建完成后再设置,否则编译出错
TypedQuery<Employee> q = em.createQuery(cbQuery);
if (projectName != null) {
q.setParameter("project", projectName);
}
return q.getResultList();
}
/**
* 9.2.8.6 NOT IN 表达式
* 查询电话类型不是办公用和家庭用的电话
* JPQL语句: select p from Phone p where p.type not in ('Office','Home')
* @return
*/
public List<Phone> findPhoneByTypeApi6(String phoneTypes){
//条件查询构建工厂
CriteriaBuilder cb = em.getCriteriaBuilder();
//条件查询主语句且指定了查询结果的类
CriteriaQuery<Phone> cbQuery = cb.createQuery(Phone.class);
//主查询语句的根,用于形成获取实体属性的路径表达式
Root<Phone> phoneRoot = cbQuery.from(Phone.class);
//主查询语句的select(路径表达式),也是主查询返回路径表达式的结果
cbQuery.select(phoneRoot);
//储存多个Predicate过滤条件
List<Predicate> predicateList = new ArrayList<>();
if (phoneTypes != null) {
String[] phoneType = phoneTypes.split(",");
CriteriaBuilder.In<String> in = cb.in(phoneRoot.get("type").as(String.class));
for (String type : phoneType) {
in.value(type);
}
//相当于JPQL子语句:not in ('Office','Home')
predicateList.add(cb.not(in));
}
//将条件从list转换成数组后将由CriteriaBuilder的and或or来构建主查询语句的where子语句
cbQuery.where(predicateList.toArray(new Predicate[predicateList.size()]));
//在表达示构建完成后,设置参数和返回查询结果
TypedQuery<Phone> query = em.createQuery(cbQuery);
return query.getResultList();
}
public List<Employee> findEmployees(String name, String deptName,
String projectName, String city, PrintWriter out) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> c = cb.createQuery(Employee.class);
Root<Employee> emp = c.from(Employee.class);
c.select(emp);
//c.distinct(true);
//Join<Employee,Project> project = emp.join("projects", JoinType.LEFT);
List<Predicate> criteria = new ArrayList<Predicate>();
/**
* 查询指定姓名的员工信息
*/
if (name != null) {
ParameterExpression<String> p =
cb.parameter(String.class, "name");
criteria.add(cb.equal(emp.get("name"), p));
}
/**
* 查询所在部门的员工信息
*/
if (deptName != null) {
ParameterExpression<String> p =
cb.parameter(String.class, "dept");
criteria.add(cb.equal(emp.get("dept").get("name"), p));
}
/**
* 查询项目参与的员工信息
*/
if (projectName != null) {
Subquery<Integer> sq = c.subquery(Integer.class);
Root<Project> project = sq.from(Project.class);
Join<Project,Employee> sqEmp = project.join("employees");
sq.select(sqEmp.<Integer>get("id"))
.where(cb.equal(project.get("name"),
cb.parameter(String.class, "project")));
criteria.add(cb.in(emp.get("id")).value(sq));
}
/**
* 查询所在城市的员工
*/
if (city != null) {
ParameterExpression<String> p =
cb.parameter(String.class, "city");
criteria.add(cb.equal(emp.get("address").get("city"), p));
}
//将条件从list转换成数组后将由CriteriaBuilder的and或or来构建主查询语句的where子语句
c.where(cb.and(criteria.toArray(new Predicate[criteria.size()])));
//在表达式构造完成后设置参数和返回查询结果
TypedQuery<Employee> q = em.createQuery(c);
if (out != null){
out.println(c);
}
if (name != null) { q.setParameter("name", name); }
if (deptName != null) { q.setParameter("dept", deptName); }
if (projectName != null) { q.setParameter("project", projectName); }
if (city != null) { q.setParameter("city", city); }
return q.getResultList();
}
}