jpql语句

1.配置对象的查询

实体类： Employee  Department Phone Project 

实体类之间的关系： 员工和部门  双向多对一；员工和电话 一对多； 部门和经理 多对一； 员工和项目 多对多；项目和经理 多对一；

2.jpal语句

学JPQL记住两个点

2.1.JPQL和SQL很像,查询关键字都是一样的

 2.唯一的区别是:JPQL是面向对象的

2.2. JPQL书写规则

JPA的查询语言,类似于sql

 1.里面不能出现表名,列名,只能出现java的类名,属性名，区分大小写

 2.出现的sql关键字是一样的意思,不区分大小写

 3.不能写select * 要写select 别名

2.3代码练习

package cn.itsource.jpa;

import cn.itsource.jpa.domain.Department;
import cn.itsource.jpa.domain.Employee;
import cn.itsource.jpa.domain.Project;
import cn.itsource.jpa.util.JpaUtil;
import org.junit.Test;

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

public class JpqlTest {
    @Test
    public void test1()throws Exception{
    //2.2.1.1.1.查询所有员工【查询实体类型】
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql="select e from Employee e";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
        entityManager.close();
    }
    //2.2.2.查询所有员工的姓名和所属部门名称【查询特定属性】
    @Test
    public void test2()throws Exception{
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql="select e.name,e.department.name from Employee e";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> resultList = query.getResultList();
        for (Object[] employee : resultList) {
            System.out.println(Arrays.toString(employee));
        }
        entityManager.close();
    }
    //代码(SQL中使用对象封装参数)
    @Test
    public void test3()throws Exception{
        EntityManager entityManager = JpaUtil.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 employee : resultList) {
            System.out.println(employee.getName()+"...."+employee.getDepartment().getName());
        }
        entityManager.close();
    }
   // 2.2.3.查询出所有在成都和广州工作的员工【查询结果过滤】
   @Test
   public void test4()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e from Employee e where e.department.city=? or e.department.city=?";
       Query query = entityManager.createQuery(jpql);
       query.setParameter(1, "成都").setParameter(2, "广州");
       List<Employee> resultList = query.getResultList();
       for (Employee employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.2.4.查询出所有员工信息，按照月薪排序【查询排序】
   @Test
   public void test5()throws Exception{
       EntityManager entityManager = JpaUtil.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 employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.2.5.查询出所有员工信息，按照部门编号排序【使用关联对象属性排序】
   @Test
   public void test6()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e from Employee e order by e.department.id desc ";
       Query query = entityManager.createQuery(jpql);
       List<Employee> resultList = query.getResultList();
       for (Employee employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.2.6.查询出在恩宁路和八宝街上班的员工信息【使用IN】
   @Test
   public void test7()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e from Employee e where e.department.street in(?1,?2)";
       Query query = entityManager.createQuery(jpql);
       query.setParameter(1, "恩宁路").setParameter(2, "八宝街");
       List<Employee> resultList = query.getResultList();
       for (Employee employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.2.7.查询出工资在5000-6000的员工【使用BETWEEN..AND..】
   @Test
    public void test8()throws Exception{
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql="select e from Employee e where  e.salary between :min and :max";
        Query query = entityManager.createQuery(jpql);
        query.setParameter("min", new BigDecimal("5000")).setParameter("max", new BigDecimal("6000"));
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
        entityManager.close();
    }
   //2.2.8.查询出姓名包含er或者en的员工【使用LIKE】
   @Test
   public void test9()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e from Employee e where e.name like ? or e.name like ?";
       Query query = entityManager.createQuery(jpql);
       query.setParameter(1, "%er%").setParameter(2,"%en%");
       List<Employee> resultList = query.getResultList();
       for (Employee employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.3.1.查询出有员工的部门【distinct】
   @Test
   public void test10()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select distinct e.department from Employee e ";
       Query query = entityManager.createQuery(jpql);
       List<Department> resultList = query.getResultList();
       for (Department employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.4.1.查询出有员工的部门【size】//必须配置双向一对多:部门和员工
   @Test
   public void test11()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e from Department e where e.employees.size>0";
       Query query = entityManager.createQuery(jpql);
       List<Department> resultList = query.getResultList();
       for (Department employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.4.2.查询出部门信息，按照部门的员工人数排序【使用函数排序】
   @Test
   public void test12()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e from Project e where e.employees.size=0";
       Query query = entityManager.createQuery(jpql);
       List<Project> resultList = query.getResultList();
       for (Project employee : resultList) {
           System.out.println(employee);
       }
       entityManager.close();
   }
   //2.5.1.查询出所有员工及部门名称【JOIN/LEFT JOIN】
   @Test
   public void test13()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e,d.name from Employee e left join e.department d";
       Query query = entityManager.createQuery(jpql);
       List<Object[]> resultList = query.getResultList();
       for (Object[] employee : resultList) {
           System.out.println(Arrays.toString(employee));
       }
       entityManager.close();
   }
   //2.5.2.查询出市场部员工信息及电话
   @Test
   public void test14()throws Exception{
       EntityManager entityManager = JpaUtil.getEntityManager();
       String jpql="select e,o from Phone o  join o.employee e where e.department.name = ?";
       Query query = entityManager.createQuery(jpql);
       query.setParameter(1, "市场部");
       List<Object[]> resultList = query.getResultList();
       for (Object[] employee : resultList) {
           System.out.println(Arrays.toString(employee));
       }
       entityManager.close();
   }
  // 2.6.1.查询出各个部门员工的平均工资和最高工资【使用聚集函数】
    @Test
    public void test15() throws Exception {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select avg(o.salary),max(o.salary) from Employee o group by o.department.name";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> list = query.getResultList();
        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
        }
       // System.out.println("size:" + list.size());
        entityManager.close();
    }
    //2.6.2.查询出各个项目参与人数报表
    @Test
    public void test16() throws Exception {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o.name,o.employees.size from Project o where o.employees.size>0";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> list = query.getResultList();
        for (Object[] objects : list) {
            System.out.println(Arrays.toString(objects));
        }
        // System.out.println("size:" + list.size());
        entityManager.close();
    }
    //查出大于平均工资的人数
    @Test
    public void test17() throws Exception {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o where o.salary>(select avg(salary) from Employee)";
        Query query = entityManager.createQuery(jpql);
        List<Employee> list = query.getResultList();
        for (Employee objects : list) {
            System.out.println(objects);
        }
        // System.out.println("size:" + list.size());
        entityManager.close();
    }
    //3.1.获取分页数据
    @Test
    public void test18() throws Exception {
        int currentPage = 2;
        int pageSize = 6;
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o";
        Query query = entityManager.createQuery(jpql);
        // 从那里开始取数据,索引从0开始
        int firstResult = (currentPage - 1) * pageSize;
        // 取多少条
        int maxResults = pageSize;
        query.setFirstResult(firstResult).setMaxResults(maxResults);
        List<Employee> list = query.getResultList();
        for (Employee employee : list) {
            System.out.println(employee);
        }
        System.out.println("size:" + list.size());
        entityManager.close();
    }
    //3.3.获取记录总数，返回类型是Long
    @Test
    public void test19() throws Exception {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select count(o) from Employee o";
        Query query = entityManager.createQuery(jpql);
        Long result = (Long) query.getSingleResult();
        System.out.println("size:" + result);
        entityManager.close();
    }
    @Test
    public void test20() throws Exception {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String sql = "select * from employee";
        // 告诉hibernate把employee表转换为Employee对象
        Query query = entityManager.createNativeQuery(sql, Employee.class);
        List<Employee> list = query.getResultList();
        query.setFirstResult(3).setMaxResults(3);
        for (Employee employee : list) {
            System.out.println(employee);
        }
        System.out.println("size:" + list.size());
        entityManager.close();
    }
    //"%en%名字
    @Test
    public void test22() throws Exception {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String sql = "select * from employee where name like ? order by salary desc";
        Query query = entityManager.createNativeQuery(sql, Employee.class);
        query.setParameter(1, "%en%");
        List<Employee> list = query.getResultList();
        for (Employee employee : list) {
            System.out.println(employee);
        }
        System.out.println("size:" + list.size());
        entityManager.close();
    }


}