Specifications动态查询
在查询某个实体的时候,给定的条件是不固定的,这时就需要动态构建相应的查询语句,在Spring Data JPA中可以通过JpaSpecificationExecutor接口查询。相比JPQL,其优势是类型安全,更加的面向对象
import java.util.List;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
/**
* JpaSpecificationExecutor中定义的方法
**/
public interface JpaSpecificationExecutor<T> {
//根据条件查询一个对象
T findOne(Specification<T> spec);
//根据条件查询集合
List<T> findAll(Specification<T> spec);
//根据条件分页查询
Page<T> findAll(Specification<T> spec, Pageable pageable);
//排序查询查询
List<T> findAll(Specification<T> spec, Sort sort);
//统计查询
long count(Specification<T> spec);
}
对于JpaSpecificationExecutor,这个接口基本是围绕着Specification接口来定义的。可以简单的理解为,Specification构造的就是查询条件。
//构造查询条件
/**
* root :Root接口,代表查询的根对象,可以通过root获取实体中的属性
* query :代表一个顶层查询对象,用来自定义查询
* cb :用来构建查询,此对象里有很多条件方法
**/
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder cb);
配置文件
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:tx="http://www.springframework.org/schema/tx"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xmlns:task="http://www.springframework.org/schema/task"
xmlns:contxt="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd
http://www.springframework.org/schema/data/jpa
http://www.springframework.org/schema/data/jpa/spring-jpa.xsd">
<!-- 配置实体类管理工厂 -->
<bean id="entityManagerFactoryBean" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="packagesToScan" value="com.ytfs.entity"/>
<property name="persistenceProvider">
<bean class="org.hibernate.jpa.HibernatePersistenceProvider"/>
</property>
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="true"/>
<property name="database" value="MYSQL"/>
<property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>
<property name="generateDdl" value="false"/>
</bean>
</property>
</bean>
<!-- 配置事务管理器 -->
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 整合jpa -->
<jpa:repositories base-package="com.ytfs.dao" transaction-manager-ref="transactionManager"
entity-manager-factory-ref="entityManagerFactoryBean"/>
<!-- spring的包扫描 -->
<context:component-scan base-package="com.ytfs"/>
<!-- 整合数据源 -->
<context:property-placeholder location="classpath:jdbcConfig.properties"/>
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
<property name="url" value="${jdbc.url}"/>
</bean>
</beans>
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jpa
jdbc.username=root
jdbc.password=root
实体类
package com.ytfs.entity;
import javax.persistence.*;
import java.io.Serializable;
/**
* @Classname Customer
* @Description TODO(客户的实体类)
* @Date 2020/5/6 16:15
* @Created by ytfs
*/
@Entity
@Table(name = "cst_customer")
public class Customer implements Serializable {
/**
* cust_id` bigint(32) NOT NULL AUTO_INCREMENT COMMENT '客户编号(主键)',
* `cust_name` varchar(32) NOT NULL COMMENT '客户名称(公司名称)',
* `cust_source` varchar(32) DEFAULT NULL COMMENT '客户信息来源',
* `cust_industry` varchar(32) DEFAULT NULL COMMENT '客户所属行业',
* `cust_level` varchar(32) DEFAULT NULL COMMENT '客户级别',
* `cust_address` varchar(128) DEFAULT NULL COMMENT '客户联系地址',
* `cust_phone` v
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "cust_id")
private Long custId;
@Column(name = "cust_name")
private String custName;
@Column(name = "cust_source")
private String custSource;
@Column(name = "cust_industry")
private String custIndustry;
@Column(name = "cust_level")
private String custLevel;
@Column(name = "cust_address")
private String custAddress;
@Column(name = "cust_phone")
private String custPhone;
public Long getCustId() {
return custId;
}
public void setCustId(Long custId) {
this.custId = custId;
}
public String getCustName() {
return custName;
}
public void setCustName(String custName) {
this.custName = custName;
}
public String getCustSource() {
return custSource;
}
public void setCustSource(String custSource) {
this.custSource = custSource;
}
public String getCustIndustry() {
return custIndustry;
}
public void setCustIndustry(String custIndustry) {
this.custIndustry = custIndustry;
}
public String getCustLevel() {
return custLevel;
}
public void setCustLevel(String custLevel) {
this.custLevel = custLevel;
}
public String getCustAddress() {
return custAddress;
}
public void setCustAddress(String custAddress) {
this.custAddress = custAddress;
}
public String getCustPhone() {
return custPhone;
}
public void setCustPhone(String custPhone) {
this.custPhone = custPhone;
}
@Override
public String toString() {
return "Customer{" +
"custId=" + custId +
", custName='" + custName + '\'' +
", custSource='" + custSource + '\'' +
", custIndustry='" + custIndustry + '\'' +
", custLevel='" + custLevel + '\'' +
", custAddress='" + custAddress + '\'' +
", custPhone='" + custPhone + '\'' +
'}';
}
}
Dao接口
package com.ytfs.dao;
import com.ytfs.entity.Customer;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
/**
* @Classname ICustomerDao
* @Description TODO(客户的数据访问层)
* @Date 2020/5/6 16:20
* @Created by ytfs
*/
public interface ICustomerDao extends JpaRepository<Customer,Long>, JpaSpecificationExecutor<Customer> {
}
查询测试
package com.ytfs.test;
import com.ytfs.dao.ICustomerDao;
import com.ytfs.entity.Customer;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
/**
* @Classname Test
* @Description TODO(测试)
* @Date 2020/5/6 16:21
* @Created by ytfs
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:applicationContext.xml")
public class TestSpringDataJPA {
@Autowired
private ICustomerDao customerDao;
/**
* 根据名称查询用户
*/
@Test
public void testFindAll() {
//匿名内部类
/**
* 自定义查询条件
* 1.实现Specification接口(提供泛型:查询的对象类型)
* 2.实现toPredicate方法(构造查询条件)
* 3.需要借助方法参数中的两个参数(
* root:获取需要查询的对象属性
* CriteriaBuilder:构造查询条件的,内部封装了很多的查询条件(模糊匹配,精准匹配)
* )
* 案例:根据客户名称查询,查询客户名为传智播客的客户
* 查询条件
* 1.查询方式
* criteriaBuilder对象
* 2.比较的属性名称
* root对象
*
*/
Specification<Customer> spec = new Specification<Customer>() {
@Override
public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
//1.获取比较的属性
Path<Object> custName = root.get("custName");
//2.构造查询条件 : select * from cst_customer where cust_name = '传智播客'
/**
* 第一个参数:需要比较的属性(path对象)
* 第二个参数:当前需要比较的取值
*/
Predicate predicate = criteriaBuilder.equal(custName, "雨听风说");//进行精准的匹配 (比较的属性,比较的属性的取值)
return predicate;
}
};
List<Customer> customers = this.customerDao.findAll(spec);
customers.forEach(System.out::println);
}
/**
* 多条件查询
*/
@Test
public void testFindOne() {
/**
* root:获取属性
* 客户名
* id
* criteriaBuilder:构造查询
* 1.构造客户名的精准匹配查询
* 2.构造所属行业的精准匹配查询
* 3.将以上两个查询联系起来
*/
Specification<Customer> spec = new Specification<Customer>() {
@Override
public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
Path<Object> path = root.get("custName");
Path<Object> path1 = root.get("custId");
/*
构造查询
*/
//1.构造客户名的精准匹配查询
Predicate predicate = criteriaBuilder.equal(path, "雨听风说");//第一个参数,path(属性),第二个参数,属性的取值
//2..构造id的精准匹配查询
Predicate equal = criteriaBuilder.equal(path1, 1L);
//3.将多个查询条件组合到一起:组合(满足条件一并且满足条件二:与关系,满足条件一或满足条件二即可:或关系)
// criteriaBuilder.or();//以或的形式拼接多个查询条件
Predicate predicate1 = criteriaBuilder.and(predicate, equal);//以与的形式将条件拼接在一起
return predicate1;
}
};
Optional<Customer> customers = this.customerDao.findOne(spec);
customers.stream().forEach(c -> {
System.out.println("c = " + c);
});
}
/**
* 案例:完成根据客户名称的模糊匹配,返回客户列表
* 客户名称包含 ’听风‘
*
* equal :直接的到path对象(属性),然后进行比较即可
* gt,lt,ge,le,like : 得到path对象,根据path指定比较的参数类型,再去进行比较
* 指定参数类型:path.as(类型的字节码对象)
*/
@Test
public void testLike() {
Specification<Customer> spec = new Specification<Customer>() {
@Override
public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
Path<Object> custName = root.get("custName");
Predicate predicate = criteriaBuilder.like(custName.as(String.class), "%雨听%");
return predicate;
}
};
List<Customer> customers = this.customerDao.findAll(spec);
customers.forEach(System.out::println);
}
/**
* 查询并排序
*/
@Test
public void testLikeAndSort() {
Specification<Customer> spec = new Specification<Customer>() {
@Override
public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
Path<Object> custName = root.get("custName");
Predicate predicate = criteriaBuilder.like(custName.as(String.class), "%雨听%");
return predicate;
}
};
//添加排序
//创建排序对象,需要调用构造方法实例化sort对象
//第一个参数:排序的顺序(倒序,正序)
// Sort.by("custId").descending():倒序
// Sort.by("custId").ascending(); : 升序
//第二个参数:排序的属性名称
Sort sort = Sort.by("custId").descending();
List<Customer> customers = this.customerDao.findAll(spec,sort);
customers.forEach(System.out::println);
}
/**
* 分页查询
* Specification: 查询条件
* Pageable:分页参数
* 分页参数:查询的页码,每页查询的条数
* findAll(Specification,Pageable):带有条件的分页
* findAll(Pageable):没有条件的分页
* 返回:Page(springDataJpa为我们封装好的pageBean对象,数据列表,共条数)
*/
@Test
public void testPage() {
//PageRequest对象是Pageable接口的实现类
/**
* 创建PageRequest的过程中,需要调用他的构造方法传入两个参数
* 第一个参数:当前查询的页数(从0开始)
* 第二个参数:每页查询的数量
*/
Pageable pageable = PageRequest.of(0,2);
Page<Customer> page = this.customerDao.findAll(pageable);
System.out.println(page.getContent()); //得到数据集合列表
System.out.println(page.getTotalElements());//得到总条数
System.out.println(page.getTotalPages());//得到总页数
}
}