Hibernate JPA complex query
1. JPQL statement
Full name of JPQL: Java Persistence Query Language
EJB query language (EJB QL) and Java persistent query language (JPQL) introduced in EJB 2.0 are portable query languages. They aim to bind SQL syntax and simple query semantics with the expression of object-oriented expression language. Queries written in this language are portable and can be compiled into SQL on all mainstream database servers.
When using JPQL, the overall processing effect is almost the same as the syntax form of SQL, and then JPQL will be compiled into SQL statements supported by traditional relational databases when JPA is executed. Its features are similar to native SQL statements and are completely object-oriented, accessed through class names and attributes, rather than table names and table attributes.
Environmental preparation
1. Add some lombok annotations on the entity class to facilitate the preparation of test data
import lombok.*;
import lombok.experimental.Tolerate;
import javax.persistence.*;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "tb_customer")
public class Customer {
// This construct is used to prepare for projection queries
@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; // Customer's primary key
@Column(name = "customer_name")
private String name; // Customer name
@Column(name="customer_age")
private int age; // Customer age
@Column(name="customer_sex")
private boolean sex; // Customer gender
@Column(name="customer_phone")
private String phone; // Customer contact information
@Column(name="customer_address")
private String address; // Customer address
}
2. Prepare test data
@Before
public void initData() {
EntityManager entityManager = JpaUtils.getEntityManager();
entityManager.getTransaction().begin();
Arrays.asList(
Customer.builder().name("Sam").age(18).sex(true).phone("135000000001").address("Guangzhou").build(),
Customer.builder().name("Mike").age(20).sex(true).phone("135000000002").address("Guangzhou").build(),
Customer.builder().name("Nick").age(25).sex(true).phone("135000000003").address("Shenzhen").build(),
Customer.builder().name("Hom").age(30).sex(true).phone("135000000004").address("Xi'an").build(),
Customer.builder().name("Rachel").age(25).sex(false).phone("135000000005").address("Beijing").build(),
Customer.builder().name("Kath").age(30).sex(true).phone("135000000006").address("Wuhan").build(),
Customer.builder().name("Vivi").age(28).sex(false).phone("135000000007").address("Nanjing").build(),
Customer.builder().name("Oliver").age(30).sex(true).phone("135000000008").address("Shenzhen").build(),
Customer.builder().name("Angus").age(10).sex(false).phone("135000000009").address("Guangzhou").build(),
Customer.builder().name("Wendy").age(15).sex(false).phone("135000000000").address("Xi'an").build()
).forEach(customer -> entityManager.persist(customer));
entityManager.getTransaction().commit();
entityManager.close();
}
1.1. Simple query
/**
* Query all
* jqpl: from Customer
* sql: select * from c_customer
*/
@Test
public void testFindAll() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// 2. Query all: create a query object, which is the object to execute jqpl
Query query = entityManager.createQuery("from Customer");
// 3. Send the query and encapsulate the result set
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
1.2. Alias query
/**
* Alias query
* jqpl: from Customer c
* jqpl: select c from Customer c
* sql: select * from c_customer c
*/
@Test
public void testFindAll2() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Query all customers, using chain call
List<Customer> list1 = entityManager.createQuery("from Customer c").getResultList();
list1.forEach(System.out::println);
// Query all customers, using chain call
List<Customer> list2 = entityManager.createQuery("select c from Customer c").getResultList();
list2.forEach(System.out::println);
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
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=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
1.3 Sorting Query
/**
* Sort query
* jqpl: from Customer order by Id desc
* sql: select * from c_customer order by Id desc
*/
@Test
public void testOder() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Chain call is adopted. By default (ascending order)
List<Customer> list1 = entityManager.createQuery("from Customer order by Id").getResultList();
list1.forEach(System.out::println);
// Chain call is adopted, in ascending order
List<Customer> list2 = entityManager.createQuery("from Customer order by Id asc").getResultList();
list2.forEach(System.out::println);
// Chain call is adopted, and descending order is adopted
List<Customer> list3 = entityManager.createQuery("from Customer order by Id desc").getResultList();
list3.forEach(System.out::println);
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
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=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
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=Xi'an)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=Guangzhou)
1.4. Query criteria
/**
* Condition query
* 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. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Condition query: bind by parameter location
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);
// Condition query: bind by parameter name
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. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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=Guangzhou)
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=Guangzhou)
1.5. Projection query
/**
* Projection query
* 1.Single field query
* jqpl: select c.name from Customer c
* sql: SELECT * FROM c_customer order by Id desc
* 2.Multiple field query
* 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. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Query all customer names: single field query
Query query1 = entityManager.createQuery("select c.name from Customer c");
List<Object> list1 = query1.getResultList();
list1.forEach(System.out::println);
// Query all customer names and customer ages: query multiple fields and encapsulate them into an array
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)));
// Query all customer names and customer sources: query multiple fields and encapsulate them into objects
// Please add the following two construction methods in Customer.java, otherwise the execution will fail
// public Customer() and 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. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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 paging query
/**
* Paging query
* jqpl : from Customer
* sql: select * from c_customer limit 2,5
*/
@Test
public void testLimit() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Paging query
Query query = entityManager.createQuery("from Customer");
// Starting index, number of queries per page
query.setFirstResult(2).setMaxResults(5);
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
1.7 group query
/**
* Grouping query
* 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. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Aggregate function: count(),max(),min(),avg(),sum()
Object object2 = entityManager.createQuery("select count(*) from Customer").getSingleResult();
System.out.println(object2);
// Group statistics:
List<Object[]> list = entityManager.createQuery("select sex,count(*) from Customer group by sex").getResultList();
list.forEach(x->System.out.println(Arrays.toString(x)));
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
View log:
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. Multi table query
@Test
public void testXXJoin() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Inner connection
List<Object[]> list1 = entityManager.createQuery("from Customer c inner join c.linkMans").getResultList();
list1.forEach(x->System.out.println(Arrays.toString(x)));
// Urgent inner connection (unique to hibernate, which encapsulates the data of another object into this object)
List<Customer> list2 = entityManager.createQuery("select distinct c from Customer c inner join fetch c.linkMans").getResultList();
list2.forEach(System.out::println);
// Left outer connection
List<Object[]> list3 = entityManager.createQuery("from Customer c left outer join c.linkMans").getResultList();
list3.forEach(x->System.out.println(Arrays.toString(x)));
// Urgent left external connection (unique to hibernate, encapsulating the data of another object into this object)
List<Customer> list4 = entityManager.createQuery("select distinct c from Customer c left outer join fetch c.linkMans").getResultList();
list4.forEach(System.out::println);
// Right outer connection
List<Object[]> list5 = entityManager.createQuery("from Customer c right outer join c.linkMans").getResultList();
list5.forEach(x->System.out.println(Arrays.toString(x)));
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
2. Native SQL query
Sometimes, improper use of JPQL will lead to that the converted SQL is not as good as ideal, or SQL optimization is required for specific occasions, and native SQL queries are still needed. The createNativeQuery method of the EntityManager object can implement very complex queries, but the query results need to be entity mapped, and cannot cross the database.
-
Get the native SQL query object: Query createNativeQuery(String var1, Class var2);
-
Compared with JPQL, you only need to replace the query object method with createNativeQuery() from createQuery() (the passed in value becomes a native SQL and class object)
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("Guangzhou").build(),
Customer.builder().name("Mike").age(20).sex(true).phone("135000000002").address("Guangzhou").build(),
Customer.builder().name("Nick").age(25).sex(true).phone("135000000003").address("Shenzhen").build(),
Customer.builder().name("Hom").age(30).sex(true).phone("135000000004").address("Xi'an").build(),
Customer.builder().name("Rachel").age(25).sex(false).phone("135000000005").address("Beijing").build(),
Customer.builder().name("Kath").age(30).sex(true).phone("135000000006").address("Wuhan").build(),
Customer.builder().name("Vivi").age(28).sex(false).phone("135000000007").address("Nanjing").build(),
Customer.builder().name("Oliver").age(30).sex(true).phone("135000000008").address("Shenzhen").build(),
Customer.builder().name("Angus").age(10).sex(false).phone("135000000009").address("Guangzhou").build(),
Customer.builder().name("Wendy").age(15).sex(false).phone("135000000000").address("Xi'an").build()
).forEach(customer -> entityManager.persist(customer));
entityManager.getTransaction().commit();
entityManager.close();
}
/**
* Query all sql: SELECT * FROM tb_customer
*/
@Test
public void testFindAll() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// 2. Query all: create a query object, which is the object to execute jqpl
String sql = "select * from tb_customer";
Query query = entityManager.createNativeQuery(sql, Customer.class);
// 3. Send the query and encapsulate the result set
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
/**
* Sort query sql: select * from tb_customer order by customer_id desc
*/
@Test
public void testOder() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Chain call is adopted. By default (ascending order)
String sql1 = "select * from tb_customer order by customer_id";
List<Customer> list1 = entityManager.createNativeQuery(sql1, Customer.class).getResultList();
list1.forEach(System.out::println);
// Chain call is adopted, in ascending order
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);
// Chain call is adopted, and descending order is adopted
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. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
/**
* Condition query
* 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. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Condition query: bind by parameter location
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);
// Condition query: bind by parameter name
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. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
/**
* Paging query sql: select * from tb_customer limit 2,5
*/
@Test
public void testLimit() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Paging query
String sql = "select * from tb_customer limit 2,5";
Query query = entityManager.createNativeQuery(sql, Customer.class);
// Starting index, number of queries per page
//query.setFirstResult(2).setMaxResults(5);
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// 4. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
/**
* Grouping query
* sql: select count(*) from tb_customer
* sql: select c_sex,count(*) from tb_customer group by c_sex
*/
@Test
public void testGroupBy() {
// 1. Get entityManager object & & start transaction
EntityManager entityManager = JpaUtils.getEntityManager();
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
// Aggregate function: count(),max(),min(),avg(),sum()
String sql1 = "select count(*) from tb_customer";
Object object2 = entityManager.createNativeQuery(sql1).getSingleResult();
System.out.println(object2);
// Group statistics:
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. Commit transaction & & release resources
transaction.commit();
entityManager.close();
}
}
Check the log (select one at random): you can find that the SQL statement is still somewhat different from that generated by JPQL
Hibernate:
select
*
from
tb_customer
Customer(Id=1, name=Sam, age=18, sex=true, phone=135000000001, address=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
3. Criteria query
JPA Criteria API: https://www.cnblogs.com/xingqi/p/3929386.html
1. Through JPA's Criteria API:
- EntityManager get CriteriaBuilder
- CriteriaBuilder creates CriteriaQuery
- CriteriaQuery specifies the table to query to get the Root (Root represents the table to query)
- CriteriaBuilder creates a condition Predicate, which is equivalent to the where condition of SQL. Multiple predicates can be used for and, or operations
- Create TypedQuery through EntityManager
- TypedQuery executes the query and returns the result
2. Detailed steps for building basic objects:
- The CriteriaBuilder object can be obtained through the EntityManager.getCriteriaBuilder() or EntityManagerFactory.getCriteriaBuilder() methods
- The CriteriaQuery instance can be obtained by calling the CriteriaBuilder.createQuery() or createuplequery() methods
- The Root instance can be obtained by calling the CriteriaQuery.from() method
3. Filter conditions:
- The filter condition is applied to the FROM clause of the SQL statement. In Criteria query, query Criteria are applied to Criteria query objects through Predicate or Expression instances
- These conditions are applied to the CriteriaQuery object using the CriteriaQuery.where() method
- CriteriaBuilder is also used as the factory of the Predicate instance to create the Predicate object by calling the conditional methods of CriteriaBuilder (equal, notEqual, gt, ge, lt, le, between, like, etc.).
- Composite Predicate statements can be built using the and, or, and not methods of CriteriaBuilder
Prepare test data
import lombok.*;
import lombok.experimental.Tolerate;
import javax.persistence.*;
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "tb_customer")
public class Customer {
// This construct is used to prepare for projection queries
@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; // Customer's primary key
@Column(name = "customer_name")
private String name; // Customer name
@Column(name="customer_age")
private int age; // Customer age
@Column(name="customer_sex")
private boolean sex; // Customer gender
@Column(name="customer_phone")
private String phone; // Customer contact information
@Column(name="customer_address")
private String address; // Customer 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("Guangzhou").build(),
Customer.builder().name("Mike").age(20).sex(true).phone("135000000002").address("Guangzhou").build(),
Customer.builder().name("Nick").age(25).sex(true).phone("135000000003").address("Shenzhen").build(),
Customer.builder().name("Hom").age(30).sex(true).phone("135000000004").address("Xi'an").build(),
Customer.builder().name("Rachel").age(25).sex(false).phone("135000000005").address("Beijing").build(),
Customer.builder().name("Kath").age(30).sex(true).phone("135000000006").address("Wuhan").build(),
Customer.builder().name("Vivi").age(28).sex(false).phone("135000000007").address("Nanjing").build(),
Customer.builder().name("Oliver").age(30).sex(true).phone("135000000008").address("Shenzhen").build(),
Customer.builder().name("Angus").age(10).sex(false).phone("135000000009").address("Guangzhou").build(),
Customer.builder().name("Wendy").age(15).sex(false).phone("135000000000").address("Xi'an").build()
).forEach(customer -> entityManager.persist(customer));
entityManager.getTransaction().commit();
entityManager.close();
}
3.1. Query all
/**
* Query all (simple)
* sql: select * from tb_customer
*/
@Test
public void testFindAll() {
// Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
// 1. Get CriteriaBuilder from entitymanager
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 2.CriteriaBuilder creates CriteriaQuery
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
// 3.CriteriaQuery specifies the table to be queried to get the Root, which represents the table to be queried
criteriaQuery.from(Customer.class);
TypedQuery<Customer> query = entityManager.createQuery(criteriaQuery);
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// Release resources
entityManager.close();
}
/**
* Query all (full)
* sql: select * from tb_customer
*/
@Test
public void testFindAll2() {
// Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
// 1. Get CriteriaBuilder from entitymanager
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 2.CriteriaBuilder creates CriteriaQuery
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
// 3.CriteriaQuery specifies the table to be queried to get the Root, which represents the table to be queried
Root<Customer> from = criteriaQuery.from(Customer.class);
// This step can be omitted, and the CriteriaQuery object is still returned
CriteriaQuery<Customer> select = criteriaQuery.select(from);
// If the previous step is omitted, the criteriaQuery is passed in
TypedQuery<Customer> query = entityManager.createQuery(select);
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// Release resources
entityManager.close();
}
View log:
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=Guangzhou)
Customer(Id=2, name=Mike, age=20, sex=true, phone=135000000002, address=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
Customer(Id=10, name=Wendy, age=15, sex=false, phone=135000000000, address=Xi'an)
3.2. Projection query
There are two ways to get CriteriaQuery instances:
- CriteriaBuilder.createQuery(): return criteriaquery < T >
- Criteriabuilder. Createtablequery(): returns criteriaquery < tuple >
Differences between the two methods:
- createQuery(): mainly used to query SQL corresponding to entity class fields
- createTupleQuery(): you can query field data (count, sum, max, min, avg, etc.) other than entity class fields. group by is used more often, generally combined with the criteriaQuery.multiselect() method
Method 1: CriteriaBuilder.createQuery()
/**
* Projection query - criteriaBuilder.createQuery(Customer.class)
* sql: select customer_name, customer_age from tb_customer
* be careful:
* The constructor must be public Customer(String name, int age) in the entity class, otherwise an error will be reported
* You can also use another method:
* criteriaBuilder.createTupleQuery() // Create a query and return the tuple type. Tuple contains multiple TupleElements
*/
@Test
public void testFindAllMultiSelect() {
// Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
// 1.CriteriaBuilder security query creation factory
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 2.CriteriaQuery security query subject sentence
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
// 3.Root defines the types that can appear in the From clause of the query
Root<Customer> from = criteriaQuery.from(Customer.class);
// You must have this constructor: public Customer(String name, int age), otherwise an error will be reported
criteriaQuery.multiselect(from.get("name"), from.get("age"));
TypedQuery<Customer> query = entityManager.createQuery(criteriaQuery);
List<Customer> list = query.getResultList();
list.forEach(System.out::println);
// Release resources
entityManager.close();
}
View log:
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)
Method 2: criteriabuilder. Createtablequery ()
/**
* Projection query - criteriabuilder. Createtablequery()
* sql: select customer_name, customer_age from tb_customer
* be careful:
* Use: returns a tuple query without error
*/
@Test
public void testFindAllMultiSelect2() {
// Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
// 1.CriteriaBuilder security query creation factory
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 2.CriteriaQuery security query subject sentence
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
// 3.Root defines the types that can appear in the From clause of the query
Root<Customer> from = criteriaQuery.from(Customer.class);
// You need to alias the field, otherwise you cannot get data through 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")));
// Release resources
entityManager.close();
}
View log:
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. Query criteria
1. Single condition query
/**
* Condition query - single condition
* Predicate Filter condition
* sql: select * from tb_customer where customer_name = 'Sam'
*/
@Test
public void testFindWhereEQ() {
// Get entityManager object & & the transaction is omitted here
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);// Define the query, and this row can be omitted
// 4. Predict or predict [] filter conditions
Predicate predicate = criteriaBuilder.equal(from.get("name"), "Sam");
criteriaQuery.where(predicate);
// Using chain call
entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);
// Release resources
entityManager.close();
}
View log:
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=Guangzhou)
2. Multi condition query
Connection, disconnection and Predicate [] comparison:
- Criteriabuilder. Junction(): logical and
- Criteriabuilder. Disconnection(): logical or (or)
- Predicate []: logical and (and)
The difference between Predicate [] and conjunction: Predicate [] normally splices all conditions in the array with and, while conjunction automatically adds 1 = 1 after where, and then splices the conditions
Operation example:
/**
* Condition query - and, equals, lt
* Predicate[] Multiple filter conditions (personal recommendation)
* sql: select * from tb_customer where customer_name = 'Sam' and c_age < 20
*/
@Test
public void testFindWhereEqGt() {
// 1. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
Root<Customer> from = criteriaQuery.from(Customer.class);
// 4. Predict [] filter criteria: set a set of query criteria
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()]
// Using chain call
entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);
// Release resources
entityManager.close();
}
/**
* Condition query - like, and, equal, lt
* Predicate Multiple filter conditions - criteriabuilder. Junction() / / logical and
* - criteriaBuilder.disjunction() // Logical or
* 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. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
Root<Customer> from = criteriaQuery.from(Customer.class);
// 4. Predict [] filter criteria: set a set of query criteria
Predicate predicate = criteriaBuilder.conjunction();
// Filter condition - like: and customer_name like S%
predicate= criteriaBuilder.and(predicate, criteriaBuilder.like(from.get("name"), "S%"));
// Filter condition - equal: and customer_name like S% and customer_name = 'Sam'
predicate= criteriaBuilder.and(predicate, criteriaBuilder.equal(from.get("name"), "Sam"));
// Filter criteria - 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);
// Using chain call
entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);
// Release resources
entityManager.close();
}
/**
* Condition query - not in, between
* Predicate Multiple filter conditions - criteriabuilder. Junction() / / logical and
* - criteriaBuilder.disjunction() // Logical or
* 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. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
Root<Customer> from = criteriaQuery.from(Customer.class);
// 4. Predict filter multiple conditions
Predicate predicate = criteriaBuilder.conjunction();
// Filter condition - in: customer_age not in (18 , 20)
predicate= criteriaBuilder.and(predicate, from.get("age").in(Arrays.asList(18, 20)).not());
// Filter condition - 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);
// Using chain call
entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);
// Release resources
entityManager.close();
}
View log: (condition query - multiple conditions (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=Guangzhou)
View log: (condition query - 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=Guangzhou)
View log: (condition query - 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=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
Customer(Id=7, name=Vivi, age=28, sex=false, phone=135000000007, address=Nanjing)
Customer(Id=8, name=Oliver, age=30, sex=true, phone=135000000008, address=Shenzhen)
Customer(Id=9, name=Angus, age=10, sex=false, phone=135000000009, address=Guangzhou)
3.4 Sorting Query
You can set a single order and multiple orders
/**
* Sort query
* 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. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
Root<Customer> from = criteriaQuery.from(Customer.class);
// 4. Predict or predict [] filter conditions
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[]{}));
// Sort (descending)
Order idOrder = criteriaBuilder.desc(from.get("Id"));
criteriaQuery.orderBy(idOrder);
// Multiple orders can be set. Sort by Id in descending order and then by age in ascending order
// criteriaQuery.orderBy(criteriaBuilder.desc(from.get("Id")),criteriaBuilder.asc(from.get("age")));
// Using chain call
entityManager.createQuery(criteriaQuery).getResultList().forEach(System.out::println);
// Release resources
entityManager.close();
}
View log:
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=Nanjing)
3.5 paging query
/**
* Paging query
* sql: select * from tb_customer where 1=1 and customer_id between 2 and 10 limit 0,5
*/
@Test
public void testFindWhereBetween() {
// 1. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Customer> criteriaQuery = criteriaBuilder.createQuery(Customer.class);
Root<Customer> from = criteriaQuery.from(Customer.class);
// 4. Predict or predict [] filter conditions
Predicate id = criteriaBuilder.between(from.get("Id"), 2, 10);
criteriaQuery.where(Arrays.asList(id).toArray(new Predicate[]{}));
// It adopts chain call and paging query
entityManager.createQuery(criteriaQuery)
.setFirstResult(0) // Start index
.setMaxResults(5) // Number of queries per page
.getResultList()
.forEach(System.out::println);
// Release resources
entityManager.close();
}
View log:
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=Guangzhou)
Customer(Id=3, name=Nick, age=25, sex=true, phone=135000000003, address=Shenzhen)
Customer(Id=4, name=Hom, age=30, sex=true, phone=135000000004, address=Xi'an)
Customer(Id=5, name=Rachel, age=25, sex=false, phone=135000000005, address=Beijing)
Customer(Id=6, name=Kath, age=30, sex=true, phone=135000000006, address=Wuhan)
3.6 group query
group by:
- Tuple type for creating query: criteriaBuilder.createTupleQuery()
- Set multiple selection results: criteriaQuery.multiselect()
- Set an alias for the field (this is not necessary and can be set for convenience)
/**
* Grouping query
* 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. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root<Customer> from = criteriaQuery.from(Customer.class);
// . alias("name") alias
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());
// Using chain call
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"))
);
// Release resources
entityManager.close();
}
View log:
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. Multi table query
It is recommended to refer to the entity class test in the many to one tutorial first, or you can directly use the entity class given below
/**
* Multi party / foreign key table
* This example adopts one-way many to one. Therefore, you only need to configure multiple parties
*/
@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;
}
/**
* One party / primary key table
* This example adopts one-way many to one. Therefore, you only need to configure multiple parties
*/
@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 code:
/**
* Multi table Association: unidirectional many to one
* Data insertion
*/
@Test
public void testSaveJoinTable() {
// 1. Get entityManager object
EntityManager entityManager = JpaUtils.getEntityManager();
// Open transaction
entityManager.getTransaction().begin();
// Insert a piece of data (primary key table) into One
One one = new One();
one.setOneType("junior middle school");
entityManager.persist(one);
// Insert three pieces of data for Many (foreign key table)
Many manyA = new Many();
manyA.setManyName("First day");
manyA.setOne(one);
entityManager.persist(manyA);
Many manyB = new Many();
manyB.setManyName("The second day of junior high school");
manyB.setOne(one);
entityManager.persist(manyB);
Many manyC = new Many();
manyC.setManyName("Junior three");
manyC.setOne(one);
entityManager.persist(manyC);
// Commit transaction
entityManager.getTransaction().commit();
}
/**
* Root Represents the table to be queried (the Many table is represented here)
* Join Represents the connection query (table), which is obtained through the root object (Join represents the associated table One table)
* During the creation process, the first parameter is the attribute name of the associated object, and the second parameter is the connection query method (left, inner, right)
* JoinType.INNER: Inner connection, JoinType.LEFT: left outer connection, JoinType.RIGHT: right outer connection
* After using multi table Association, Join has the function of root. You can join.get("object attribute name")
* be careful! be careful! be careful:
* Root: It represents the Many table, so you can only get the attributes or fields of the Many table through root.get()
* join: It represents the One table. Although it is an associated query, it only represents the associated One table and can only obtain the properties of One
* If root/join obtains attributes or fields other than its own table, the following error will be reported:
* Unable to locate Attribute with the the given name [categoryType] on this ManagedType[XXX]
* If there is a third table Association, use join.join() to pass it down.
*/
@Test
public void testFindJoinTable() {
// 1. Get entityManager object & & the transaction is omitted here
EntityManager entityManager = JpaUtils.getEntityManager();
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Many> criteriaQuery = criteriaBuilder.createQuery(Many.class);
// Important: root can only represent the Many table, and join can only represent the One table.
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, "junior middle school");
Predicate p2 = criteriaBuilder.equal(manyName, "First day");
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);
}
View log:
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=First day, one=One(oneId=1, oneType=junior middle school))