Spring Data -Specification用法
入门例子
controller层，这里是个简单查询获取所有用户并分页
@GetMapping("/list")
@ApiOperation(value = "所有用户列表")
public Result list(@RequestParam Map<String, Object> params) {
Page page = userService.queryPage(params);
return Result.ok().put("page", page);
}
接下对service层的实现,功能是实现关键字搜索，这里因为简单并没有单独将Specification提出来，主要是对Specification接口有个大概的认识。
@Override
public page queryPage(Map<String, Object> params) {
//MapUtils方法用来取除params中的方法，来自于 org.apache.commons.collections.MapUtils;
String keyword = MapUtils.getSrting(params,"keyword");
Page page = sysUserRepository.findAll(new Specification<SysUserEntity>() {
@Override
public Predicate toPredicate(Root<SysUserEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
List<Predicate> predicates = new ArrayList<>();
if (StringUtils.isNotBlank(keyword)) {
List<Predicate> temp = new ArrayList<>();
for (String oneKeyword : keywordCopyStr) {
temp.add(criteriaBuilder.like(root.<String>get("mobile"), "%" + oneKeyword + "%"));
temp.add(criteriaBuilder.like(root.<String>get("trueName"), "%" + oneKeyword + "%"));
}
predicates.add(criteriaBuilder.or(temp.toArray(new Predicate[temp.size()])));
}
return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
}
});
return page;
}
Repository层中为了支持这样的查询，sysUserRepository需要继承JpaRepository（基本查询），JpaSpecificationExecutor（分页），这个接口是不需要再去实现的，到了Repository层就行，再对此进行扩充（比Mybatis简单多了）。
public interface sysUserRepository extends JpaRepository<SysCaptchaEntity, String>, JpaSpecificationExecutor<SysCaptchaEntity> {
}
Repository层常用写法
按着类字段查询 按着类关系关联查询不需要写语句的查询就不在详细讲解，给个例子看看就行
//查询第一个
EngineerVersionControl findTopByProjectIdAndOldOrderByVersionDesc(String projectId, int old);
//查询存在
boolean existsByProjectId(String projectId);
//排序
List<EngineerVersionControl> findByProjectIdOrderByVersionDesc(String projectId);
@Query语句查询
一般不会做物理删除，而是逻辑删除。保存操作使用save或者saveAll方法
//更新 必有@Modifying，和使用hiberna一样，HQL语句的写法
@Transactional(rollbackFor = Exception.class)
@Modifying
@Query("update SysUserEntity u set u.isDelete = ?2 ,u.gmtModified= ?3 where u.id = ?1 ")
int deleteIsUpdate(String id, int deleted, Date date);//查询
@Query("select u from SysUserEntity u where u.id in ?1 and u.isDelete = 0 ")
Page<SysUserEntity> findAllUser(List<String> userIds, Pageable pageable);//多表查询
@Query(value = " select p from DictionaryEntity p , DictionaryContentEntity w " +
" Where w.ContentEntity.id = ?1 and p.id = w.DictionaryEntity.id and p.deleted = ?2 ORDER BY p.dictionary")
List<WebsiteDictionaryEntity> webOnwDictionary(String id,int isDeleted);
3.使用@Query实现写sql语句的查询
再spring data 中不仅有HQl语句，在功能太复杂的时候，可以使用sql语句进行本地查询
@Query(value="select serve.* from service_serve serve " +
"left join company_info_user cominfo on serve.company_info_user_id=cominfo.company_info_user_id" +
" left join user_company company on cominfo.company_info_user_id=company.company_info_user_id " +
" left join employee employeeen3_ on company.user_company_id=employeeen3_.user_company_id " +
" left join user userentity4_ on employeeen3_.user_id=userentity4_.user_id " +
" where userentity4_.user_id=?1 and employeeen3_.activity_management_power=1" +
" order by serve.gmt_create desc " +
"limit ?2 , ?3 " ,nativeQuery = true)
List<ServiceServeEntity> queryByCompany(String userId,int startPoint,int endPoint);
4.@Param（value = “name”）查询，这是两种写法。一种?，一种@Param()
@Query(value="select activity.* from service_activity activity " +
"left join company_info_user cominfo on activity.company_id=cominfo.company_info_user_id" +
" left join user_company company on cominfo.company_info_user_id=company.company_info_user_id " +
" left join employee employeeen3_ on company.user_company_id=employeeen3_.user_company_id " +
" left join user userentity4_ on employeeen3_.user_id=userentity4_.user_id " +
" where userentity4_.user_id=:userId and employeeen3_.service_management_power=1 and activity.status=:status" +
" order by activity.gmt_create desc " +
"limit :startPoint , :endPoint " ,nativeQuery = true)
List<ServiceActivityEntity> queryByCompanyAndStatus(@Param(value="userId")String userId,@Param(value="status")int status, @Param(value="startPoint")int startPoint, @Param(value="endPoint")int endPoint);
Specification 的用法
下面是个较为全面的例子，将一个较为复杂的查询提取成一个方法。这个方法时使用and的方式拼接，接下来的每一个查询都需要使用把finalConditions拼上，如同 finalConditions = criteriaBuilder.and(finalConditions, taskFastPre)。
public class TaskProjectSpecs {
public static Specification<Task> where(Map params, String userId, List<String> taskIds) {
//lambda表达式
return (Root<Task> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) -> {
//开始
Predicate finalConditions = criteriaBuilder.conjunction();
//提取参数
String taskFast = MapUtils.getString(params, "taskFast");
//lile 和join 用法 join可跟,JoinType.LEFT等
if (StringUtils.isNotBlank(taskFast)) {
Predicate taskFastPre = criteriaBuilder.like(root.join("taskType",JoinType.LEFT).<String>get("id"), "%" + taskFast + "%");
finalConditions = criteriaBuilder.and(finalConditions, taskFastPre);
}
//between用法
if ((null != createBegin) && (null != createEnd)) {
Predicate datePredicate = null;
if (createBegin.after(createEnd)) {
datePredicate = criteriaBuilder.between(root.get("gmtCreate"), createEnd, createBegin);
} else {
datePredicate = criteriaBuilder.between(root.get("gmtCreate"), createBegin, createEnd);
}
finalConditions = criteriaBuilder.and(finalConditions, datePredicate);
}
//equale
if (null != emergency && 0 != emergency) {
finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.equal(root.get("emergencyLevel"), emergency));
}
//大于 不等于
if (status != null) {
finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.greaterThan(root.get("startDate"), new Date()));
finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.notEqual(root.get("status"), 1));
}
// or
if (StringUtils.isNotBlank(keyword)) {
finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.or(
criteriaBuilder.like(root.get("taskName"), "%" + keyword + "%"),
criteriaBuilder.like(root.join("project").get("name"), "%" + keyword + "%"))
);
}
//in
if (taskIds.size() > 0) {
CriteriaBuilder.In<Object> in = criteriaBuilder.in(root.get("id"));
for (String id : taskIds) {
in.value(id);
}
finalConditions = criteriaBuilder.and(finalConditions, in);
}
return query.where(finalConditions).getRestriction();
};
}
}
上面的方法是and凭借，还有一种add的方法，本质一样，都是构建query.where（）查询。
public class UserSpecs {
public static Specification<SysUserEntity> where(String keyword, Date createdAtBegin, Date createdAtEnd, List<String> userIds) {
return (Root<SysUserEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
List<Predicate> predicates = new ArrayList<>();
if (StringUtils.isNotBlank(keyword)) {
List<Predicate> temp = new ArrayList<>();
Set<String> keywordCopyStr = StringUtil.cutToArray(keyword);
for (String oneKeyword : keywordCopyStr) {
temp.add(cb.like(root.<String>get("mobile"), "%" + oneKeyword + "%"));
temp.add(cb.like(root.<String>get("trueName"), "%" + oneKeyword + "%"));
}
predicates.add(cb.or(temp.toArray(new Predicate[temp.size()])));
}
//未删除
predicates.add(cb.equal(root.get("isDelete"), Constant.NOT_DELETED));
query.where(predicates.toArray(new Predicate[predicates.size()]));
return query.getRestriction();
};
}
}
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();@Entity
@Table(name = "db1_News_Files", schema = "dbo")
@IdClass(Db1NewsFilesEntityPK.class)
public class Db1NewsFilesEntity {
private String appId;
private String fileId;
/....省略.../
private Db1NewsItemsEntity newsItem;
@Id
@Column(name = "appid", nullable = false, length = 50)
public String getAppId() {
return appId;
}
public void setAppId(String appId) {
this.appId = appId;
}
@Id
@Column(name = "fileId", nullable = false, length = 50)
public String getFileId() {
return fileId;
}
public void setFileId(String fileId) {
this.fileId = fileId;
}
/....省略.../
@ManyToOne()
@JoinTable(name="db1_News_Items",
inverseJoinColumns = {
@JoinColumn(name = "AppId",referencedColumnName="appId",insertable = false,updatable = false)
,@JoinColumn(name = "ItemId",referencedColumnName="itemId",insertable = false,updatable = false)
})
public Db1NewsItemsEntity getNewsItem() {
return newsItem;
}
public void setNewsItem(Db1NewsItemsEntity newsItem) {
this.newsItem = newsItem;
}
@Entity
@Table(name = "db1_News_Items", schema = "dbo")
@IdClass(Db1NewsItemsEntityPK.class)
public class Db1NewsItemsEntity {
private String appId;
private String itemId;
/....省略...../
private List<Db1NewsFoldersEntity> folders;
@Id
@Column(name = "appid", nullable = false, length = 50)
public String getAppId() {
return appId;
}
public void setAppId(String appId) {
this.appId = appId;
}
@Id
@Column(name = "itemid", nullable = false, length = 50)
public String getItemId() {
return itemId;
}
public void setItemId(String itemId) {
this.itemId = itemId;
}
/....省略...../
@OneToMany()
@JoinTable(name="db1_News_Folders",
inverseJoinColumns = {
@JoinColumn(name = "AppId", referencedColumnName = "appId", insertable = false, updatable = false),
@JoinColumn(name = "FolderId", referencedColumnName = "FolderId", insertable = false, updatable = false)
})
public List<Db1NewsFoldersEntity> getFolders() {
return folders;
}
public void setFolders(List<Db1NewsFoldersEntity> folders) {
this.folders = folders;
}
}
private Specification<Db1NewsFilesEntity> countByAppidAndKwAndSearchId(String appId, String wk , String searchId){
return new Specification<Db1NewsFilesEntity>() {
@Override
public Predicate toPredicate(Root<Db1NewsFilesEntity> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
List<Predicate> list = new ArrayList<>();
Join<Db1NewsFilesEntity, Db1NewsItemsEntity> join = root.join("newsItem", JoinType.INNER);
list.add(criteriaBuilder.equal(root.get("appId"),appId));
list.add(criteriaBuilder.equal(root.get("img"),true));
list.add(criteriaBuilder.equal(join.get("appId"),appId));
list.add(criteriaBuilder.equal(join.get("isOk"),2));
list.add(criteriaBuilder.isNotNull(join.get("topImgId")));
if(!StringUtils.isEmpty(searchId)){
list.add(criteriaBuilder.equal(join.get("searchId"),searchId));
}
if(!StringUtils.isEmpty(wk)){
list.add(criteriaBuilder.like(join.get("ItemTitle"),"%"+wk+"%"));
}
Predicate[] p = new Predicate[list.size()];
query.where(criteriaBuilder.and(list.toArray(p)));
return query.getRestriction();
}
};
}封装Specification查询条件，在Spring Data JPA 2.0以前使用 Specifications 这个辅助类来操作where、not、and和or连接，在2.0版本以后这个类会被剔除，可以直接使用 Specification 自身对象来操作where多条件连接。（以下展示单表多条件查询）
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.domain.Specifications;
import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
/**
* SQL拼接工具类
*
* @author yanhu
* @date 2018/8/9
*/
public class SpecificationFactory {
private Specifications specs;
private SpecificationFactory(Specification specs) {
this.specs = Specifications.where(specs);
}
public static SpecificationFactory wheres(Specification spec) {
return new SpecificationFactory(spec);
}
public SpecificationFactory and(Specification other) {
this.specs.and(other);
return this;
}
public SpecificationFactory or(Specification other) {
this.specs.or(other);
return this;
}
public Specifications build() {
return this.specs;
}
/**
* 单where条件
*
* @param p
* @return
*/
public static Specification where(Predication p) {
List<Predication> ps = new ArrayList<>();
ps.add(p);
return where(ps);
}
/**
* 多where条件and连接
*
* @param ps
* @param <T>
* @return
*/
public static <T> Specification<T> where(List<Predication> ps) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) ->
builder.and(getPredicateList(root, builder, ps));
}
/**
* 多where条件or连接
*
* @param ps
* @param <T>
* @return
*/
public static <T> Specification<T> or(List<Predication> ps) {
return (Root<T> root, CriteriaQuery<?> query, CriteriaBuilder builder) ->
builder.or(getPredicateList(root, builder, ps));
}
/**
* 获取查询条件数组
*
* @param root
* @param builder
* @param ps
* @return
*/
private static Predicate[] getPredicateList(Root<?> root, CriteriaBuilder builder, List<Predication> ps) {
List<Predicate> predicateList = new ArrayList<>();
ps.forEach(p -> {
Predicate predicate = buildPredicate(builder, root.get(p.getName()), p);
predicateList.add(predicate);
});
return predicateList.toArray(new Predicate[predicateList.size()]);
}
/**
* 选取查询方式
*
* @param cb
* @param path
* @param p
* @return
*/
private static Predicate buildPredicate(CriteriaBuilder cb, Path path, Predication p) {
Predicate predicate;
switch (p.getOperator()) {
case LIKE:
predicate = cb.like(path, p.getValue().toString());
break;
case EQ:
predicate = cb.equal(path, p.getValue());
break;
case NOTEQ:
predicate = cb.notEqual(path, p.getValue());
break;
case GT:
predicate = cb.greaterThan(path, (Comparable) p.getValue());
break;
case GTEQ:
predicate = cb.greaterThanOrEqualTo(path, (Comparable) p.getValue());
break;
case LT:
predicate = cb.lessThan(path, (Comparable) p.getValue());
break;
case LTEQ:
predicate = cb.lessThanOrEqualTo(path, (Comparable) p.getValue());
break;
case NULL:
predicate = cb.isNull(path);
break;
case NOTNULL:
predicate = cb.isNotNull(path);
break;
case IN:
predicate = getIn(path, p.getValue());
break;
case NOTIN:
predicate = getIn(path, p.getValue()).not();
break;
default:
throw new IllegalArgumentException("非法的操作符");
}
return predicate;
}
/**
* 创建in操作
*
* @param path
* @param value
* @param <T>
* @return
*/
private static <T> Predicate getIn(Path path, T value) {
if (value instanceof Object[]) {
return path.in((Object[]) value);
} else if (value instanceof Collection) {
return path.in((Collection) value);
} else {
throw new IllegalArgumentException("非法的IN操作");
}
}
/***********************************************单where条件查询********************************************************/
// like
public static Specification like(String name, String value) {
return (root, query, cb) ->
cb.like(root.get(name), value);
}
// =
public static Specification equal(String name, Object value) {
return (root, query, cb) ->
cb.equal(root.get(name), value);
}
// !=
public static Specification notEqual(String name, Object value) {
return (root, query, cb) ->
cb.notEqual(root.get(name), value);
}
// >
public static Specification gt(String name, Object value) {
return (root, query, cb) ->
cb.greaterThan(root.get(name), (Comparable) value);
}
// >=
public static Specification gtEqual(String name, Object value) {
return (root, query, cb) ->
cb.greaterThanOrEqualTo(root.get(name), (Comparable) value);
}
// <
public static Specification lt(String name, Object value) {
return (root, query, cb) ->
cb.lessThan(root.get(name), (Comparable) value);
}
// <=
public static Specification ltEqual(String name, Object value) {
return (root, query, cb) ->
cb.lessThanOrEqualTo(root.get(name), (Comparable) value);
}
// is null
public static Specification isNull(String name) {
return (root, query, cb) ->
cb.isNull(root.get(name));
}
// is not null
public static Specification notNull(String name) {
return (root, query, cb) ->
cb.isNotNull(root.get(name));
}
// in
public static Specification in(String name, Object value) {
return (root, query, cb) ->
root.get(name).in(value);
}
// not in
public static Specification notIn(String name, Object value) {
return (root, query, cb) ->
root.get(name).in(value).not();
}
}
import lombok.Data;
@Data
public class Predication<T> {
private OP operator;
private String name;
private T value;
private Predication() {
}
public static <T> Predication<T> get(OP operator, String name, T value) {
return new Builder().operator(operator)
.name(name).value(value).build();
}
public static class Builder<T> {
private Predication p;
public Builder() {
this.p = new Predication();
}
public Builder operator(OP op) {
this.p.operator = op;
return this;
}
public Builder name(String name) {
this.p.name = name;
return this;
}
public Builder value(T value) {
this.p.value = value;
return this;
}
public <T> Predication<T> build() {
return this.p;
}
}
}public enum OP {
// like
LIKE,
// =
EQ,
// !=
NOTEQ,
// >
GT,
// >=
GTEQ,
// <
LT,
// <=
LTEQ,
// is null
NULL,
// is not null
NOTNULL,
// in
IN,
// not in
NOTIN,
AND,
OR,
NOT
}具体使用
Sort sort = new Sort(Sort.Direction.DESC, "id");
Pageable pageable = new PageRequest(number, size, sort);
Specification spec;
/***********************单条件查询*************************/
// 方式1
Predication p = Predication.get(OP.EQ, "name", name);
spec = SpecificationFactory.where(p);
// 方式2
spec = SpecificationFactory.equal("name", name);
/***********************多条件查询*************************/
List<Predication> ps = new ArrayList<>();
ps.add(Predication.get(OP.LIKE, "name", name));
ps.add(Predication.get(OP.EQ, "age", age));
// 全and连接
spec = SpecificationFactory.where(ps);
// 全or连接
spec = SpecificationFactory.or(ps);
// and和or混合连接
// where name like ?1 and age = ?2
// and name like ?3 and age = ?4
// or name like ?5 or age = ?6
// 工具类实现
spec = SpecificationFactory.wheres(SpecificationFactory.where(ps))
.and(SpecificationFactory.where(ps))
.or(SpecificationFactory.or(ps))
.build();
// JPA API辅助类实现
spec = Specifications.where(SpecificationFactory.where(ps))
.and(SpecificationFactory.where(ps))
.or(SpecificationFactory.where(ps));
// where name like ?1 and age = ?2
// and ( name like ?3 or age = ?4 )
// 工具类实现
spec = SpecificationFactory.wheres(SpecificationFactory.where(ps))
.and(SpecificationFactory.or(ps))
.build();
// JPA API辅助类实现
spec = Specifications.where(SpecificationFactory.where(ps))
.and(SpecificationFactory.or(ps));
Page<ConsultChat> chatPage = consultChatDao.findAll(spec, pageable);Example快速实现动态查询
Example官方介绍
Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require to write queries containing field names. In fact, Query by Example does not require to write queries using store-specific query languages at all.
谷歌翻译：
按例查询（QBE）是一种用户界面友好的查询技术。 它允许动态创建查询，并且不需要编写包含字段名称的查询。 实际上，按示例查询不需要使用特定的数据库的查询语言来编写查询语句。
Example api的组成
Probe: 含有对应字段的实例对象。
ExampleMatcher：ExampleMatcher携带有关如何匹配特定字段的详细信息，相当于匹配条件。
Example：由Probe和ExampleMatcher组成，用于查询。
限制
属性不支持嵌套或者分组约束，比如这样的查询 firstname = ?0 or (firstname = ?1 and lastname = ?2)
灵活匹配只支持字符串类型，其他类型只支持精确匹配
Limitations
1. No support for nested/grouped property constraints like firstname = ?0 or (firstname = ?1 and lastname = ?2)
2. Only supports starts/contains/ends/regex matching for strings and exact matching for other property types
使用
创建实体映射：
@Entity
@Table(name="t_user")
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
@Column(name="username")
private String username;
@Column(name="password")
private String password;
@Column(name="email")
private String email;
@Column(name="phone")
private String phone;
@Column(name="address")
private String address;
}
测试查询：
@Test
public void contextLoads() {
User user = new User();
user.setUsername("admin");
Example<User> example = Example.of(user);
List<User> list = userRepository.findAll(example);
System.out.println(list);
}打印的sql语句如下：
Hibernate:
select
user0_.id as id1_0_,
user0_.address as address2_0_,
user0_.email as email3_0_,
user0_.password as password4_0_,
user0_.phone as phone5_0_,
user0_.username as username6_0_
from
t_user user0_
where
user0_.username=?
可以发现，试用Example查询，默认情况下会忽略空值，官方文档也有说明：
This is a simple domain object. You can use it to create an Example. By default, fields having null values are ignored, and strings are matched using the store specific defaults. Examples can be built by either using the of factory method or by using ExampleMatcher. Example is immutable.
在上面的测试之中，我们只是只是定义了Probe而没有ExampleMatcher，是因为默认会不传时会使用默认的匹配器。点进方法可以看到下面的代码：
static <T> Example<T> of(T probe) {
return new TypedExample(probe, ExampleMatcher.matching());
}
static ExampleMatcher matching() {
return matchingAll();
}
static ExampleMatcher matchingAll() {
return (new TypedExampleMatcher()).withMode(ExampleMatcher.MatchMode.ALL);
}
自定匹配器规则
@Test
public void contextLoads() {
User user = new User();
user.setUsername("y");
user.setAddress("sh");
user.setPassword("admin");
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("username", ExampleMatcher.GenericPropertyMatchers.startsWith())//模糊查询匹配开头，即{username}%
.withMatcher("address" ,ExampleMatcher.GenericPropertyMatchers.contains())//全部模糊查询，即%{address}%
.withIgnorePaths("password");//忽略字段，即不管password是什么值都不加入查询条件
Example<User> example = Example.of(user ,matcher);
List<User> list = userRepository.findAll(example);
System.out.println(list);
}打印的sql语句如下：
select
user0_.id as id1_0_,
user0_.address as address2_0_,
user0_.email as email3_0_,
user0_.password as password4_0_,
user0_.phone as phone5_0_,
user0_.username as username6_0_
from
t_user user0_
where
(
user0_.username like ?
)
and (
user0_.address like ?
)参数如下：
2018-03-24 13:26:57.425 TRACE 5880 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [y%]
2018-03-24 13:26:57.425 TRACE 5880 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [%sh%]
补充
官方创建ExampleMatcher例子（1.8 lambda）
ExampleMatcher matcher = ExampleMatcher.matching()
.withMatcher("firstname", match -> match.endsWith())
.withMatcher("firstname", match -> match.startsWith());
}
| Matching | 生成的语句 | 说明 |
|---|---|---|
| DEFAULT (case-sensitive) | firstname = ?0 | 默认（大小写敏感） |
| DEFAULT (case-insensitive) | LOWER(firstname) = LOWER(?0) | 默认（忽略大小写） |
| EXACT (case-sensitive) | firstname = ?0 | 精确匹配（大小写敏感） |
| EXACT (case-insensitive) | LOWER(firstname) = LOWER(?0) | 精确匹配（忽略大小写） |
| STARTING (case-sensitive) | firstname like ?0 + ‘%’ | 前缀匹配（大小写敏感） |
| STARTING (case-insensitive) | LOWER(firstname) like LOWER(?0) + ‘%’ | 前缀匹配（忽略大小写） |
| ENDING (case-sensitive) | firstname like ‘%’ + ?0 | 后缀匹配（大小写敏感） |
| ENDING (case-insensitive) | LOWER(firstname) like ‘%’ + LOWER(?0) | 后缀匹配（忽略大小写） |
| CONTAINING (case-sensitive) | firstname like ‘%’ + ?0 + ‘%’ | 模糊查询（大小写敏感） |
| CONTAINING (case-insensitive) | LOWER(firstname) like ‘%’ + LOWER(?0) + ‘%’ | 模糊查询（忽略大小写） |
说明：
1. 在默认情况下（没有调用withIgnoreCase()）都是大小写敏感的。
2. api之中还有个regex，但是我在mysql下测试报错，不了解具体作用。
总结
通过在使用springdata jpa时可以通过Example来快速的实现动态查询，同时配合Pageable可以实现快速的分页查询功能。
对于非字符串属性的只能精确匹配，比如想查询在某个时间段内注册的用户信息，就不能通过Example来查询
扫一扫
2498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
