Hibernate JPA 复杂查询

路面烧卖

2024-04-09 帮助1人

Hibernate JPA 复杂查询

1、JPQL 查询与更新

JPQL全称：Java Persistence Query Language

在 EJB2.0 中引入的 EJB 查询语言 (EJB QL)，Java 持久化查询语言 (JPQL) 是一种可移植的查询语言，旨在以面向对象表达式语言的表达式，将 SQL 语法和简单查询语义绑定在一起,使用这种语言编写的查询是可移植的，可以被编译成所有主流数据库服务器上的 SQL。

在使用 JPQL 的时候整体的处理效果与SQL的语法形式是几乎相同的，而后在JPA执行的时候会将 JPQL 编译为传统的关系型数据库支持的SQL语句。其特征与原生 SQL 语句类似，并且完全面向对象，通过类名和属性访问，而不是表名和表的属性。

环境准备

1、在实体类上加上一些Lombok的注解，方便准备测试数据

import lombok.*;
import lombok.experimental.Tolerate;
import javax.persistence.*;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "tb_customer")
public class Customer {

    // 这个构造是用来给 投影查询 准备的
    @Tolerate
    public Customer(String name, int age) {
        this.name = name;
        this.age = age;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Long Id; // 客户的主键

    @Column(name = "customer_name")
    private String name; // 客户名称

    @Column(name="customer_age")
    private int age; // 客户年龄

    @Column(name="customer_sex")
    private boolean sex; // 客户性别

    @Column(name="customer_phone")
    private String phone; // 客户的联系方式

    @Column(name="customer_address")
    private String address; // 客户地址
}

2、准备测试数据

    @Before
    public void initData() {
        EntityManager entityManager = JpaUtils.getEntityManager();
        entityManager.getTransaction().begin();
        Arrays.asList(
                Customer.builder().name("Sam").age(18).sex(true).phone("135000000001").address("广州").build(),
                Customer.builder().name("Mike").age(20).sex(true).phone("135000000002").address("广州").build(),
                Customer.builder().name("Nick").age(25).sex(true).phone("135000000003").address("深圳").build(),
                Customer.builder().name("Hom").age(30).sex(true).phone("135000000004").address("西安").build(),
                Customer.builder().name("Rachel").age(25).sex(false).phone("135000000005").address("北京").build(),
                Customer.builder().name("Kath").age(30).sex(true).phone("135000000006").address("武汉").build(),
                Customer.builder().name("Vivi").age(28).sex(false).phone("135000000007").address("南京").build(),
                Customer.builder().name("Oliver").age(30).sex(true).phone("135000000008").address("深圳").build(),
                Customer.builder().name("Angus").age(10).sex(false).phone("135000000009").address("广州").build(),
                Customer.builder().name("Wendy").age(15).sex(false).phone("135000000000").address("西安").build()
        ).forEach(customer -> entityManager.persist(customer));
        entityManager.getTransaction().commit();
        entityManager.close();
    }

1.1、简单查询

   /**
     * 查询全部
     * jqpl：from Customer
     * sql：select * from c_customer
     */
    @Test
    public void testFindAll() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 2.查询全部：创建Query查询对象，query对象才是执行jqpl的对象
        Query query = entityManager.createQuery("from Customer");

        // 3.发送查询，并封装结果集
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)

1.2、别名查询

    /**
     * 别名查询
     *     jqpl：from Customer c
     *     jqpl: select c from Customer c
     *     sql：select * from c_customer c
     */
    @Test
    public void testFindAll2() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 查询所有客户,采用链式调用
        List<Customer> list1 = entityManager.createQuery("from Customer c").getResultList();
        list1.forEach(System.out::println);

        // 查询所有客户,采用链式调用
        List<Customer> list2 = entityManager.createQuery("select c from Customer c").getResultList();
        list2.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)
Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)

1.3、排序查询

    /**
     * 排序查询
     *     jqpl：from Customer order by Id desc
     *     sql：select * from c_customer order by Id desc
     */
    @Test
    public void testOder() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 采用链式调用，默认情况（升序）
        List<Customer> list1 = entityManager.createQuery("from Customer order by Id").getResultList();
        list1.forEach(System.out::println);

        // 采用链式调用，升序情况
        List<Customer> list2 = entityManager.createQuery("from Customer order by Id asc").getResultList();
        list2.forEach(System.out::println);

        // 采用链式调用，降序情况
        List<Customer> list3 = entityManager.createQuery("from Customer order by Id desc").getResultList();
        list3.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    order by
        customer0_.customer_id
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)
Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    order by
        customer0_.customer_id asc
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)
Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    order by
        customer0_.customer_id desc
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)

1.4、条件查询

    /**
     * 条件查询
     *     jqpl：from Customer where sex = ?1 and name like ?2
     *     sql：SELECT * FROM c_customer where c_sex = ?1 and c_name like ?2
     *     jqpl：from Customer where sex = :aaa and name like :bbb
     *     sql：SELECT * FROM c_customer where c_sex = :aaa and c_name like :bbb
     */
    @Test
    public void testWhere() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 条件查询：按参数位置绑定
        Query query1 = entityManager.createQuery("from Customer where sex = ?1 and name like ?2");
        query1.setParameter(1, true);
        query1.setParameter(2, "S%");
        List<Customer> list1 = query1.getResultList();
        list1.forEach(System.out::println);

        // 条件查询：按参数名称绑定
        Query query2 = entityManager.createQuery("from Customer where sex = :aaa and name like :bbb");
        query2.setParameter("aaa", true);
        query2.setParameter("bbb", "S%");
        List<Customer> list2 = query2.getResultList();
        list2.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        customer0_.customer_sex=? 
        and (
            customer0_.customer_name like ?
        )
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        customer0_.customer_sex=? 
        and (
            customer0_.customer_name like ?
        )
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)

