在后台开发中，数据库中会建许多表，而基本上每个表需要写一个查询的接口，而且查询的条件各不相同，每个表都要一一去适配其查询参数，每次客户需要新的查询参数，都需要去适配一下，这就很麻烦了，我觉得有一个通用的接口能够查询所有的表是很必要的，省时省力。
在接口中，提供一个规范，让前端去传相应的查询对象与查询参数与查询方法（=、like、between等），进行数据库查询，以后如果有新的查询参数需要添加，压力直接给到前端，我就不需要做任何操作了。当然，也支持连表查询（所以我经常喷Mybatis Plus的编程式查询，表都不能连^^），不过需要@ManyToMany、@OneToMany、@ManyToOne、@OneToOne用的很好。不然一些查询还是要自己手动去写接口
比如，有2个表，person、human，其都有相同的name、age属性，human与person是多对一的关系，其human中存在着person的id
前端提供查询参数：{"name": "Human", "query": [{"field": "personEntity.name", "value": "xixi", "type": "like"}, {"field": "name", "value": "haha", "type": "eq"}]}Spring-Data-Jpa查询生成的sql
select h1_0.id,h1_0.age,h1_0.birth,h1_0.name,h1_0.person_id
from human h1_0 join person p1_0 on p1_0.id=h1_0.person_id
where p1_0.name like ? and h1_0.name=? offset ? rows fetch first ? rows only
就很舒服了
使用Specification来实现，我是很喜欢用QueryDSL的，但是IntelliJ IDEA的maven阉割了生命周期，对QueryDSL的支持不好
1、先编写查询参数的对象
class DynamicParam {
// entity的名字
var name: String = ""
var query: List<DynamicQuery> = mutableListOf()
var pageable: DynamicPageable = DynamicPageable()
}
class DynamicQuery {
var field: String = ""
var value: String = ""
var valueList: List<String> = mutableListOf()
// eq、like、between、startWith、endWith、gt、ge、lt、le、ne、greaterThan、lessThan、greaterThanOrEqualTo
var type: String = "eq"
}
class DynamicSort {
var field: String = ""
var order: String = "asc"
}
class DynamicPageable {
var page: Int = 0
var size: Int = 20
var sort: List<DynamicSort> = mutableListOf()
}
@Data
public class DynamicPageable {
private Integer page = 0;
private Integer size = 20;
private List<DynamicSort> sort = new ArrayList<>();
}
@Data
public class DynamicSort {
private String field = "";
private String order = "asc";
}
@Data
public class DynamicQuery {
private String field = "";
private String value = "";
private List<String> valueList = new ArrayList<>();
// eq、like、between、startWith、endWith、gt、ge、lt、le、ne、greaterThan、lessThan、greaterThanOrEqualTo
private String type = "eq";
}
@Data
public class DynamicParam {
private String name = "";
private List<DynamicQuery> query = new ArrayList<>();
private DynamicPageable pageable = new DynamicPageable();
}其参数即为
DynamicParam对象2、编写其Entity映射表
@Entity
@Table(name= "person")
class PersonEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Int? = null
var name: String = ""
var age: Int = 0
var birth: LocalDateTime = LocalDateTime.now()
}
@Entity
@Table(name= "human")
class HumanEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Int? = null
var name: String = ""
var age: Int = 0
var birth: LocalDateTime = LocalDateTime.now()
@ManyToOne
@JoinColumn(name = "person_id")
var personEntity: PersonEntity? = null
}
3、表对应的Repository
interface PersonRepository: JpaRepository<PersonEntity, Int>, JpaSpecificationExecutor<PersonEntity>
interface HumanRepository: JpaRepository<HumanEntity, Int>, JpaSpecificationExecutor<HumanEntity>4、controller层
@RestController
@RequestMapping("/test")
class TestController(
private val applicationContext: ApplicationContext
) {
// repository的前缀包名
private val packageName = "me.kuku.test"
@PostMapping
fun test(@RequestBody dynamicParam: DynamicParam): Any {
val sp = Specification<Any> { root, _, criteriaBuilder ->
val predicates = mutableListOf<Predicate>()
for (dynamicQuery in dynamicParam.query) {
val field = dynamicQuery.field
val fieldList = field.split(".")
val firstField = fieldList[0]
var path = root.get<Any>(firstField)
fieldList.stream().skip(1).forEach {
path = path.get(it)
}
val type = dynamicQuery.type
val value = dynamicQuery.value
val predicate = when (type) {
"eq" -> criteriaBuilder.equal(path, value)
"like" -> criteriaBuilder.like(path as Expression<String>, "%$value%")
"between" -> {
val valueList = dynamicQuery.valueList
if (valueList.size != 2) error("参数错误，类型为between，valueList数组必须是2个")
val first = valueList[0]
val second = valueList[1]
convert(criteriaBuilder, path, first, second)
}
"startWith" -> criteriaBuilder.like(path as Expression<String>, "%$value")
"endWith" -> criteriaBuilder.like(path as Expression<String>, "$value%")
"gt" -> criteriaBuilder.gt(path as Expression<out Number>, value.toInt())
"ge" -> criteriaBuilder.ge(path as Expression<out Number>, value.toInt())
"lt" -> criteriaBuilder.lt(path as Expression<out Number>, value.toInt())
"le" -> criteriaBuilder.le(path as Expression<out Number>, value.toInt())
"ne" -> criteriaBuilder.notEqual(path, value)
"greaterThan" -> criteriaBuilder.greaterThan(path as Expression<String>, value)
"lessThan" -> criteriaBuilder.lessThan(path as Expression<String>, value)
"greaterThanOrEqualTo" -> criteriaBuilder.greaterThanOrEqualTo(path as Expression<String>, value)
"lessThanOrEqualTo" -> criteriaBuilder.lessThanOrEqualTo(path as Expression<String>, value)
else -> continue
}
predicate?.let {
predicates.add(it)
}
}
criteriaBuilder.and(*predicates.toTypedArray())
}
val dynamicPageable = dynamicParam.pageable
val page = dynamicPageable.page
val size = dynamicPageable.size
val sortList = dynamicPageable.sort
val sortOrderList = mutableListOf<Sort.Order>()
for (dynamicSort in sortList) {
val order = dynamicSort.order
val field = dynamicSort.field
if (order.uppercase() == "asc") sortOrderList.add(Sort.Order.asc(field))
else if (order.uppercase() == "desc") sortOrderList.add(Sort.Order.desc(field))
}
val pageable = PageRequest.of(page, size, Sort.by(sortOrderList))
val name = dynamicParam.name
val prefix = name.substring(0, 1).uppercase() + name.substring(1)
val repositoryClass = Class.forName("$packageName.pojo.${prefix}Repository")
val repository = applicationContext.getBean(repositoryClass)
val method =
repositoryClass.getMethod("findAll", Specification::class.java, Pageable::class.java)
return method.invoke(repository, sp, pageable)
}
private fun convert(build: CriteriaBuilder, path: Path<*>, text1: String, text2: String): Predicate? {
return when (path.javaType) {
LocalDate::class.java -> {
build.between(path as Expression<LocalDate>,
DateTimeFormatterUtils.parseToLocalDate(text1, "yyyy-MM-dd"),
DateTimeFormatterUtils.parseToLocalDate(text2, "yyyy-MM-dd")
)
}
LocalDateTime::class.java -> {
build.between(path as Expression<LocalDateTime>,
DateTimeFormatterUtils.parseToLocalDateTime(text1, "yyyy-MM-dd HH:mm:ss"),
DateTimeFormatterUtils.parseToLocalDateTime(text2, "yyyy-MM-dd HH:mm:ss")
)
}
String::class.java -> {
build.between(path as Expression<String>, text1, text2)
}
else -> null
}
}
}@RestController
@RequestMapping("/api/general")
public class GeneralController {
String packageName = "me.kuku";
@PostMapping("query")
public Object query(@RequestBody DynamicParam dynamicParam) throws Exception {
Specification<Object> sp = (root, query, criteriaBuilder) -> {
List<javax.persistence.criteria.Predicate> predicates = new ArrayList<>();
for (DynamicQuery dynamicQuery : dynamicParam.getQuery()) {
String field = dynamicQuery.getField();
String[] fieldList = field.split("\\.");
String firstField = fieldList[0];
Path path = root.get(firstField);
for (String it : Arrays.stream(fieldList).skip(1).collect(Collectors.toList())) {
path = path.get(it);
}
String type = dynamicQuery.getType();
String value = dynamicQuery.getValue();
javax.persistence.criteria.Predicate predicate = null;
switch (type) {
case "eq": predicate = criteriaBuilder.equal(path, value); break;
case "like": predicate = criteriaBuilder.like(path, "%" + value + "%"); break;
case "between": {
List<String> valueList = dynamicQuery.getValueList();
if (valueList.size() != 2) throw new IllegalStateException("参数错误，类型为between，valueList数组必须是2个");
String first = valueList.get(0);
String second = valueList.get(1);
predicate = convert(criteriaBuilder, path, first, second); break;
}
case "startsWith": criteriaBuilder.like(path, "%" + value); break;
case "endsWith": criteriaBuilder.like(path, value + "%"); break;
case "gt": criteriaBuilder.gt(path, Integer.parseInt(value)); break;
case "ge": criteriaBuilder.ge(path, Integer.parseInt(value)); break;
case "lt": criteriaBuilder.lt(path, Integer.parseInt(value)); break;
case "le": criteriaBuilder.le(path, Integer.parseInt(value)); break;
case "ne": criteriaBuilder.notEqual(path, Integer.parseInt(value)); break;
case "greaterThan": criteriaBuilder.greaterThan(path, value); break;
case "lessThan": criteriaBuilder.lessThan(path, value); break;
case "greaterThanOrEqualTo": criteriaBuilder.greaterThanOrEqualTo(path, value); break;
case "lessThanOrEqualTo": criteriaBuilder.lessThanOrEqualTo(path, value); break;
default: continue;
}
predicates.add(predicate);
}
return criteriaBuilder.and(predicates.toArray(new javax.persistence.criteria.Predicate[0]));
};
DynamicPageable dynamicParamPageable = dynamicParam.getPageable();
Integer page = dynamicParamPageable.getPage();
Integer size = dynamicParamPageable.getSize();
List<DynamicSort> sortList = dynamicParamPageable.getSort();
List<Sort.Order> sortOrderList = new ArrayList<>();
for (DynamicSort dynamicSort : sortList) {
String order = dynamicSort.getOrder();
String field = dynamicSort.getField();
if ("asc".equals(order.toUpperCase())) sortOrderList.add(Sort.Order.asc(field));
else if ("desc".equals(order.toUpperCase())) sortOrderList.add(Sort.Order.desc(field));
}
PageRequest pageable = PageRequest.of(page, size, Sort.by(sortOrderList));
String name = dynamicParam.name;
String prefix = name.substring(0, 1).toUpperCase() + name.substring(1);
Class<?> repositoryClass = Class.forName(packageName + ".repository" + "." + prefix + "Repository");
Object repository = applicationContext.getBean(repositoryClass);
Method method = repositoryClass.getMethod("findAll", Specification.class, Pageable.class);
return method.invoke(repository, sp, pageable);
}
private javax.persistence.criteria.Predicate convert(CriteriaBuilder build, Path path, String text1, String text2) {
Class clazz = path.getJavaType();
if (clazz == LocalDate.class) {
return build.between(path, DateTimeFormatterUtils.parseToLocalDate(text1, "yyyy-MM-dd"),
DateTimeFormatterUtils.parseToLocalDate(text2, "yyyy-MM-dd"));
} else if (clazz == LocalDateTime.class) {
return build.between(path, DateTimeFormatterUtils.parseToLocalDateTime(text1, "yyyy-MM-dd HH:mm:ss"),
DateTimeFormatterUtils.parseToLocalDateTime(text2, "yyyy-MM-dd HH:mm:ss"));
} else return null;
}
}完成