pro JPA2 第九章--条件API 子查询 示例代码

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();
    }
}