1.5、投影查询

    /**
     * 投影查询
     * 1.单个字段查询
     * jqpl：select c.name from Customer c
     * sql：SELECT * FROM c_customer order by Id desc
     * 2.多个字段查询
     * jpql：select c.name,c.age from Customer c
     * jpql：select new Customer(c.name,c.age) from Customer c
     * sql：SELECT * FROM c_customer order by Id desc
     */
    @Test
    public void testFieldName() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 查询所有客户名称：单个字段查询
        Query query1 = entityManager.createQuery("select c.name from Customer c");
        List<Object> list1 = query1.getResultList();
        list1.forEach(System.out::println);

        // 查询所有客户名称、客户年龄：多个字段查询，封装到数组中
        Query query2 = entityManager.createQuery("select c.name,c.age from Customer c");
        List<Object[]> list2 = query2.getResultList();
        list2.forEach(x-> System.out.println(Arrays.toString(x)));

        // 查询所有客户名称、客户来源：多个字段查询，封装到对象中
        // 请在Customer.java添加以下两个构造方法，否则会执行失败
        // public Customer() 和 public Customer(String name, int age)
        Query query3 = entityManager.createQuery("select new Customer(c.name,c.age) from Customer c");
        List<Customer> list3 = query3.getResultList();
        list3.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_name as col_0_0_ 
    from
        tb_customer customer0_
Sam
Mike
Nick
Hom
Rachel
Kath
Vivi
Oliver
Angus
Wendy
Hibernate: 
    select
        customer0_.customer_name as col_0_0_,
        customer0_.customer_age as col_1_0_ 
    from
        tb_customer customer0_
[Sam, 18]
[Mike, 20]
[Nick, 25]
[Hom, 30]
[Rachel, 25]
[Kath, 30]
[Vivi, 28]
[Oliver, 30]
[Angus, 10]
[Wendy, 15]
Hibernate: 
    select
        customer0_.customer_name as col_0_0_,
        customer0_.customer_age as col_1_0_ 
    from
        tb_customer customer0_
Customer(Id=null, name=Sam, age=18, sex=false, phone=null, address=null)
Customer(Id=null, name=Mike, age=20, sex=false, phone=null, address=null)
Customer(Id=null, name=Nick, age=25, sex=false, phone=null, address=null)
Customer(Id=null, name=Hom, age=30, sex=false, phone=null, address=null)
Customer(Id=null, name=Rachel, age=25, sex=false, phone=null, address=null)
Customer(Id=null, name=Kath, age=30, sex=false, phone=null, address=null)
Customer(Id=null, name=Vivi, age=28, sex=false, phone=null, address=null)
Customer(Id=null, name=Oliver, age=30, sex=false, phone=null, address=null)
Customer(Id=null, name=Angus, age=10, sex=false, phone=null, address=null)
Customer(Id=null, name=Wendy, age=15, sex=false, phone=null, address=null)

1.6、分页查询

    /**
     * 分页查询
     *      jqpl : from Customer
     *      sql：select * from c_customer limit 2,5
     */
    @Test
    public void testLimit() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 分页查询
        Query query = entityManager.createQuery("from Customer");
        // 起始索引、每页查询的条数
        query.setFirstResult(2).setMaxResults(5);
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ limit ?,
        ?
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)

1.7、分组查询

    /**
     * 分组查询
     *      jqpl : select count(*) from Customer
     *      sql：select count(*) from c_customer
     *
     *      jqpl : select sex,count(*) from Customer group by sex
     *      sql：select c_sex,count(*) from c_customer group by c_sex
     */
    @Test
    public void testGroupBy() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 聚合函数：count(),max(),min(),avg(),sum()
        Object object2 = entityManager.createQuery("select count(*) from Customer").getSingleResult();
        System.out.println(object2);

        // 分组统计：
        List<Object[]> list = entityManager.createQuery("select sex,count(*) from Customer group by sex").getResultList();
        list.forEach(x->System.out.println(Arrays.toString(x)));

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        count(*) as col_0_0_ 
    from
        tb_customer customer0_
10
Hibernate: 
    select
        customer0_.customer_sex as col_0_0_,
        count(*) as col_1_0_ 
    from
        tb_customer customer0_ 
    group by
        customer0_.customer_sex
[false, 4]
[true, 6]

1.8、多表查询

    @Test
    public void testXXJoin() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 内连接
        List<Object[]> list1 = entityManager.createQuery("from Customer c inner join c.linkMans").getResultList();
        list1.forEach(x->System.out.println(Arrays.toString(x)));

        // 迫切内连接（hibernate独有，将另一个对象的数据封装到该对象中）
        List<Customer> list2 = entityManager.createQuery("select distinct c from Customer c inner join fetch c.linkMans").getResultList();
        list2.forEach(System.out::println);

        // 左外连接
        List<Object[]> list3 = entityManager.createQuery("from Customer c left outer join c.linkMans").getResultList();
        list3.forEach(x->System.out.println(Arrays.toString(x)));

        // 迫切左外连接（hibernate独有，将另一个对象的数据封装到该对象中）
        List<Customer> list4 = entityManager.createQuery("select distinct c from Customer c left outer join fetch c.linkMans").getResultList();
        list4.forEach(System.out::println);

        // 右外连接
        List<Object[]> list5 = entityManager.createQuery("from Customer c right outer join c.linkMans").getResultList();
        list5.forEach(x->System.out.println(Arrays.toString(x)));

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

1.9、更新删除

    /**
     * 数据更新
     *     jpql：update Customer c set c.age = :age where c.name = :name
     *     sql：sql：update tb_customer set customer_age = 20 where customer_name = 'Sam'
     */
    @Test
    public void testUpdate() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 数据更新
        String jpql = "update Customer c set c.age = :age where c.name = :name";
        int updateSize = entityManager.createQuery(jpql)
                .setParameter("age", 20)
                .setParameter("name", "Sam")
                .executeUpdate();
        System.out.println("更新数据量："   updateSize);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

    /**
     * 数据删除
     *     jpql：delete Customer c where c.name = :name
     *     sql：delete tb_customer where customer_name = 'Sam'
     */
    @Test
    public void testDelete() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 数据删除
        String sql = "delete Customer c where c.name = :name";
        int updateSize = entityManager.createQuery(sql)
                .setParameter("name", "Sam")
                .executeUpdate();
        System.out.println("删除数据量："   updateSize);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

