JPQL(JPA的查询语句)

1.最基本的JPQL的格式

1.1 只能写java的类名和属性名
SELECT o[o.property,o.property*] FROM Entity o
[WHERE conditions]
[GROUP BY conditions]
[HAVING conditions]
[ORDER BY o.property[ASC|DESC]]
JPQL本质是JPA通过antlr-2.7.7.jar翻译成sql并且封装执行的。

.1.2学JPQL记住两个点
1.JPQL和SQL很像,查询关键字都是一样的
2.唯一的区别是:JPQL是面向对象的
1.3.JPQL书写规则
JPA的查询语言,类似于sql
1.里面不能出现表名,列名,只能出现java的类名,属性名，区分大小写
2.出现的sql关键字是一样的意思,不区分大小写
3.不能写select * 要写select 别名

下面是整体的查询思路,以及各种查询练习
getset tosting省略

Department 类

	 @Id
    @GeneratedValue
    private Long id;
    private String name;
    private String provice;
    private String city;
    private String street;
    private String sn;
    // 多对一,多个部门由一个部门经理管理
    @ManyToOne(fetch = FetchType.LAZY)
    private Department manager;
    // 双向一对多:当前部门有那些员工
    @OneToMany(mappedBy = "department")
    private Set<Employee> employees = new HashSet<>();
  

Employee类

@Id
    @GeneratedValue
    private Long id;
    private String name;
    private BigDecimal salary;
    private Date hireDate;
    @ManyToOne(fetch = FetchType.LAZY)
    private Department department;

Phone类

@Id
    @GeneratedValue
    private Long id;
    private String types;
    private String number;
    // 多对一:多个电话属于同一个员工
    @ManyToOne(fetch = FetchType.LAZY)
    private Employee employee;

Project类

@Id
    @GeneratedValue
    private Long id;
    private String name;
    // 多对一,多个项目由同一个项目经理管理
    @ManyToOne
    private Employee manager;
    @ManyToMany(fetch = FetchType.LAZY,cascade = CascadeType.REMOVE)
    @JoinTable(name = "project_employee",joinColumns = {@JoinColumn(name = "project_id")},inverseJoinColumns = {@JoinColumn(name = "employee_id")})
    private Set<Employee> employees = new HashSet<>();

//查询全部员工
 @Test
    public void testName() throws Exception{
        EntityManager entityManager = JpaUtils.getEntityManager();
        String jpql = "select e from Employee e";
        Query query = entityManager.createQuery(jpql);
        List<Employee> list = query.getResultList();
        for (Employee employee : list) {
            System.out.println(employee);
        }
    }

 */
    //2.2.2.查询所有员工的姓名和所属部门名称【查询特定属性】
    @Test
    public void testQuery1() throws Exception{
        EntityManager entityManager = JpaUtils.getEntityManager();
        String jpql = "select e.name,e.department from Employee e";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> resultList = query.getResultList();
        for (Object[] o : resultList) {
            System.out.println(Arrays.toString(o));
        }
    }
    @Test
    public void testQuery1_1() throws Exception{
        EntityManager entityManager = JpaUtils.getEntityManager();
        //这里这样写的话 需要在实体类里面添加一个有参构造,然后在加一个无参构造
        String jpql = "select new Employee(e.name,e.department.name) from Employee e ";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj.getName()+"---"+obj.getDepartment().getName());
        }

    }

 //2.2.3.查询出所有在成都和广州工作的员工【查询结果过滤】
    // insert into table(name,tt) values(?,?)
    @Test
    public void testQuery2() throws Exception{
        EntityManager entityManager = JpaUtils.getEntityManager();

        String jpql = "select e from Employee e where e.department.city like ? or e.department.city like ?";
        Query query = entityManager.createQuery(jpql);
        query.setParameter(1,"成都").setParameter(2, "广州");
        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);
        }

    }

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

        String jpql = "select e from Employee e  order by e.salary desc ";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);
        }
    }

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

        String jpql = "select e from Employee e  order by e.department.id  ";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);
        }
    }

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

        String jpql = "select e from Employee e where e.department.street in (?0,?1) ";
        Query query = entityManager.createQuery(jpql);
        query.setParameter(0, "恩宁路").setParameter(1, "八宝街");

        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);
        }
    }

 //查询出工资在5000-6000的员工【使用BETWEEN..AND..】 【5000,6000】 (5000,6000]
    @Test
    public void testQuery6() throws Exception{

        String jpql = "select e from Employee e  where e.salary between :min and :max ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        query.setParameter("min",new BigDecimal("5000")).setParameter("max", new BigDecimal("6000"));

        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);

        }
    }

