在后台开发中，数据库中会建许多表，而基本上每个表需要写一个查询的接口，而且查询的条件各不相同，每个表都要一一去适配其查询参数，每次客户需要新的查询参数，都需要去适配一下，这就很麻烦了，我觉得有一个通用的接口能够查询所有的表是很必要的，省时省力。

在接口中，提供一个规范，让前端去传相应的查询对象与查询参数与查询方法（=、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()
    var method: String = "findAll"

    fun toPredicate(): Specification<Any> {
        return Specification<Any> { root, _, criteriaBuilder ->
            val predicates = mutableListOf<Predicate>()
            for (dynamicQuery in 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 convertValue = convertValue(path, value)
                val predicate = when (type) {
                    "eq" -> criteriaBuilder.equal(path, convertValue)
                    "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())
        }
    }

    private fun convertValue(path: Path<*>, value: String): Any {
        val javaType = path.javaType
        return if (javaType.isEnum) {
            val method = javaType.getDeclaredMethod("valueOf", String::class.java)
            method.isAccessible = true
            method.invoke(null, value)
        }
        else value
    }

    fun toSort(): Sort {
        val sortList = pageable.sort
        val sortOrderList = mutableListOf<Sort.Order>()
        for (dynamicSort in sortList) {
            val order = dynamicSort.order
            val field = dynamicSort.field
            if (order.lowercase() == "asc") sortOrderList.add(Sort.Order.asc(field))
            else if (order.lowercase() == "desc") sortOrderList.add(Sort.Order.desc(field))
        }
        return Sort.by(sortOrderList)
    }

}

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()
}

其参数即为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
class DynamicJpaSpecificationController(
    private val applicationContext: ApplicationContext
) {

    @PostMapping("/dynamic/query/specification")
    fun test(@RequestBody dynamicParam: DynamicParam): Any {
        val sp = dynamicParam.toPredicate()
        val dynamicPageable = dynamicParam.pageable
        val page = dynamicPageable.page
        val size = dynamicPageable.size
        val pageable = PageRequest.of(page, size, dynamicParam.toSort())
        val name = dynamicParam.name
        val prefix = name.substring(0, 1).uppercase() + name.substring(1)
        val repositoryClass = repositoryClass(prefix)
        val repository = applicationContext.getBean(repositoryClass)
        val method =
            repositoryClass.getMethod("findAll", Specification::class.java, Pageable::class.java)
        return method.invoke(repository, sp, pageable)
    }

}

private val repositoryPackageNames = listOf("com.fnl.system.entity.master")

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
    }
}


fun repositoryClass(prefix: String): Class<*> {
    for (repositoryPackageName in repositoryPackageNames) {
        runCatching {
            return Class.forName("$repositoryPackageName.${prefix}Repository")
        }
    }
    error("找不到repository")
}

完成