# 更新日志
Hibernate: 
    update
        tb_customer 
    set
        customer_age=? 
    where
        customer_name=?
更新数据量：1
# 删除日志
Hibernate: 
    delete 
    from
        tb_customer 
    where
        customer_name=?
删除数据量：1

如果报错：java.lang.IllegalArgumentException: Update/delete queries cannot be typed

问题原因（错误写法）：Query query = em.createQuery(jpql, Entity.class);

解决办法（正确写法）：Query query = em.createQuery(jpql);

2、SQL 查询与更新

有些时候，JPQL使用不当会导致转化成的SQL并不如理想或者特定场合需要SQL优化，还是得用到原生SQL查询的。EntityManager对象的createNativeQuery方法，可以实现十分复杂的查询，但是需要对查询结果进行实体映射处理，并且不能跨数据库。

获得原生SQL查询对象：Query createNativeQuery(String var1, Class var2);
SQL对比JPQL只需要把查询对象方法由 createQuery()替换成createNativeQuery() 即可（传入值变成原生SQL和类对象）

package query;

import entity.Customer;
import org.junit.Before;
import org.junit.Test;
import util.JpaUtils;
import javax.persistence.*;
import java.util.Arrays;
import java.util.List;

public class JpaSqlTest {

    @Before
    public void initData() {
        EntityManager entityManager = JpaUtils.getEntityManager();
        entityManager.getTransaction().begin();
        Arrays.asList(
                Customer.builder().name("Sam").age(18).sex(true).phone("135000000001").address("广州").build(),
                Customer.builder().name("Mike").age(20).sex(true).phone("135000000002").address("广州").build(),
                Customer.builder().name("Nick").age(25).sex(true).phone("135000000003").address("深圳").build(),
                Customer.builder().name("Hom").age(30).sex(true).phone("135000000004").address("西安").build(),
                Customer.builder().name("Rachel").age(25).sex(false).phone("135000000005").address("北京").build(),
                Customer.builder().name("Kath").age(30).sex(true).phone("135000000006").address("武汉").build(),
                Customer.builder().name("Vivi").age(28).sex(false).phone("135000000007").address("南京").build(),
                Customer.builder().name("Oliver").age(30).sex(true).phone("135000000008").address("深圳").build(),
                Customer.builder().name("Angus").age(10).sex(false).phone("135000000009").address("广州").build(),
                Customer.builder().name("Wendy").age(15).sex(false).phone("135000000000").address("西安").build()
        ).forEach(customer -> entityManager.persist(customer));
        entityManager.getTransaction().commit();
        entityManager.close();
    }

    /**
     * 查询全部 sql：SELECT * FROM tb_customer
     */
    @Test
    public void testFindAll() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 2.查询全部：创建Query查询对象，query对象才是执行jqpl的对象
        String sql = "select * from tb_customer";
        Query query = entityManager.createNativeQuery(sql, Customer.class);

        // 3.发送查询，并封装结果集
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

    /**
     * 排序查询 sql：select * from tb_customer order by customer_id desc
     */
    @Test
    public void testOder() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 采用链式调用，默认情况（升序）
        String sql1 = "select * from tb_customer order by customer_id";
        List<Customer> list1 = entityManager.createNativeQuery(sql1, Customer.class).getResultList();
        list1.forEach(System.out::println);

        // 采用链式调用，升序情况
        String sql2 = "select * from tb_customer order by customer_id asc ";
        List<Customer> list2 = entityManager.createNativeQuery(sql2, Customer.class).getResultList();
        list2.forEach(System.out::println);

        // 采用链式调用，降序情况
        String sql3 = "select * from tb_customer order by customer_id desc";
        List<Customer> list3 = entityManager.createNativeQuery(sql3, Customer.class).getResultList();
        list3.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

    /**
     * 条件查询
     *     sql：SELECT * FROM tb_customer where customer_sex = ?1 and customer_name like ?2
     *     sql：SELECT * FROM tb_customer where customer_sex = :aaa and customer_name like :bbb
     */
    @Test
    public void testWhere() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 条件查询：按参数位置绑定
        String sql1 = "SELECT * FROM tb_customer where customer_sex = ?1 and customer_name like ?2";
        Query query1 = entityManager.createNativeQuery(sql1, Customer.class);
        query1.setParameter(1, true);
        query1.setParameter(2, "S%");
        List<Customer> list1 = query1.getResultList();
        list1.forEach(System.out::println);

        // 条件查询：按参数名称绑定
        String sql2 = "SELECT * FROM tb_customer where customer_sex = :aaa and customer_name like :bbb";
        Query query2 = entityManager.createNativeQuery(sql2, Customer.class);
        query2.setParameter("aaa", true);
        query2.setParameter("bbb", "S%");
        List<Customer> list2 = query2.getResultList();
        list2.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

    /**
     * 分页查询 sql：select * from tb_customer limit 2,5
     */
    @Test
    public void testLimit() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 分页查询
        String sql = "select * from tb_customer limit 2,5";
        Query query = entityManager.createNativeQuery(sql, Customer.class);
        // 起始索引、每页查询的条数
        //query.setFirstResult(2).setMaxResults(5);
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

    /**
     * 分组查询
     *      sql：select count(*) from tb_customer
     *      sql：select c_sex,count(*) from tb_customer group by c_sex
     */
    @Test
    public void testGroupBy() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 聚合函数：count(),max(),min(),avg(),sum()
        String sql1 = "select count(*) from tb_customer";
        Object object2 = entityManager.createNativeQuery(sql1).getSingleResult();
        System.out.println(object2);

