JPQL语句及事务并发

基础的jpql语句

最基本的JPQL的格式
只能写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的查询语言,类似于sql
 1.里面不能出现表名,列名,只能出现java的类名,属性名，区分大小写
 2.出现的sql关键字是一样的意思,不区分大小写
 3.不能写select * 要写select 别名

简单查询

查询实体类型

@Test
public void test1() throws Exception {
  EntityManager entityManager = JPAUtils.getEntityManager();
  String jpql = "select o from Employee o";
  Query query = entityManager.createQuery(jpql);
  List<Employee> list = query.getResultList();
  for (Employee employee : list) {
    System.out.println(employee);
  }
  System.out.println("size:" + list.size());
  entityManager.close();
}

查询特定属性

 public void Test2() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o.name,o.department.name from Employee o";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> resultList = query.getResultList();
        for (Object[] objects : resultList) {
            System.out.println(Arrays.toString(objects));
        }
    }

查询结果过滤

    @Test
    public void Test3() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o where o.department.city =? or o.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);
        }
    }

查询排序

    @Test
    public void Test4() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o order by  o.salary desc ";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

使用关联对象属性排序

 @Test
    public void Test5() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o order by  o.department.id desc ";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

使用IN

    @Test
    public void Test6() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o  where o.department.street in (?0,?1)";
        Query query = entityManager.createQuery(jpql);
        query.setParameter(0, "恩宁路").setParameter(1, "八宝街");
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

使用BETWEEN…AND…

       @Test
    public void Test7() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o  where o.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);
        }
    

使用LIKE

    @Test
    public void Test8() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o  where o.name like ?0 or o.name like ?1";
        Query query = entityManager.createQuery(jpql);
        query.setParameter(0, "%er%").setParameter(1, "%en%");
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

distinct 去重

 @Test
    public void Test9() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select  distinct o.department from Employee o ";
        Query query = entityManager.createQuery(jpql);
        List<Department> resultList = query.getResultList();
        for (Department department : resultList) {
            System.out.println(department);
        }
    }

size

    @Test
    public void Test10() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select  o from Department o where o.employees.size> 0 ";
        Query query = entityManager.createQuery(jpql);
        List<Department> resultList = query.getResultList();
        for (Department department : resultList) {
            System.out.println(department);
        }
    }

对集合使用size

    @Test
    public void Test12() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select  o from Project o where o.employees.size= 0 ";
        Query query = entityManager.createQuery(jpql);
        List<Project> resultList = query.getResultList();
        for (Project project : resultList) {
            System.out.println(project);
        }
    }

JOIN/LEFT JOIN 连接

    @Test
    public void Test13() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select  o,e.name from Employee o join o.department e ";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> resultList = query.getResultList();
        for (Object[] objects : resultList) {
            System.out.println(Arrays.toString(objects));
        }
    }

使用聚集函数

   @Test
    public void Test15() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select  max(o.salary),avg(o.salary),o.department.name " +
                     "from Employee o group by o.department.name";
        Query query = entityManager.createQuery(jpql);
        List<Object[]> resultList = query.getResultList();
        for (Object[] objects : resultList) {
            System.out.println(Arrays.toString(objects));
        }
    }

子查询

     @Test
    public void Test17() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o where salary>(select avg(salary) from Employee)";
        Query query = entityManager.createQuery(jpql);
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

分页查询

  @Test
    public void Test18() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String jpql = "select o from Employee o ";
        Query query = entityManager.createQuery(jpql);
        //setFirstResult 设置每页每页开始条数的索引
        //setMaxResults 设置每页多少条数据
        Query query1 = query.setFirstResult(10).setMaxResults(5);
        List<Employee> resultList = query1.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

JPA对原生SQL的支持

返回对象数组

  @Test
    public void Test20() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String sql = "select * from employee  ";
        Query query = entityManager.createNativeQuery(sql);
        List<Object[]> resultList = query.getResultList();
        for (Object[] objects : resultList) {
            System.out.println(Arrays.toString(objects));
        }
    }

返回模型对象

 @Test
    public void Test21() {
        EntityManager entityManager = JpaUtil.getEntityManager();
        String sql = "select * from employee  ";
        Query query = entityManager.createNativeQuery(sql,Employee.class);
        query.setFirstResult(3).setMaxResults(3);
        List<Employee> resultList = query.getResultList();
        for (Employee employee : resultList) {
            System.out.println(employee);
        }
    }

添加查询条件

    @Test
    public void testQuery22()throws Exception{
        EntityManager entityManager = JpaUtil.getEntityManager();
        String sql = "select p.* from employee p where p.name like ?";
        Query query = entityManager.createNativeQuery(sql,Employee.class);
        query.setParameter(1,"%en%");
        List<Employee> result = query.getResultList();
        for (Employee employee : result) {
            System.out.println(employee);
        }
    }

事务4个特性（CAID）

原子性(atomic)，事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行
一致性(consistent)，事务在完成时，必须使所有的数据都保持一致状态。
隔离性(insulation)，由事务并发所作的修改必须与任何其它并发事务所作的修改隔离。
持久性(Duration)，事务完成之后，它对于系统的影响是永久性的。

事务并发

通常为了获得更好的运行性能，各种数据库都允许多个事务同时运行，这就是事务并发

隔离机制（解决事务并发）

当并发的事务访问或修改数据库中相同的数据(同一行同一列)时，通常需要采取必要的隔离机制。