//2.2.8.查询出姓名包含er或者en的员工【使用LIKE】
    @Test
    public void testQuery7() throws Exception{

        String jpql = "select e from Employee e  where e.name like ? or e.name like ? ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        //query.setParameter(1,"%er%");
       // query.setParameter(2, "%en%");
        query.setParameter(1, "%en%" ).setParameter(2, "%er%");
        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);

        }
    }

  //2.3.1.查询出有员工的部门【distinct】
    @Test
    public void testQuery8() throws Exception{

        String jpql = "select distinct e.department from  Employee e ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Department> resultList = query.getResultList();
        for (Department obj : resultList) {
            System.out.println(obj);
        }
    }

 //查询出有员工的部门【size】
    @Test
    public void testQuery9() throws Exception{

        String jpql = "select d  from  Department d where d.employees.size > 0 ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Department> resultList = query.getResultList();
        for (Department obj : resultList) {
            System.out.println(obj);
        }
    }

//查询出部门信息，按照部门的员工人数排序
    @Test
    public void testQuery10() throws Exception{

        String jpql = "select d  from  Department d order by d.employees.size desc ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Department> resultList = query.getResultList();
        for (Department obj : resultList) {
            System.out.println(obj);
        }
    }

 //查询出没有员工参与的项目【对集合使用size】
    @Test
    public void testQuery11() throws Exception{

        String jpql = "select d  from  Department d where d.employees.size = 0";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Department> resultList = query.getResultList();
        for (Department obj : resultList) {
            System.out.println(obj);
        }
    }

 //查询出所有员工及部门名称
    @Test
    public void testQuery12() throws Exception{

        String jpql = "select e.name,d.name  from  Employee e join e.department d ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Object[]> resultList = query.getResultList();
        for (Object[] obj : resultList) {
            System.out.println(Arrays.toString(obj));
        }
    }

 //2.5.2.查询出市场部员工信息及电话
    @Test
    public void testQuery13() throws Exception{

        String jpql = "select e,p from  Phone p  join p.employee e where e.department.name like ?";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        query.setParameter(1, "%市场部%");
        List<Object[]> resultList = query.getResultList();
        for (Object[] obj : resultList) {
            System.out.println(Arrays.toString(obj));
        }
    }

//查询出各个部门员工的平均工资和最高工资\
    @Test
    public void testQuery14() throws Exception{

        String jpql = "select e.department.name, avg(e.salary),max(e.salary) from Employee e group by e.department.name ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Object[]> resultList = query.getResultList();
        for (Object[] obj : resultList) {
            System.out.println(Arrays.toString(obj));
        }
    }

 //2.6.2.查询出各个项目参与人数报表
    @Test
    public void testQuery15() throws Exception{

        String jpql = "select  p.id,p.name,p.employees.size from Project p where p.employees.size > 0";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Object[]> resultList = query.getResultList();
        for (Object[] obj : resultList) {
            System.out.println(Arrays.toString(obj));
        }
    }

//2.7.子查询：查询出大于平均工资的员工信息
    @Test
    public void testQuery16() throws Exception{

        String jpql = "select  e from Employee e where e.salary > (select avg(e.salary) from Employee e )";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);

        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);
        }
    }

//分页查询--springdatajpa
    @Test
    public void testQuery17() throws Exception{

        String jpql = "select  e from Employee e ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        query.setFirstResult(1).setMaxResults(10);
        List<Employee> resultList = query.getResultList();
        for (Employee obj : resultList) {
            System.out.println(obj);
        }
    }

 //查询员工总共多少条
    @Test
    public void testQuery18() throws Exception{

        String jpql = "select  count(e ) from Employee e ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List list = query.getResultList();
        System.out.println(list);

    }

    //jpa 还对原生的sql进行支持 --返回值存入对象数组 Object[]
    @Test
    public void testQuery19() throws Exception{

        String sql = "select * from Employee ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query nativeQuery = entityManager.createNativeQuery(sql);

        List<Object[]> list = nativeQuery.getResultList();
        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
        }

    }

//返回对象形式
    @Test
    public void testQuery20() throws Exception{

        String sql = "select * from Employee ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query nativeQuery = entityManager.createNativeQuery(sql,Employee.class);

        List list = nativeQuery.getResultList();
        for (Object o : list) {
            System.out.println(o);
        }
    }

    @Test
    public void testQuery21() throws Exception{
        EntityManager entityManager = JpaUtils.getEntityManager();
        String sql = "select * from employee where name like ?";
        Query query = entityManager.createNativeQuery(sql,Employee.class);
        query.setParameter(1,"%en%");
        query.setFirstResult(0).setMaxResults(2);
        List<Employee> resultList = query.getResultList();
        for (Employee o : resultList) {
            System.out.println(o);
        }

    }