        // 分组统计：
        String sql2 = "select customer_sex,count(*) from tb_customer group by customer_sex";
        List<Object[]> list = entityManager.createNativeQuery(sql2).getResultList();
        list.forEach(x->System.out.println(Arrays.toString(x)));

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }
    
    /**
     * 数据更新 sql：update tb_customer set customer_age = 20 where customer_name = 'Sam'
     */
    @Test
    public void testUpdate() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 2.数据更新
        String sql = "update tb_customer set customer_age = :customer_age where customer_name = :customer_name";
        int updateSize = entityManager.createNativeQuery(sql, Customer.class)
                .setParameter("customer_age", 20)
                .setParameter("customer_name", "Sam")
                .executeUpdate();
        System.out.println("更新数据量："   updateSize);

        // 3.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

    /**
     * 数据删除 sql：delete tb_customer where customer_name = 'Sam'
     */
    @Test
    public void testDelete() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 2.数据删除
        String sql = "delete tb_customer where customer_name = :customer_name";
        int updateSize = entityManager.createNativeQuery(sql, Customer.class)
                .setParameter("customer_name", "Sam")
                .executeUpdate();
        System.out.println("删除数据量："   updateSize);

        // 3.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }
}

查看日志（随意抽取一条）：可以发现SQL语句与JPQL生成的还是有些不一样

Hibernate: 
    select
        * 
    from
        tb_customer
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)

查看数据更新与删除日志：

# 数据更新
Hibernate: 
    update
        tb_customer 
    set
        customer_age = ? 
    where
        customer_name = ?
更新数据量：1
# 数据删除
Hibernate: 
    delete tb_customer 
    where
        customer_name = ?
删除数据量：1

3、Criteria 复杂查询

JPA Criteria API：https://www.cnblogs.com/xingqi/p/3929386.html

1、通过JPA的 Criteria API 实现：

EntityManager 获取 CriteriaBuilder
CriteriaBuilder 创建 CriteriaQuery
CriteriaQuery 指定要查询的表，得到 Root（Root 代表要查询的表）
CriteriaBuilder 创建条件 Predicate，Predicate 相当于SQL的 where 条件，多个 Predicate 可以进行与、或操作
通过 EntityManager 创建 TypedQuery
TypedQuery 执行查询，返回结果

2、基本对象的构建详细步骤：

通过 EntityManager.getCriteriaBuilder() 或 EntityManagerFactory.getCriteriaBuilder() 方法可以得到 CriteriaBuilder 对象
通过 CriteriaBuilder.createQuery() 或 CriteriaBuilder.createTupleQuery() 方法可以获得 CriteriaQuery 实例
通过 CriteriaQuery.from() 方法可以获得 Root 实例
子句总结：
1. SELECT：CriteriaQuery select()
2. FROM：AbstractQuery from()
3. WHERE：AbstractQuery where()
4. ORDER BY：CriteriaQuery orderBy()
5. GROUP BY：AbstractQuery groupBy()
6. HAVING：AbstractQuery having()

3、过滤条件：

过滤条件会被应用到SQL语句的FROM子句中。在Criteria查询中，查询条件通过Predicate或Expression实例应用到CriteriaQuery对象上
这些条件使用 CriteriaQuery.where()方法应用到CriteriaQuery对象上
CriteriaBuilder也作为Predicate实例的工厂，通过调用CriteriaBuilder的条件方法（equal，notEqual， gt， ge，lt， le，between，like等）创建Predicate对象
复合的 Predicate 语句可以使用CriteriaBuilder的 and, or, andnot 方法构建

准备测试数据

import lombok.*;
import lombok.experimental.Tolerate;
import javax.persistence.*;

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "tb_customer")
public class Customer {

    // 这个构造是用来给 投影查询 准备的
    @Tolerate
    public Customer(String name, int age) {
        this.name = name;
        this.age = age;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Long Id; // 客户的主键

    @Column(name = "customer_name")
    private String name; // 客户名称

    @Column(name="customer_age")
    private int age; // 客户年龄

    @Column(name="customer_sex")
    private boolean sex; // 客户性别

    @Column(name="customer_phone")
    private String phone; // 客户的联系方式

    @Column(name="customer_address")
    private String address; // 客户地址
}

    @Before
    public void initData() {
        EntityManager entityManager = JpaUtils.getEntityManager();
        entityManager.getTransaction().begin();
        Arrays.asList(
                Customer.builder().name("Sam").age(18).sex(true).phone("135000000001").address("广州").build(),
                Customer.builder().name("Mike").age(20).sex(true).phone("135000000002").address("广州").build(),
                Customer.builder().name("Nick").age(25).sex(true).phone("135000000003").address("深圳").build(),
                Customer.builder().name("Hom").age(30).sex(true).phone("135000000004").address("西安").build(),
                Customer.builder().name("Rachel").age(25).sex(false).phone("135000000005").address("北京").build(),
                Customer.builder().name("Kath").age(30).sex(true).phone("135000000006").address("武汉").build(),
                Customer.builder().name("Vivi").age(28).sex(false).phone("135000000007").address("南京").build(),
                Customer.builder().name("Oliver").age(30).sex(true).phone("135000000008").address("深圳").build(),
                Customer.builder().name("Angus").age(10).sex(false).phone("135000000009").address("广州").build(),
                Customer.builder().name("Wendy").age(15).sex(false).phone("135000000000").address("西安").build()
        ).forEach(customer -> entityManager.persist(customer));
        entityManager.getTransaction().commit();
        entityManager.close();
    }

3.1、查询全部

