前面使用spring-data-jpa的通用查询接口还是有很多的局限性，比如没有通过@OneToOne等注解关联就不能使用了，如果没有使用注解关联的话，spring-data-jpa也没有很好用的编程式查询，不过querydsl提供的编程式查询就很方便。所以可以使用querydsl来编写一个通用的查询接口
QueryDSL官网：http://querydsl.com/
效果
前端传参
{
"select": ["ByLazyEntity.id"],
"from": {
"value": "LazyEntity",
"leftJoin": [
{
"value": "ByLazyEntity",
"on": [
{"left": "LazyEntity.byLazy.id", "right": "ByLazyEntity.id"}
]
},
{
"value": "NoEntity",
"on": [
{"left": "LazyEntity.fullName", "right": "NoEntity.fullName"}
]
}
]
},
"where": [
{
"field": "NoEntity.fullName",
"value": "xixi",
"type": "ne"
}
],
"orderBy": [
{
"value": "ByLazyEntity.id",
"type": "asc"
}
]
}生成的查询sql
Hibernate: select b1_0.id
from lazy l1_0 left join by_lazy b1_0 on l1_0.by_lazy_id=b1_0.id left join no n1_0 on l1_0.full_name=n1_0.full_name
where n1_0.full_name!=?
order by b1_0.id asc其中实体类：
@Entity
@Table(name = "lazy")
class LazyEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Int? = null
@Basic(fetch = FetchType.LAZY)
var name: String = ""
var fullName = ""
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "by_lazy_id")
var byLazy: ByLazyEntity? = null
}
@Entity
@Table(name = "by_lazy")
class ByLazyEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Int? = null
var fullName: String = ""
@OneToMany(mappedBy = "byLazy")
@CollectionType(type = KuKuSetType::class)
var lazy: Set<LazyEntity> = mutableSetOf()
}
@Entity
@Table(name = "no")
class NoEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
var id: Int? = null
var fullName: String = ""
}还是很舒服的
实现
构建JPAQueryFactory对象
Querydsl是使用JPAQueryFactory对象来进行查询的，所以我们需要构建这个对象，需要传入一个EntityManager
如果是Spring
@Component
class B {
@Bean
fun jpaQueryFactory(entityManager: EntityManager): JPAQueryFactory {
return JPAQueryFactory(entityManager)
}
}参数类
首先我们需要定义一个参数类，根据这个参数来查询
open class BaseSql {
var from: From = From()
var where: MutableList<Where> = mutableListOf()
var orderBy: MutableList<OrderBy> = mutableListOf()
fun from(jpaQuery: JPAQuery<*>) {
for (leftJoin in from.leftJoin) {
val leftJoinValue = leftJoin.value
jpaQuery.leftJoin(qEntity(leftJoinValue))
for (on in leftJoin.on) {
on.on(jpaQuery)
}
}
for (rightJoin in from.rightJoin) {
val rightJoinValue = rightJoin.value
jpaQuery.rightJoin(qEntity(rightJoinValue))
for (on in rightJoin.on) {
on.on(jpaQuery)
}
}
}
fun where(jpaQuery: JPAQuery<*>) {
for (where in where) {
val field = where.field
val path = convert(field)
if (where.value.toString().isNotEmpty()) {
// val method = path::class.java.getMethod(where.type, Any::class.java)
val method = method(path::class.java, where.type, where.value::class.java)
val predicate = method.invoke(path, where.value) as Predicate
jpaQuery.where(predicate)
} else if (where.valueList.size == 2) {
val method = path::class.java.getMethod(where.type, Comparable::class.java, Comparable::class.java)
val predicate = method.invoke(path, *where.valueList.toTypedArray()) as Predicate
jpaQuery.where(predicate)
}
}
}
fun orderBy(jpaQuery: JPAQuery<*>) {
for (orderBy in orderBy) {
val path = convert(orderBy.value)
val orderSpecifier = path::class.java.getMethod(orderBy.type.lowercase()).invoke(path) as OrderSpecifier<*>
jpaQuery.orderBy(orderSpecifier)
}
}
}
class Sql: BaseSql() {
var select: MutableList<String> = mutableListOf()
var offset: Long? = null
var limit: Long? = null
}
class From {
var value: String = ""
var leftJoin: MutableList<LeftJoin> = mutableListOf()
var rightJoin: MutableList<RightJoin> = mutableListOf()
@Suppress("MemberVisibilityCanBePrivate")
class On {
var left: String = ""
var right: String = ""
fun on(jpaQuery: JPAQuery<*>) {
val pathLeft = convert(left)
val pathRight = convert(right)
jpaQuery.on(pathLeft::class.java.getMethod("eq", Expression::class.java).invoke(pathLeft, pathRight) as Predicate)
}
}
class LeftJoin {
var value: String = ""
var on: List<On> = mutableListOf()
}
class RightJoin {
var value: String = ""
var on: List<On> = mutableListOf()
}
}
class Where {
var field: String = ""
var value: Any = ""
var valueList: List<Any> = mutableListOf()
// querydsl 的比较方法名
var type: String = "eq"
}
class OrderBy {
var value: String = ""
var type: String = ""
}
mvc接口
@RestController
class DynamicQuerydslController(
private val jpaQueryFactory: JPAQueryFactory,
applicationContext: ApplicationContext
) {
init {
val environment = applicationContext.environment
val activeProfiles = environment.activeProfiles
if (activeProfiles.contains("dev")) {
val exporter = GenericExporter()
exporter.setKeywords(Keywords.JPA)
exporter.setEntityAnnotation(Entity::class.java)
exporter.setEmbeddableAnnotation(Embeddable::class.java)
exporter.setEmbeddedAnnotation(Embedded::class.java)
exporter.setSupertypeAnnotation(MappedSuperclass::class.java)
exporter.setSkipAnnotation(Transient::class.java)
exporter.setTargetFolder(File("fnl-modules/fnl-system/target/generated-sources/java"))
exporter.export("com.fnl.system.entity.master");
}
}
@PostMapping("/dynamic/query/querydsl")
fun query(@RequestBody sql: Sql): Any {
val from = sql.from
val primary = from.value
val qPrimary = qEntity(primary)
val jpaQuery = jpaQueryFactory.from(qPrimary)
sql.from(jpaQuery)
sql.where(jpaQuery)
sql.orderBy(jpaQuery)
val total = jpaQuery.fetch().size
sql.offset?.let { jpaQuery.offset(it) }
sql.limit?.let { jpaQuery.limit(it) }
val selectQArray = sql.select.map { convert(it) }.toTypedArray()
if (selectQArray.isNotEmpty()) jpaQuery.select(*selectQArray)
val fetch = jpaQuery.fetch()
val list = mutableListOf<MutableMap<String, Any>>()
for (f in fetch) {
if (f is Tuple) {
val map = mutableMapOf<String, Any>()
for ((i, path) in selectQArray.withIndex()) {
f.get(path)?.let {
map[sql.select[i]] = it
}
}
list.add(map)
} else return fetch
}
return QuerydslResult(list, total, sql.offset, sql.limit)
// return list
}
}
data class QuerydslResult<T> (val data: T, val total: Int, val offset: Long?, val limit: Long?)
// 实体类的包名
val qEntityPackages = listOf("com.fnl.system.entity.master")
fun qEntity(str: String): EntityPathBase<*> {
for (qPrefix in qEntityPackages) {
runCatching {
return Class.forName("$qPrefix.Q${firstUpper(str)}")
.getDeclaredField(firstLower(str)).get(null) as EntityPathBase<*>
}
}
error("没有找到q类")
}
private fun firstUpper(str: String): String {
return str.substring(0, 1).uppercase() + str.substring(1)
}
private fun firstLower(str: String): String {
return str.substring(0, 1).lowercase() + str.substring(1)
}
private fun method(clazz: Class<*>, name: String, paramType: Class<*>): Method {
val methods = clazz.methods.filter { it.name == name }
if (methods.isEmpty()) error("没有${name}的方法")
for (method in methods) {
if (method.parameterTypes.last() == paramType) return method
}
return methods.find { it.parameterTypes.last() == Any::class.java } ?: error("没有${name}的方法")
}
fun convert(str: String): Path<*> {
val arr = str.split(".")
var qEntity = qEntity(arr[0]) as Path<*>
for (s in arr.stream().skip(1)) {
qEntity = qEntity::class.java.getDeclaredField(firstLower(s)).get(qEntity) as Path<*>
}
return qEntity
}gradle.kts配置文件（依赖）：
import org.jetbrains.kotlin.gradle.tasks.KotlinCompile
plugins {
id("java")
val kotlinVersion = "1.8.10"
kotlin("jvm") version kotlinVersion
kotlin("plugin.spring") version kotlinVersion
id("org.jetbrains.kotlin.kapt") version kotlinVersion
id("org.springframework.boot") version "3.0.4"
id("io.spring.dependency-management") version "1.1.0"
}
group = "me.kuku"
version = "1.0-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_17
repositories {
mavenCentral()
}
dependencies {
implementation("org.jetbrains.kotlin:kotlin-reflect")
implementation("org.jetbrains.kotlinx:kotlinx-coroutines-reactor")
implementation("io.projectreactor.kotlin:reactor-kotlin-extensions")
implementation("org.springframework.boot:spring-boot-starter-data-jpa")
implementation("org.springframework.boot:spring-boot-starter-json")
implementation("org.springframework.boot:spring-boot-starter-web")
implementation("com.fasterxml.jackson.datatype:jackson-datatype-hibernate5-jakarta:2.14.1")
implementation("com.h2database:h2:2.1.210")
implementation("com.querydsl:querydsl-core:5.0.0")
implementation("com.querydsl:querydsl-jpa:5.0.0:jakarta")
kapt("com.querydsl:querydsl-apt:5.0.0:jakarta")
kapt("org.springframework.boot:spring-boot-configuration-processor")
testImplementation("org.springframework.boot:spring-boot-starter-test")
testImplementation(kotlin("test"))
}
tasks.test {
useJUnitPlatform()
}
tasks.withType(JavaCompile::class.java) {
options.encoding = "utf-8"
}
tasks.withType<KotlinCompile> {
kotlinOptions.jvmTarget = "17"
}