7-29-jpa4

1.课程介绍
1. 配置查询对象的模型;（掌握）
2. JPQL;（掌握）
3. 原生SQL查询;（掌握）
4. 分页查询;（掌握）
5. 乐观锁;（掌握）

一，JPQL;（掌握）

select o
from 类名 o
where o.属性名
order by
group by
having
jpql原则属性的规则
(1) jpql 不能出现*号，必须出现对象的别名和属性名
（2）jpql的关键字和sql一样，不分区大小写
（3）类名何属性名区别大小写
基本查询

 @Test
    public void test1() throws Exception{

        String jpql = "select o from Employee o";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }
    //查询所有员工的姓名和所属部门名称【查询特定属性】
    @Test
    public void test2() throws Exception{

        String jpql = "select o.name,o.department.name from Employee o";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Object[]> list = query.getResultList();
        for (Object[] o : list) {
            System.out.println(Arrays.toString(o));
        }
    }
    @Test
    public void test3() throws Exception{

        String jpql = "select new Employee(e.name,e.department.name) from Employee e";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o.getName()+"----"+o.getDepartment().getName());
        }
    }
   // 查询出所有在成都和广州工作的员工【查询结果过滤】
   @Test
   public void test4() throws Exception{

       String jpql = "select e from Employee e where e.department.city like ? or e.department.city like ?";
       EntityManager entityManager = JpaUtils.getEntityManager();
       Query query = entityManager.createQuery(jpql);
       query.setParameter(1,"%成都%").setParameter(2,"%广州%");
       List<Employee> list = query.getResultList();
       for (Employee o : list) {
           System.out.println(o);
       }
   }
   //查询出所有员工信息，按照月薪排序【查询排序】order by
    @Test
   public void test5() throws Exception{
       String jpql = "select e from Employee e  order by e.salary desc";
       EntityManager entityManager = JpaUtils.getEntityManager();
       Query query = entityManager.createQuery(jpql);
       List<Employee> list = query.getResultList();
       for (Employee o : list) {
           System.out.println(o);
       }
   }
    //查询出所有员工信息，按照部门编号排序【使用关联对象属性排序】
    @Test
    public void test6() throws Exception{
        String jpql = "select e from Employee e  order by e.department.sn asc ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }
    //查询出在恩宁路和八宝街上班的员工信息【使用IN】
    @Test
    public void test7() throws Exception{
        String jpql = "select e from Employee e  where e.department.street in(?0,?1)";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        query.setParameter(0,"%恩宁路%").setParameter(1,"%八宝街%");
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }
    //查询出工资在5000-6000的员工【使用BETWEEN..AND..】 区间【5000,6000】 左开(5000,6000]右闭
    @Test
    public void test8() 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> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }
    //查询出姓名包含er或者en的员工【使用LIKE】
    @Test
    public void test9() 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%").setParameter(1,"%en%");
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }
    //查询出有员工的部门【distinct】
    @Test
    public void test10() throws Exception{
        String jpql = "select distinct e.department from Employee e  ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Department> list = query.getResultList();
        for (Department o : list) {
            System.out.println(o);
        }
    }
    //查询出有员工的部门【size】
    @Test
    public void test11() throws Exception{
        String jpql = "select d from Department d where d.employee.size>0";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Department> list = query.getResultList();
        for (Department o : list) {
            System.out.println(o);
        }
    }
    //查询出部门信息，按照部门的员工人数排序
    @Test
    public void test12() 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> list = query.getResultList();
        for (Department o : list) {
            System.out.println(o);
        }
    }
    //查询出没有员工参与的项目【对集合使用size】
    @Test
    public void test13() throws Exception{
        String jpql = "select p from Project p where p.employees.size =0  ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Project> list = query.getResultList();
        for (Project o : list) {
            System.out.println(o);
        }
    }
    //查询出所有员工及部门名称
    @Test
    public void test14() throws Exception{
        String jpql = "select e.name,d.name from Employee e left join e.department d    ";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Object[]> list = query.getResultList();
        for (Object[] o : list) {
            System.out.println(Arrays.toString(o));
        }
    }
    //查询出市场部员工信息及电话
    @Test
    public void test15() 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[]> list = query.getResultList();
        for (Object[] o : list) {
            System.out.println(Arrays.toString(o));
        }
    }
    //查询出各个部门员工的平均工资和最高工资
    @Test
    public void test16() 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[]> list = query.getResultList();
        for (Object[] o : list) {
            System.out.println(Arrays.toString(o));
        }
    }
    //查询出各个项目参与人数报表
    @Test
    public void test17() throws Exception{
        String jpql = "select p.id,p.name,p.employee.size from Project p where p.employee.size>0";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Object[]> list = query.getResultList();
        for (Object[] o : list) {
            System.out.println(Arrays.toString(o));
        }
    }

子查询

@Test
    public void test18() throws Exception{
        String jpql = "select o from Employee o where o.salary > (select avg(e.salary) from Employee e )";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }

分页查询

 @Test
    public void test19() throws Exception{
        String jpql = "select o from Employee o";
        EntityManager entityManager = JpaUtils.getEntityManager();
        Query query = entityManager.createQuery(jpql);
        query.setFirstResult(1).setMaxResults(5);//前者是起始页面，后者是查询条数
        List<Employee> list = query.getResultList();
        for (Employee o : list) {
            System.out.println(o);
        }
    }

乐观锁，悲观锁

事物并发
事物
一组操作，要么都成功，要么都失败
特性
原子性（atomic）： 事务这个单元它已经是最小单元，不能在去分割
一致性 （consistent）: 数据一致性，操作之前，是什么样，操作之后就是什么样
隔离性（insulation） : 事务和事务之间，他们是相互隔离的，你的事务，不能操作我的事务，不然出现事务并发
持久性 （Duration）：当前事务commit的时候，就把数据永久存储下来；

@Test
    public void test2() throws Exception{
        try {
            //用户1
            EntityManager entityManager1 = JpaUtils.getEntityManager();
            //用户2
            EntityManager entityManager2 = JpaUtils.getEntityManager();
            entityManager1.getTransaction().begin();
            entityManager2.getTransaction().begin();
            //用户1查询的时候，还有一个娃娃
            Product product1 = entityManager1.find(Product.class, 1L);
            //用户2查询的时候，还有一个娃娃
            Product product2 = entityManager2.find(Product.class, 1L);
            product1.setNum(product1.getNum()-1);
            product2.setNum(product2.getNum()-1);

            entityManager1.getTransaction().commit();
            entityManager2.getTransaction().commit();

            entityManager1.close();
            entityManager2.close();

        }catch (Exception e){
            e.printStackTrace();
            System.out.println("下次快点，已经卖完了");
        }

    }

乐观锁：
版本号跟新