    /**
     * 查询全部（简单写法）
     * sql：select * from tb_customer
     */
    @Test
    public void testFindAll() {
        // 获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        // 1.EntityManager获取CriteriaBuilder
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        // 2.CriteriaBuilder创建CriteriaQuery
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        // 3.CriteriaQuery指定要查询的表，得到Root，Root代表要查询的表
        criteriaQuery.from(Customer.class);
        TypedQuery<Customer> query = entityManager.createQuery(criteriaQuery);
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

    /**
     * 查询全部（完整写法）
     * sql：select * from tb_customer
     */
    @Test
    public void testFindAll2() {
        // 获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        // 1.EntityManager获取CriteriaBuilder
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        // 2.CriteriaBuilder创建CriteriaQuery
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        // 3.CriteriaQuery指定要查询的表，得到Root，Root代表要查询的表
        Root<Customer> from = criteriaQuery.from(Customer.class);
        // 这一步骤可以省略,返回的依旧是CriteriaQuery对象
        CriteriaQuery<Customer> select = criteriaQuery.select(from);
        // 如果上一步省略就传入criteriaQuery
        TypedQuery<Customer> query = entityManager.createQuery(select);
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)

3.2、投影查询

获取 CriteriaQuery 实例有两种方式：

CriteriaBuilder.createQuery()：返回 CriteriaQuery<T>
CriteriaBuilder.createTupleQuery()：返回CriteriaQuery<Tuple>

两种方式的区别：

createQuery()：主要用来查询与实体类字段对应的SQL
createTupleQuery()：可以查询实体类字段以外的字段数据（count、sum、max、min、avg 等）group by用的较多，一般配合criteriaQuery.multiselect() 方法较多

方式一：CriteriaBuilder.createQuery()

    /**
     * 投影查询 — criteriaBuilder.createQuery(Customer.class)
     * sql：select customer_name, customer_age from tb_customer
     * 注意：
     *   实体类中必须要有该构造方法：public Customer(String name, int age) 不然会报错
     * 也可以用另一个方式：
     *   criteriaBuilder.createTupleQuery() // 创建查询，返回元组类型，Tuple包含多个TupleElements
     */
    @Test
    public void testFindAllMultiSelect() {
        // 获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        // 1.CriteriaBuilder 安全查询创建工厂
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        // 2.CriteriaQuery 安全查询主语句
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        // 3.Root 定义查询的From子句中能出现的类型
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 必须要有该构造方法才行：public Customer(String name, int age)，不然会报错
        criteriaQuery.multiselect(from.get("name"), from.get("age"));
        TypedQuery<Customer> query = entityManager.createQuery(criteriaQuery);
        List<Customer> list = query.getResultList();
        list.forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_name as col_0_0_,
        customer0_.customer_age as col_1_0_ 
    from
        tb_customer customer0_
Customer(Id=null, name=Sam, age=18, sex=false, phone=null, address=null)
Customer(Id=null, name=Mike, age=20, sex=false, phone=null, address=null)
Customer(Id=null, name=Nick, age=25, sex=false, phone=null, address=null)
Customer(Id=null, name=Hom, age=30, sex=false, phone=null, address=null)
Customer(Id=null, name=Rachel, age=25, sex=false, phone=null, address=null)
Customer(Id=null, name=Kath, age=30, sex=false, phone=null, address=null)
Customer(Id=null, name=Vivi, age=28, sex=false, phone=null, address=null)
Customer(Id=null, name=Oliver, age=30, sex=false, phone=null, address=null)
Customer(Id=null, name=Angus, age=10, sex=false, phone=null, address=null)
Customer(Id=null, name=Wendy, age=15, sex=false, phone=null, address=null)

方式二：CriteriaBuilder.createTupleQuery()

    /**
     * 投影查询 — criteriaBuilder.createTupleQuery()
     * sql：select customer_name, customer_age from tb_customer
     * 注意：
     *   使用：返回元组(Tuple)的查询，不会报错
     */
    @Test
    public void testFindAllMultiSelect2() {
        // 获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        // 1.CriteriaBuilder 安全查询创建工厂
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        // 2.CriteriaQuery 安全查询主语句
        CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
        // 3.Root 定义查询的From子句中能出现的类型
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 需要给字段取别名,否则无法通过tuple.get(field)获取数据
        criteriaQuery.multiselect(from.get("name").alias("name"), from.get("age").alias("age"));
        TypedQuery<Tuple> query = entityManager.createQuery(criteriaQuery);
        List<Tuple> list = query.getResultList();
        // name = list.get(0).get(0); age = list.get(0).get(1)
        list.forEach(x-> System.out.println(x.get("name") "、" x.get("age")));

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_name as col_0_0_,
        customer0_.customer_age as col_1_0_ 
    from
        tb_customer customer0_
Sam、18
Mike、20
Nick、25
Hom、30
Rachel、25
Kath、30
Vivi、28
Oliver、30
Angus、10
Wendy、15

3.3、条件查询

1、单条件查询

    /**
     * 条件查询——单条件
     * Predicate 过滤条件
     * sql：select * from tb_customer where customer_name = 'Sam'
     */
    @Test
    public void testFindWhereEQ() {
        // 获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        Root<Customer> from = criteriaQuery.from(Customer.class);
        criteriaQuery.select(from);// 定义查询，该行可以省略

        // 4.Predicate 或 Predicate[] 过滤条件
        Predicate predicate = criteriaBuilder.equal(from.get("name"), "Sam");
        criteriaQuery.where(predicate);
        // 采用链式调用
        entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        customer0_.customer_name=?
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)

2、多条件查询

Predicate[]、Conjunction和 Disjunction 对比：

Predicate[]：逻辑与（and）
criteriaBuilder.conjunction()：逻辑与（and）
criteriaBuilder.disjunction()：逻辑或（or）

Predicate[] 与 conjunction 区别：Predicate[] 正常把数组内所有条件用 and 拼接，而 conjunction 会在 where 后面自动加上 1=1 然后再拼接条件

操作示例：

    /**
     * 条件查询——多条件（and，equals，lt）
     * Predicate[] 多个过滤条件（个人推荐）
     * sql：select * from tb_customer where customer_name = 'Sam' and c_age < 20
     */
    @Test
    public void testFindWhereEqGt() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 4.Predicate[] 过滤条件，设置一个查询条件集合
        List<Predicate> predicates = new ArrayList<>();
        predicates.add(criteriaBuilder.equal(from.get("name"), "Sam"));
        predicates.add(criteriaBuilder.lt(from.get("age"), 20));
        criteriaQuery.where(predicates.toArray(new Predicate[]{}));// new Predicate[]{}=》new Predicate[predicates.size()]
        // 采用链式调用
        entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

    /**
     * 条件查询——多条件（like，and，equal，lt）
     * Predicate 多个过滤条件 —— criteriaBuilder.conjunction() // 逻辑与
     *                       —— criteriaBuilder.disjunction() // 逻辑或
     * sql：select * from tb_customer where 1=1 and customer_name like 'S%' and customer_name = 'Sam' and customer_age < 20
     */
    @Test
    public void testFindWhereEqGt2() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 4.Predicate[] 过滤条件，设置一个查询条件集合
        Predicate predicate = criteriaBuilder.conjunction();
        // 过滤条件— like：and customer_name like S%
        predicate= criteriaBuilder.and(predicate, criteriaBuilder.like(from.get("name"), "S%"));
        // 过滤条件— equal：and customer_name like S% and customer_name = 'Sam'
        predicate= criteriaBuilder.and(predicate, criteriaBuilder.equal(from.get("name"), "Sam"));
        // 过滤条件— lt：and customer_name like S% and customer_name = 'Sam' and customer_age < 20
        predicate= criteriaBuilder.and(predicate, criteriaBuilder.lt(from.get("age"), 20));
        criteriaQuery.where(predicate);
        // 采用链式调用
        entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

    /**
     * 条件查询——多条件（not in，between）
     * Predicate 多个过滤条件 —— criteriaBuilder.conjunction() // 逻辑与
     *                        —— criteriaBuilder.disjunction() // 逻辑或
     * sql：select * from tb_customer where 1=1 and customer_age not in (18 , 20) and customer_id between 5 and 9
     */
    @Test
    public void testFindWhereNotInBetween() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 4.Predicate 过滤多条件
        Predicate predicate = criteriaBuilder.conjunction();
        // 过滤条件— in：customer_age not in (18 , 20)
        predicate= criteriaBuilder.and(predicate, from.get("age").in(Arrays.asList(18, 20)).not());
        // 过滤条件— in：customer_age not in (18 , 20) and customer_id between 5 and 9
        predicate= criteriaBuilder.and(predicate, criteriaBuilder.between(from.get("Id"), 5,9));
        criteriaQuery.where(predicate);
        // 采用链式调用
        entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

查看日志：（条件查询——多条件（and，equals，lt））

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        customer0_.customer_name=? 
        and customer0_.customer_age<20
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)

查看日志：（条件查询——多条件（like，and，equal，lt））

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        1=1 
        and (
            customer0_.customer_name like ?
        ) 
        and customer0_.customer_name=? 
        and customer0_.customer_age<20
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)

查看日志：（条件查询——多条件（not in，between））

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        1=1 
        and (
            customer0_.customer_age not in  (
                18 , 20
            )
        ) 
        and (
            customer0_.customer_id between 5 and 9
        )
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)

3.4、排序查询

可以设置单个order和多个order

