JPQL(JPA的查询语句)

一JPQL和SQL

• 1.JPQL和SQL很像,查询关键字都是一样的
• 2.唯一的区别是:JPQL是面向对象的

二、JPQL书写规则

JPA的查询语言,类似于sql

• 1.里面不能出现表名,列名,只能出现java的类名,属性名，区分大小写
• 2.出现的sql关键字是一样的意思,关键字不区分大小写
• 3.不能写select * 要写select 别名

三、JPQL的简单查询

package cn.itsource.test;

import cn.itsource.pojo.Department;
import cn.itsource.pojo.Employee;
import cn.itsource.pojo.Project;
import cn.itsource.utils.JpaUtils;
import org.junit.Test;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;

public class JpqlTest {

    /**
     * 2.2.1.查询所有员工【查询实体类型】
     * @throws Exception
     */
    @Test
    public void test01() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象
        Query query = entityManager.createQuery("from Employee");

        query.getResultList().forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 2.2.2.查询所有员工的姓名和所属部门名称【查询特定属性】
     * @throws Exception
     */
    @Test
    public void test02() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象
        //如果使用连表查询就不能使用逗号那种【不能使用隐式内连接】
        //String jpql = "select e.name,d.name from Employee e join e.department d";
        String jpql = "select e.name,e.department.name from Employee e";
        Query query = entityManager.createQuery(jpql);

