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 实例
- 子句总结:
- SELECT:CriteriaQuery select()
- FROM:AbstractQuery from()
- WHERE:AbstractQuery where()
- ORDER BY:CriteriaQuery orderBy()
- GROUP BY:AbstractQuery groupBy()
- 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()
: 主要用来查询与实体类字段对应的SQLcreateTupleQuery()
:可以查询实体类字段以外的字段数据(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 异常
*/