    /**
     * 排序查询
     * sql：select * from tb_customer where 1=1 and customer_age in (18 , 28) and customer_id between 2 and 4 order by customer_id desc
     */
    @Test
    public void testFindWhereInBetweenOderBy() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 4.Predicate 或 Predicate[] 过滤条件
        Predicate age = from.get("age").in(18, 28);
        Predicate id = criteriaBuilder.between(from.get("Id"), 2, 10);
        criteriaQuery.where(Arrays.asList(age, id).toArray(new Predicate[]{}));
        // 排序（降序）
        Order idOrder = criteriaBuilder.desc(from.get("Id"));
        criteriaQuery.orderBy(idOrder);
        // 可以设置多个 order。先按Id降序，然后按age升序排序
        // criteriaQuery.orderBy(criteriaBuilder.desc(from.get("Id")),criteriaBuilder.asc(from.get("age")));
        // 采用链式调用
        entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        (
            customer0_.customer_age in (
                18 , 28
            )
        ) 
        and (
            customer0_.customer_id between 2 and 10
        ) 
    order by
        customer0_.customer_id desc
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)

3.5、分页查询

    /**
     * 分页查询
     * sql：select * from tb_customer where 1=1 and customer_id between 2 and 10 limit 0,5
     */
    @Test
    public void testFindWhereBetween() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // 4.Predicate 或 Predicate[] 过滤条件
        Predicate id = criteriaBuilder.between(from.get("Id"), 2, 10);
        criteriaQuery.where(Arrays.asList(id).toArray(new Predicate[]{}));

        // 采用链式调用,分页查询
        entityManager.createQuery(criteriaQuery)
                .setFirstResult(0) // 起始索引
                .setMaxResults(5)  // 每页查询的条数
                .getResultList()
                .forEach(System.out::println);

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_2_,
        customer0_.customer_address as customer2_2_,
        customer0_.customer_age as customer3_2_,
        customer0_.customer_name as customer4_2_,
        customer0_.customer_phone as customer5_2_,
        customer0_.customer_sex as customer6_2_ 
    from
        tb_customer customer0_ 
    where
        customer0_.customer_id between 2 and 10 limit ?
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)

3.6、分组查询

分组查询需要注意事项（group by ）：

