背景
新增一个模块我们就要写一大堆CRUD代码,一般新增更新我们需要自己写(牵扯其它模块调用,业务逻辑),但是查询我们可以利用jpa来实现一个baseController,跟前端约定好查询方式,省去各种字段的查询加快项目进度。主要参考《Spring Data JPA从入门到精通》这本书,百度可以搜到pdf。ide是InteliJava 2020。
先把git地址贴出来:https://github.com/fantasticqiang/jpatest.git
注意事项
- 代码下载下来之后,在applicaton.yml文件中更改数据库地址,数据库名称。
- 项目启动之后会自动建一张user表(确保你连接的库中没有user表),表中 会自动插入测试数据
- 修改mysql的用户名,密码
有了baseController之后,我们的UserController什么都不用写,就可以CRUD了
先展示一张查询图片:
查询条件说明:
filter | 表示查询的字段筛选条件
and查询中间用逗号(,)隔开。 or查询用分号(;)隔开。 in查询多个值用单引号(')隔开 | ||
page | 0_5, 0表示查询的页码,0表示第一页,5表示每一页5条数据 | ||
sort | -createTime, - 表示降序排序, createTime表示降序排序的字段 |
我们只需要新建一个UserController继承BaseController,对于User表的CRUD就都已经做好了。到底是怎么实现的呢? 先熟悉一下以下知识点(简单的介绍一下)。
关键知识点
自定义标签
//声明自定义标签用在参数上
@Target({ElementType.PARAMETER})
//声明标签保留到vm运行期
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface Filter {
/**
* 说明:@interface 自定义标签
* @return
*/
String value() default "filter";
}
自定义查询条件 MySpecification,根据url中的一个(k,v)生成一个查询条件,比如:username= 张三
MySpecification实现Specification接口,里面只定义了一个接口
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { }
这个接口的目的就是封装查询条件,就是 sql语句where后面的查询条件。下面表格介绍toPredicate中的三个参数
root | Root<T>root:代表了可以查询和操作的实体对象的根。如 果将实体对象比喻成表名,那root里面就是这张表里面的字段。这不 过是JPQL的实体字段而已。通过里面的Path<Y>get(String attributeName)来获得我们操作的字段。 |
criteriaQuery | CriteriaQuery<?>query:代表一个specific的顶层查询对 1. CriteriaQuery<T> select(Selection<? extends T> selection); 2.CriteriaQuery<T> where(Predicate... restrictions); 3.CriteriaQuery<T> groupBy(Expression<?>... grouping); 4.CriteriaQuery<T> having(Expression<Boolean> restriction); 5.CriteriaQuery<T> orderBy(Order... o); |
criteriaBuilder | CriteriaBuilder cb:用来构建CritiaQuery的构建器对 cb.like(root.get("fieldName"), value); |
下面贴下最关键的代码:
package org.example.parameter;
import org.example.common.Common;
import org.springframework.data.jpa.domain.Specification;
import javax.persistence.criteria.*;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
public class MySpecification<T> implements Specification<T> {
private ParameterCriteria parameterCriteria;
public MySpecification(String parameterCriteriaStr) {
this.parameterCriteria = new ParameterCriteria(parameterCriteriaStr);
}
/**
* 获取查询的字段名称
* @param root
* @param path
* @return
*/
private Expression get(Root<T> root, String path) {
String[] paths = path.split("\\.");
Path finalPath = root.get(paths[0]);
if (paths.length > 1) {
for (int i = 1; i < paths.length; i++) {
finalPath = finalPath.get(paths[i]);
}
}
return finalPath;
}
/**
* 封装一个查询条件
* @param root
* @param criteriaQuery
* @param criteriaBuilder
* @return
*/
@Override
public Predicate toPredicate(Root<T> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
Expression expression = get(root, parameterCriteria.getKey());
Class javaType = expression.getJavaType();
switch (parameterCriteria.getOperator()) {
case greaterEqualThan:
return criteriaBuilder.greaterThanOrEqualTo(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
case greaterThan:
return criteriaBuilder.greaterThan(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
case lessEqualThan:
return criteriaBuilder.lessThanOrEqualTo(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
case lessThan:
return criteriaBuilder.lessThan(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
case equal:
return criteriaBuilder.equal(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
case notEqual:
return criteriaBuilder.notEqual(expression, parameterCriteria.getValue(javaType, criteriaBuilder));
case in:
return expression.in(Arrays.asList(parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER)));
case notIn:
return criteriaBuilder.not(expression.in(Arrays.asList(parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER))));
case like:
return criteriaBuilder.like(expression,"%" + parameterCriteria.getStringValue() + "%");
case unlike:
return criteriaBuilder.notLike(expression,"%" + parameterCriteria.getStringValue() + "%");
case isnull:
return criteriaBuilder.isNull(expression);
case notnull:
return criteriaBuilder.isNotNull(expression);
case jspe:
String[] jspeParamValues = parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER);
String jspePath = jspeParamValues[0], jspeValue = jspeParamValues[1];
return criteriaBuilder.equal(criteriaBuilder.function("JSON_EXTRACT", String.class, root.get(parameterCriteria.getKey()), criteriaBuilder.literal(jspePath)),
jspeValue);
case jos:
String[] josParamValues = parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER);
String josPath = josParamValues[0], josValue = josParamValues[1];
return criteriaBuilder.isNotNull(criteriaBuilder.function("JSON_SEARCH", String.class, root.get(parameterCriteria.getKey()),
criteriaBuilder.literal("one"),
criteriaBuilder.literal(josValue),
criteriaBuilder.nullLiteral(String.class),
criteriaBuilder.literal(josPath)));
case jas:
String[] jasParamValues = parameterCriteria.getStringValue().split(Common.FILTER_SPECIFICATION_VALUE_SPLITTER);
String jasPath = jasParamValues[0], jasValue = jasParamValues[1];
return criteriaBuilder.isNotNull(criteriaBuilder.function("JSON_SEARCH", String.class, root.get(parameterCriteria.getKey()),
criteriaBuilder.literal("all"),
criteriaBuilder.literal(jasValue),
criteriaBuilder.nullLiteral(String.class),
criteriaBuilder.literal(jasPath)));
}
return null;
}
/**
* 一个标准查询查询参数,url中的查询条件解析成对象
* 比如:username=张三
*/
public static class ParameterCriteria {
private String key;
private EFieldOperator operator;
private String value;
public ParameterCriteria(String key, EFieldOperator operator, String value) {
this.key = key;
this.operator = operator;
this.value = value;
}
private ParameterCriteria(String parameterSpecification) {
String[] parts = parameterSpecification.split(Common.FILTER_OPERATION_SPLITTER);
this.key = parts[0];
this.operator = EFieldOperator.getByName(parts[1]);
String[] valueParts = new String[parts.length - 2];
System.arraycopy(parts, 2, valueParts, 0, valueParts.length);
this.value = parts.length > 2 ? String.join(Common.COMMON_SPLITTER, valueParts) : "";
}
public String getKey() {
return key;
}
public EFieldOperator getOperator() {
return operator;
}
public Expression getValue(Class clazz, CriteriaBuilder cb) {
if (clazz.isAssignableFrom(Date.class)) {
Date result = null;
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
result = sdf.parse(value);
} catch (Exception e) {
e.printStackTrace();
}
return cb.literal(result);
}
return cb.literal(value);
}
public String getStringValue() {
return value;
}
}
}
把多个specification合并成查询语句
关键代码:
package org.example.parameter;
import org.apache.commons.lang.StringUtils;
import org.example.common.Common;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import java.util.ArrayList;
/**
* 接收传过来的url参数,封装成specification查询条件
* @param <T>
*/
public class SimpleFilterResolver<T> {
private final Logger logger = LoggerFactory.getLogger(getClass());
private String filterString;
private String pageableString;
private String sortString;
public SimpleFilterResolver(String filterString, String pageableString, String sortString) {
this.filterString = filterString;
this.pageableString = pageableString;
this.sortString = sortString;
}
//生成查询语句
public <T> Specification<T> genSpecification() {
Specification<T> orSpec = null;
for (String orString : filterString.split(Common.FILTER_SPECIFICATION_OR_SPLITTER)) {
String[] split = orString.split(Common.FILTER_SPECIFICATION_AND_SPLITTER);
if (split.length >= 1) {
Specification<T> mySpec = null;
for (int i = 0; i < split.length; i++) {
if (mySpec == null) {
if (StringUtils.isNotBlank(split[i])) {
mySpec = new MySpecification<T>(split[i]);
}
} else {
if (StringUtils.isNotBlank(split[i])) {
mySpec = mySpec.and(new MySpecification<T>(split[i]));
}
}
}
if (mySpec != null) {
if (orSpec == null) {
orSpec = mySpec;
} else {
orSpec = orSpec.or(mySpec);
}
}
}
}
return orSpec;
}
//生成分页
public PageRequest genPageRequest() {
int start = 0;
int size = 10;
if (StringUtils.isNotBlank(pageableString)) {
String[] split = pageableString.split(Common.COMMON_SPLITTER);
if (split != null && split.length >= 2) {
try {
Integer startTmp = Integer.valueOf(split[0]);
Integer sizeTmp = Integer.valueOf(split[1]);
if (startTmp >= 0) {
start = startTmp;
}
if (sizeTmp >= 1) size = sizeTmp;
} catch (Exception e) {
logger.error("分页数据转换失败");
}
}
}
ArrayList<Sort.Order> orders = new ArrayList<>();
if (StringUtils.isNotBlank(sortString)) {
String[] split = sortString.split(Common.FILTER_SPECIFICATION_AND_SPLITTER);
for (String sortStr : split) {
String finalSortStr = null;
boolean asc = true;
if (sortStr.startsWith("-")) {
asc = false;
finalSortStr = sortStr.substring(1);
} else if (sortStr.startsWith("+")) {
finalSortStr = sortStr.substring(1);
} else {
finalSortStr = sortStr;
}
if (StringUtils.isNotBlank(finalSortStr)) {
orders.add(new Sort.Order(asc ? Sort.Direction.ASC : Sort.Direction.DESC, finalSortStr));
}
}
}
return PageRequest.of(start, size, Sort.by(orders));
}
}