        //当你查询的内容不是某个实体类中所有字段的时候，结果就不能封装成实体类对象，List的泛型不能写实体类类型，而是Object[]
        //java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to cn.itsource.pojo.Employee
        List<Object[]> list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }

    /**
     * 查询出所有在成都和广州工作的员工【查询结果过滤】
     * @throws Exception
     */
    @Test
    public void test03() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象
        //如果使用连表查询就不能使用逗号那种【不能使用隐式内连接】
        //String jpql = "select e from Employee e where e.department.city = '成都' or e.department.city = '广州'";

        //String jpql = "select e from Employee e where e.department.city in ('成都','广州')";

        //查询条件不应该在这里直接写死，而是今后在项目是通过前端表单提交
        //String jpql = "select e from Employee e where e.department.city in (?,?)";

        //可以在问号后面跟一个整数，指定编号【推荐使用】
        String jpql = "select e from Employee e where e.department.city in (?1,?2)";

        //还支持使用:变量 方式代替 ? 来作为占位符，设置参数的时候就通过变量名称来设置[不常用，知道就行了]
        //String jpql = "select e from Employee e where e.department.city in (:city1,:city2)";


        Query query = entityManager.createQuery(jpql);
        //有问号必须设置参数【编号是从1开始，与JDBC的PreparedStatement不同】
        query = query.setParameter(1, "成都");
        query = query.setParameter(2, "广州");

        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 2.2.4.查询出所有员工信息，按照月薪排序【查询排序】
     * @throws Exception
     */
    @Test
    public void test04() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象[排序与SQL的排序没有任何区别]
        String jpql = "select e from Employee e order by e.salary desc";

        Query query = entityManager.createQuery(jpql);


        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     * 查询出所有员工信息，按照部门编号排序【使用关联对象的属性排序】
     * @throws Exception
     */
    @Test
    public void test05() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象[排序与SQL的排序没有任何区别]
        String jpql = "select e from Employee e order by e.department.sn asc";

        Query query = entityManager.createQuery(jpql);


        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 查询出在恩宁路和八宝街上班的员工信息【使用IN】
     * @throws Exception
     */
    @Test
    public void test06() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象[排序与SQL的排序没有任何区别]
        String jpql = "select e from Employee e where e.department.street in (?1, ?2)";

        Query query = entityManager.createQuery(jpql);

        //设置参数
        query = query.setParameter(1, "恩宁路").setParameter(2, "八宝街");

        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 2.2.7.查询出工资在5000-6000的员工【使用BETWEEN..AND..】
     * @throws Exception
     */
    @Test
    public void test07() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象[排序与SQL的排序没有任何区别]
        String jpql = "select e from Employee e where e.salary between ?1 and ?2";

        Query query = entityManager.createQuery(jpql);

        //设置参数[java.lang.IllegalArgumentException: Parameter value [5000] did not match expected type [java.math.BigDecimal]
        query = query.setParameter(1, new BigDecimal(5000)).setParameter(2, new BigDecimal(6000));

        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     * 查询出姓名包含er或者en的员工【使用LIKE】
     * @throws Exception
     */
    @Test
    public void test08() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象[排序与SQL的排序没有任何区别]
        String jpql = "select e from Employee e where e.name like ?1 or e.name like ?2";

        Query query = entityManager.createQuery(jpql);

        //设置参数[如果是模糊查询，不要加单引号]
        query = query.setParameter(1, "%er%").setParameter(2, "%en%");

        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 查询出有员工的部门【distinct】
     * @throws Exception
     */
    @Test
    public void test09() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //创建Query对象
        String jpql = "select distinct e.department.name from Employee e";

        Query query = entityManager.createQuery(jpql);

        List<Object> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 查询出有员工的部门【size】
     *  1）只有集合变量才能使用size【双向多对多的两方、双向一对多/多对一的一方】
     *  2）size可以用来作为判断条件、可以用来排序、可以用作查询结果
     * @throws Exception
     */
    @Test
    public void test10() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //从员工去找部门，找到的部门就一定是有员工的部门
        //String jpql = "select distinct e.department from Employee e";

        //从部门找员工，判断该部门的员工人数大于0
        String jpql = "select d from Department d where d.employees.size > 0";

        Query query = entityManager.createQuery(jpql);

        List<Department> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     * 查询出部门信息，按照部门的员工人数排序【使用函数排序】
     * @throws Exception
     */
    @Test
    public void test11() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

     
        String jpql = "select d from Department d order by d.employees.size";

        Query query = entityManager.createQuery(jpql);

        List<Department> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     * 查询出没有员工参与的项目【对集合使用size】
     * @throws Exception
     */
    @Test
    public void test12() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //从部门找员工，判断该部门的员工人数大于0
        String jpql = "select p from Project p where p.employees.size = 0";

        Query query = entityManager.createQuery(jpql);

        List<Project> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 查询每个部门名称以及员工人数【对集合使用size】
     * @throws Exception
     */
    @Test
    public void test13() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        String jpql = "select p.name, p.employees.size from Department p";

        Query query = entityManager.createQuery(jpql);

        List<Object[]> list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     * 查询出所有员工姓名及部门名称【JOIN/LEFT JOIN】
     * @throws Exception
     */
    @Test
    public void test14() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //使用了隐式内连接
        //String jpql = "select e.name, e.department.name from Employee e";

        //使用显式内连接
        //String jpql = "select e.name, d.name from Employee e inner join e.department d";

        //使用左外连接
        //String jpql = "select e.name, d.name from Employee e left join e.department d";

        //使用右外连接
        String jpql = "select e.name, d.name from Department d right join d.employees e";


        Query query = entityManager.createQuery(jpql);

        List<Object[]> list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }

    /**
     * 查询出市场部员工信息及电话
     * @throws Exception
     */
    @Test
    public void test15() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL中从某个实体类开始查询，然后通过该别名.属性.属性.属性....
        String jpql = "select p.employee.name, p.number from Phone p where p.employee.department.name = ?1";


        Query query = entityManager.createQuery(jpql);

        List<Object[]> list = query.setParameter(1, "市场部").getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     * 各个  每个
     * 查询出每个部门员工的平均工资和最高工资【使用聚集函数】
     * @throws Exception
     */
    @Test
    public void test16() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL中从某个实体类开始查询，然后通过该别名.属性.属性.属性....
        String jpql = "select e.department.name, max(e.salary), avg(e.salary) from Employee e group by e.department.name";


        Query query = entityManager.createQuery(jpql);

        List<Object[]> list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     * 查询出各个项目参与人数报表
     * @throws Exception
     */
    @Test
    public void test17() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL中从某个实体类开始查询，然后通过该别名.属性.属性.属性....
        //String jpql = "select p.name,p.employees.size from Project p";
        String jpql = "select p.name,count(e.name) from Project p left join p.employees e group by p.name";


        Query query = entityManager.createQuery(jpql);

        List<Object[]> list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     * 子查询：查询出大于平均工资的员工信息
     * @throws Exception
     */
    @Test
    public void test18() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL中从某个实体类开始查询，然后通过该别名.属性.属性.属性....
        String jpql = "select e from Employee e where e.salary > (select avg(ee.salary) from Employee ee)";

        Query query = entityManager.createQuery(jpql);

        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 查员工，每页展示10行数据
     * @throws Exception
     */
    @Test
    public void test19() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL中从某个实体类开始查询，然后通过该别名.属性.属性.属性....
        String jpql = "select e from Employee e";

        Query query = entityManager.createQuery(jpql);

        /**
         * setFirstResult：相当于填写limit后面的第一个问号的值  (pageNo - 1) * pageSize
         * setMaxResults：相当于填写limit后面的第二个问号的值 pageSize
         * 支持所有数据库产品
         * pageNo和pageSize都需要你从前端请求中传递到Controller中，然后再传递到DAO层
         */
        query = query.setFirstResult(10).setMaxResults(10);


        List<Employee> list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 查员工，每页展示10行数据--查总行数
     * @throws Exception
     */
    @Test
    public void test20() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL中从某个实体类开始查询，然后通过该别名.属性.属性.属性....
        String jpql = "select count(e) from Employee e";

        Query query = entityManager.createQuery(jpql);

        //查询count()统计结果一定是单行单列值，需要使用getSingleResult来提取数据  Single表示单例
        //得到的结果就是一个Long类型整数
        Long total = (Long)query.getSingleResult();

        System.out.println("总共有 " + total + " 行数据");

        JpaUtils.close(entityManager);
    }


    /**
     * 执行原生SQL
     *  1）创建createNativeQuery
     *  2）可以指定查询结果的封装类型
     *  3）其他用法都与JPQL一致
     * @throws Exception
     */
    @Test
    public void test21() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        String sql = "select * from employee where name like ?1";

        //创建NativeQuery
        //Query nativeQuery = entityManager.createNativeQuery(sql);
        Query nativeQuery = entityManager.createNativeQuery(sql, Employee.class);

        //不加单引号
        nativeQuery.setParameter(1, "%en%");

        //查询的结果集，默认将每一行封装一个Object[]进行返回
        List<Employee> list = nativeQuery.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

}