创建查询用元组类型：criteriaBuilder.createTupleQuery()
设置多个选择结果：criteriaQuery.multiselect()
给字段设置别名（这不是必须的，为了获取方便可以设置）

    /**
     * 分组查询
     * sql：select c_name,count(c_name),max(c_age),min(c_age),sum(c_age),avg(c_age) from c_customer group by c_name
     */
    @Test
    public void testFindGroupBy() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
        Root<Customer> from = criteriaQuery.from(Customer.class);

        // .alias("name") 取别名
        criteriaQuery.multiselect(
                from.get("name").alias("name"),
                criteriaBuilder.count(from.get("name")).alias("count"),
                criteriaBuilder.max(from.get("age")).alias("max"),
                criteriaBuilder.min(from.get("age")).alias("min"),
                criteriaBuilder.sum(from.get("age")).alias("sum"),
                criteriaBuilder.avg(from.get("age")).alias("avg"));
        criteriaQuery.groupBy(from.get("name"));
        // criteriaQuery.having(criteriaBuilder.disjunction());

        // 采用链式调用
        entityManager.createQuery(criteriaQuery).getResultList()
                .forEach(x-> System.out.println(
                        x.get("name")   "、"  
                        x.get("count")   "、"  
                        x.get("max")   "、"  
                        x.get("min")   "、"  
                        x.get("sum")   "、"  
                        x.get("avg"))
                );

        // 释放资源
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_name as col_0_0_,
        count(customer0_.customer_name) as col_1_0_,
        max(customer0_.customer_age) as col_2_0_,
        min(customer0_.customer_age) as col_3_0_,
        sum(customer0_.customer_age) as col_4_0_,
        avg(customer0_.customer_age) as col_5_0_ 
    from
        tb_customer customer0_ 
    group by
        customer0_.customer_name
Angus、1、10、10、10、10.0
Hom、1、30、30、30、30.0
Kath、1、30、30、30、30.0
Mike、1、20、20、20、20.0
Nick、1、25、25、25、25.0
Oliver、1、30、30、30、30.0
Rachel、1、25、25、25、25.0
Sam、1、18、18、18、18.0
Vivi、1、28、28、28、28.0
Wendy、1、15、15、15、15.0

3.7、多表查询

建议先参考多对一教程的实体类测试，或者也可以直接使用下方给定的实体类

/**
 * 多方/外键表
 * 本实例采用的是：单向多对一。所以只需要配置多方即可
 */
@Data
@Entity
@Table(name = "t_many")
public class Many {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "many_id")
    private Long manyId;
    @Column(name = "many_name")
    private String manyName;

    @ManyToOne
    @JoinColumn(name="one_id")
    private One one;
}


/**
 * 一方/主键表
 * 本实例采用的是：单向多对一。所以只需要配置多方即可
 */
@Data
@Entity
@Table(name = "t_one")
public class One {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "one_id")
    private Long oneId;
    @Column(name = "one_type")
    private String oneType;
}

测试代码：

    /**
     * 多表关联：单向多对一
     * 数据插入
     */
    @Test
    public void testSaveJoinTable() {
        // 1.获取entityManager对象
        EntityManager entityManager = JpaUtils.getEntityManager();
        // 开启事务
        entityManager.getTransaction().begin();

        // 给One插入一条数据（主键表）
        One one = new One();
        one.setOneType("初中");
        entityManager.persist(one);

        // 给Many插入三条数据（外键表）
        Many manyA = new Many();
        manyA.setManyName("初一");
        manyA.setOne(one);
        entityManager.persist(manyA);
        Many manyB = new Many();
        manyB.setManyName("初二");
        manyB.setOne(one);
        entityManager.persist(manyB);
        Many manyC = new Many();
        manyC.setManyName("初三");
        manyC.setOne(one);
        entityManager.persist(manyC);

        // 提交事务
        entityManager.getTransaction().commit();
    }

    /**
     * Root代表需要查询的表（这里表示的是Many表）
     * Join代表连接查询(表)，通过root对象获取（Join代表的是关联表One表）
     * 创建的过程中，第一个参数为关联对象的属性名称，第二个参数为连接查询的方式（left，inner，right）
     * JoinType.INNER：内连接, JoinType.LEFT: 左外连接, JoinType.RIGHT：右外连接
     * 使用多表关联后，Join 就相当有了 root 的功能，可以join.get("对象属性名称")
     * 注意！注意！注意：
     *     Root：代表的是Many表，所以通过root.get()只能获取Many表的属性或字段.
     *     join：代表的是One表，虽然是关联查询，但它只代表关联的One表，只能获取One的属性
     *     如果root/join获取自己表以外的属性或字段会报如下错：
     *     Unable to locate Attribute  with the the given name [categoryType] on this ManagedType[XXX]
     *     如果有第三张表关联使用join.join()往下传递即可。
     */
    @Test
    public void testFindJoinTable() {
        // 1.获取entityManager对象 && 这里就省略事务了
        EntityManager entityManager = JpaUtils.getEntityManager();

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Many> criteriaQuery = criteriaBuilder.createQuery(Many.class);

        // 重点：root只能代表Many表，join只能代表One表。
        Root<Many> root = criteriaQuery.from(Many.class);
        Join<Many, One> join = root.join("one", JoinType.LEFT);

        Path<Object> type = join.get("oneType");
        Path<Object> manyName = root.get("manyName");
        Predicate p1 = criteriaBuilder.equal(type, "初中");
        Predicate p2 = criteriaBuilder.equal(manyName, "初一");
        Predicate predicate = criteriaBuilder.and(p1, p2);
        criteriaQuery.where(predicate);
        TypedQuery<Many> query = entityManager.createQuery(criteriaQuery);
        List<Many> resultList = query.getResultList();
        resultList.forEach(System.out::println);
    }

查看日志：

Hibernate: 
    select
        many0_.many_id as many_id1_3_,
        many0_.many_name as many_nam2_3_,
        many0_.one_id as one_id3_3_ 
    from
        t_many many0_ 
    left outer join
        t_one one1_ 
            on many0_.one_id=one1_.one_id 
    where
        one1_.one_type=? 
        and many0_.many_name=?
Hibernate: 
    select
        one0_.one_id as one_id1_4_0_,
        one0_.one_type as one_type2_4_0_ 
    from
        t_one one0_ 
    where
        one0_.one_id=?