pojo:
Department.java

package cn.itsource.pojo;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name="department")
public class Department {


    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String provice;//省
    private String city;//城市
    private String street;

    //多个部门被同一个部门经理管理【单向多对一】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="manager_id")
    private Employee manager;//经理

    private String sn;//序列号

    //当前部门下的员工集合
    @OneToMany(fetch = FetchType.LAZY,mappedBy = "department")//放弃维护关系
    private List<Employee> employees = new ArrayList<>();

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getProvice() {
        return provice;
    }

    public void setProvice(String provice) {
        this.provice = provice;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getStreet() {
        return street;
    }

    public void setStreet(String street) {
        this.street = street;
    }

    public Employee getManager() {
        return manager;
    }

    public void setManager(Employee manager) {
        this.manager = manager;
    }

    public String getSn() {
        return sn;
    }

    public void setSn(String sn) {
        this.sn = sn;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", provice='" + provice + '\'' +
                ", city='" + city + '\'' +
                ", street='" + street + '\'' +
                ", sn='" + sn + '\'' +
                '}';
    }
}

Employee.java

package cn.itsource.pojo;

import javax.persistence.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Entity
@Table(name="employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;

    //多个员工属于同一个部门【单向多对一】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="department_id")
    private Department department;

    private BigDecimal salary;//薪水
    private Date hireDate;//雇佣日期

    //一个项目有多个开发人员，一个开发人员开发多个项目【单向多对多】
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name="project_employee",
        joinColumns = {@JoinColumn(name="EMPLOYEE_ID")},
        inverseJoinColumns = {@JoinColumn(name="PROJECT_ID")}
    )
    private List<Project> projects = new ArrayList<>();

    public List<Project> getProjects() {
        return projects;
    }

    public void setProjects(List<Project> projects) {
        this.projects = projects;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public BigDecimal getSalary() {
        return salary;
    }

    public void setSalary(BigDecimal salary) {
        this.salary = salary;
    }

    public Date getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", hireDate=" + hireDate +
                ", department_id=" + department.getSn() +
                '}';
    }
}

Phone.java

package cn.itsource.pojo;

import javax.persistence.*;

@Entity
@Table(name="phone")
public class Phone {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String types;//类型
    private String number;

    //多个电话属于同一个员工【单向多对一】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="employee_id")
    private Employee employee;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTypes() {
        return types;
    }

    public void setTypes(String types) {
        this.types = types;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Employee getEmployee() {
        return employee;
    }

    public void setEmployee(Employee employee) {
        this.employee = employee;
    }
}

Project.java

package cn.itsource.pojo;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name="project")
public class Project {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;

    //多个项目被同一个项目经理管理【单向多对一】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="manager_id")
    private Employee manager;

    @ManyToMany(fetch = FetchType.LAZY,mappedBy = "projects")
    private List<Employee> employees = new ArrayList<>();

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Employee getManager() {
        return manager;
    }

    public void setManager(Employee manager) {
        this.manager = manager;
    }

    @Override
    public String toString() {
        return "Project{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}