Many(manyId=1, manyName=初一, one=One(oneId=1, oneType=初中))

4、NamedQuery 别名查询

又叫别名查询。主要使用：@NamedQuery 或者 @NamedNativeQuery entityManager.createNamedQuery() 来查询

实体类：

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "tb_customer")
// 主要加上该注解，name是取名，用来被引用的。query是JPQL语句
@NamedQuery(name = "selectAllNamedQuery", query = "from Customer")
@NamedNativeQuery(name = "selectAllNamedNativeQuery", query = "select * from tb_customer", resultClass = Customer.class)
public class Customer {

    @Tolerate
    public Customer(String name, int age) {
        this.name = name;
        this.age = age;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "customer_id")
    private Long Id; // 客户的主键

    @Column(name = "customer_name")
    private String name; // 客户名称

    @Column(name="customer_age")
    private int age; // 客户年龄

    @Column(name="customer_sex")
    private boolean sex; // 客户性别

    @Column(name="customer_phone")
    private String phone; // 客户的联系方式

    @Column(name="customer_address")
    private String address; // 客户地址

}

测试方法：

    /**
     * 查询全部：createNamedQuery()
     *     jqpl：from Customer
     *     sql：SELECT * FROM c_customer
     */
    @Test
    public void testNamedQuery() {
        // 1.获取entityManager对象 && 开启事务
        EntityManager entityManager = JpaUtils.getEntityManager();
        EntityTransaction transaction = entityManager.getTransaction();
        transaction.begin();

        // 2.查询全部：创建Query查询对象，query对象才是执行jqpl和原生sql的对象。发送查询，并封装结果集
        // 使用@NamedQuery的name
        entityManager.createNamedQuery("selectAllNamedQuery").getResultList().forEach(System.out::println);
        // 使用@NamedNativeQuery的name
        entityManager.createNamedQuery("selectAllNamedNativeQuery", Customer.class).getResultList().forEach(System.out::println);

        // 4.提交事务 && 释放资源
        transaction.commit();
        entityManager.close();
    }

查看日志：

Hibernate: 
    select
        customer0_.customer_id as customer1_3_,
        customer0_.customer_address as customer2_3_,
        customer0_.customer_age as customer3_3_,
        customer0_.customer_name as customer4_3_,
        customer0_.customer_phone as customer5_3_,
        customer0_.customer_sex as customer6_3_ 
    from
        tb_customer customer0_
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)
Hibernate: 
    select
        * 
    from
        tb_customer
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=广州)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=广州)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=深圳)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=西安)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=北京)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=武汉)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=南京)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=深圳)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=广州)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=西安)

5、Query 接口方法介绍

JPQL 语言和原生 SQL 语句可以是 select 语句、update 语句或 delete语句（注意：无法执行insert语句），它们都通过 Query 接口封装执行。Query 接口封装了执行数据库查询的相关方法。调用 EntityManager 的 createQuery、createNamedQuery 及 createNativeQuery 方法可以获得查询对象，进而调用 Query 接口的相关方法来执行查询操作。

/**
 * JPQL 语言和原生 SQL 语句可以是 select 语句、update 语句或 delete语句（没有insert），它们都通过 Query 接口封装执行
 * Query 接口封装了执行数据库查询的相关方法。调用 EntityManager 的 createQuery、createNamedQuery 及 createNativeQuery 方法可以获得查询对象，进而调用 Query 接口的相关方法来执行查询操作
 *
 * int executeUpdate()
 * 用于执行update或delete语句
 *
 * List getResultList()
 * 用于执行select语句并返回结果集实体列表
 *
 * Object getSingleResult()
 * 用于执行只返回单个结果实体的select语句
 *
 * Query setFirstResult(int startPosition)
 * 用于设置从哪个实体记录开始返回查询结果
 *
 * Query setMaxResults(int maxResult)
 * 用于设置返回结果实体的最大数。与setFirstResult结合使用可实现分页查询
 *
 * Query setFlushMode(FlushModeType flushMode)
 * 设置查询对象的Flush模式。参数可以取2个枚举值：FlushModeType.AUTO 为自动更新数据库记录，FlushMode Type.COMMIT 为直到提交事务时才更新数据库记录
 *
 * setHint(String hintName, Object value)
 * 设置与查询对象相关的特定供应商参数或提示信息。参数名及其取值需要参考特定 JPA 实现库提供商的文档。如果第二个参数无效将抛出IllegalArgumentException异常
 *
 * setParameter(int position, Object value)
 * 为查询语句的指定位置参数赋值。Position 指定参数序号，value 为赋给参数的值
 *
 * setParameter(int position, Date d, TemporalType type)
 * 为查询语句的指定位置参数赋 Date 值。Position 指定参数序号，value 为赋给参数的值，temporalType 取 TemporalType 的枚举常量，包括 DATE、TIME 及 TIMESTAMP 三个，用于将 Java 的 Date 型值临时转换为数据库支持的日期时间类型（java.sql.Date、java.sql.Time及java.sql.Timestamp）
 *
 * setParameter(int position, Calendar c, TemporalType type)
 * 为查询语句的指定位置参数赋 Calenda r值。position 指定参数序号，value 为赋给参数的值，temporalType 的含义及取舍同前
 *
 * setParameter(String name, Object value)
 * 为查询语句的指定名称参数赋值
 *
 * setParameter(String name, Date d, TemporalType type)
 * 为查询语句的指定名称参数赋 Date 值。用法同前
 *
 * setParameter(String name, Calendar c, TemporalType type)
 * 为查询语句的指定名称参数设置Calendar值。name为参数名，其它同前。该方法调用时如果参数位置或参数名不正确，或者所赋的参数值类型不匹配，将抛出 IllegalArgumentException 异常
 */

这篇好文章是转载于：学新通技术网

Hibernate JPA 复杂查询