通常在 java
+ springboot
下使用 querydsl
很顺畅,但是切换到 kotlin
下之后有了一些不同,这里记录一下集成的过程
一、项目依赖 maven
配置
- 增加依赖包
<properties>
<querydsl.version>4.2.1</querydsl.version>
</properties>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
<version>${querydsl.version}</version>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<scope>provided</scope>
</dependency>
- 增加编译插件
<plugin>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-plugin</artifactId>
<configuration>
<correctErrorTypes>true</correctErrorTypes>
<languageVersion>${kotlin.language.version}</languageVersion>
</configuration>
<dependencies>
<dependency>
<groupId>org.jetbrains.kotlin</groupId>
<artifactId>kotlin-maven-allopen</artifactId>
<version>${kotlin.version}</version>
</dependency>
</dependencies>
<executions>
<execution>
<id>kapt</id>
<goals>
<goal>kapt</goal>
</goals>
<configuration>
<correctErrorTypes>true</correctErrorTypes>
<sourceDirs>
<sourceDir>src/main/kotlin</sourceDir>
<sourceDir>src/main/java</sourceDir>
</sourceDirs>
<annotationProcessorPaths>
<annotationProcessorPath>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<classifier>jpa</classifier>
</annotationProcessorPath>
</annotationProcessorPaths>
</configuration>
</execution>
<execution>
<id>compile</id>
<goals>
<goal>compile</goal>
</goals>
<configuration>
<sourceDirs>
<sourceDir>src/main/kotlin</sourceDir>
<sourceDir>src/main/java</sourceDir>
</sourceDirs>
</configuration>
</execution>
<execution>
<id>test-kapt</id>
<goals>
<goal>test-kapt</goal>
</goals>
<configuration>
<sourceDirs>
<sourceDir>src/test/kotlin</sourceDir>
<sourceDir>src/test/java</sourceDir>
</sourceDirs>
<annotationProcessorPaths>
<annotationProcessorPath>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<version>${querydsl.version}</version>
<classifier>jpa</classifier>
</annotationProcessorPath>
</annotationProcessorPaths>
</configuration>
</execution>
<execution>
<id>test-compile</id>
<goals>
<goal>test-compile</goal>
</goals>
<configuration>
<sourceDirs>
<sourceDir>src/test/kotlin</sourceDir>
<sourceDir>src/test/java</sourceDir>
<sourceDir>target/generated-sources/kapt/test</sourceDir>
</sourceDirs>
</configuration>
</execution>
</executions>
</plugin>
二、数据库操作层接口继承
QuerydslPredicateExecutor<Device>
@Repository
interface DeviceRepository : JpaRepository<Device, Long>,
JpaSpecificationExecutor<Device>, QuerydslPredicateExecutor<Device> {
fun getByDeviceName(deviceName: String): Optional<Device>
}
由于查询回来的对象是一个 List<com.querydsl.core.Tuple>
这里需要做一个简单的解析处理
四、创建一个通用的查询工具解析器
QuerydslUtlis
/**
* 对querydsl的查询结果做了一个简化解析的实现,具体输出参考 PageUtlis
* @see com.ayouran.flow.utlis.PageUtlis
*/
@Component
class QuerydslUtlis @Autowired
constructor(private val entityManager: EntityManager) {
private val logger = LoggerFactory.getLogger(javaClass)
//查询工厂实体
private var queryFactory: JPAQueryFactory? = null
//jackson对象
private var objectMapper: ObjectMapper? = null
@PostConstruct
private fun initFactory() {
logger.info("开始实例化JPAQueryFactory")
queryFactory = JPAQueryFactory(entityManager)
}
@PostConstruct
private fun objectMapperConfig() {
if (objectMapper == null) objectMapper = ObjectMapper()
objectMapper!!.configure(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS, false)
objectMapper!!.dateFormat = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
}
fun getQueryFactory(): JPAQueryFactory {
if (queryFactory == null) initFactory()
return queryFactory!!
}
/**
* 将com.querydsl.core.Tuple转换为需要的bean
*
* @param tuple 查询得到的数据
* @param exprs 查询的列
* @param clazz 要转换的目标 class类型
* @return 返回根据 clazz转换的 class -> Object对象
* ==== 注意 =======
* @see objectMapperConfig
* 需要在有 objectMapperConfig 的配置前提
* @see com.fasterxml.jackson.annotation.JsonProperty
* 默认取最后的字段名字与clazz的类字段名称匹配,如有不匹配的字段 请自行使用@JsonProperty注解来匹配
* 匹配规则为 exprs 查询参数的全称
*
* 示例:
* querydslUtlis.getBean(it, exprs, QueryDeviceFlowRulesVO::class.java) as QueryDeviceFlowRulesVO
*/
fun getBean(tuple: Tuple, exprs: Array<Expression<*>>, clazz: Class<*>): Any {
val map = mutableMapOf<String, Any>()
exprs.forEach {
var value = tuple[it]
if (value == null) value = Any()
val key = it.toString()
val index = key.lastIndexOf(".")
var subKey = ""
if (index != -1) subKey = key.substring(key.lastIndexOf(".") + 1)
if (subKey.isNotBlank() && !map.containsKey(subKey)) {
map[subKey] = value
} else {
map[key] = value
}
}
if (map.isEmpty()) return Any()
return mapToBean(map, clazz)
}
/**
* 将List<com.querydsl.core.Tuple>转换为需要的List<bean>
*
* @param tuples 查询得到的数据结果集
* @param exprs 查询的列
* @param clazz 要转换的目标 class类型
* @return 返回根据 clazz转换的 class -> List<Object>对象
* ==== 注意 =======
* @see objectMapperConfig
* 需要在有 objectMapperConfig 的配置前提
* @see com.fasterxml.jackson.annotation.JsonProperty
* 默认取最后的字段名字与clazz的类字段名称匹配,如有不匹配的字段 请自行使用@JsonProperty注解来匹配
* 匹配规则为 exprs 查询参数的全称
* 示例:
* querydslUtlis.getList(results.results, exprs, QueryDeviceVO::class.java) as List<QueryDeviceVO>
*/
fun getCollection(tuples: Collection<Tuple>, exprs: Array<Expression<*>>, clazz: Class<*>): Collection<Any> {
val list = mutableListOf<Map<String, Any>>()
tuples.forEach { tuple ->
val map = mutableMapOf<String, Any>()
exprs.forEach {
var value = tuple[it]
if (value == null) value = Any()
val key = it.toString()
val index = key.lastIndexOf(".")
var subKey = ""
if (index != -1) subKey = key.substring(key.lastIndexOf(".") + 1)
if (subKey.isNotBlank() && !map.containsKey(subKey)) {
map[subKey] = value
} else {
map[key] = value
}
}
if (map.isEmpty()) return@forEach
list.add(map)
}
return listToListBean(list, getCollectionType(List::class.java, clazz))
}
// 将对象转成字符串
@Throws(Exception::class)
private fun objectToString(obj: Any): String {
return objectMapper!!.writeValueAsString(obj)
}
// 将Map转成指定的Bean
@Throws(Exception::class)
private fun mapToBean(map: Map<*, *>, clazz: Class<*>): Any {
return objectMapper!!.readValue(objectToString(map), clazz)
}
// 将Bean转成Map
@Throws(Exception::class)
private fun beanToMap(obj: Any): Map<*, *> {
return objectMapper!!.readValue(objectToString(obj), MutableMap::class.java)
}
// 将list<map>转成指定的list<Bean>
@Throws(Exception::class)
private fun listToListBean(list: Collection<*>, clazz: JavaType): Collection<Any> {
return objectMapper!!.readValue(objectToString(list), clazz)
}
/**
* 获取泛型的Collection Type
*
* @param collectionClass 泛型的Collection
* @param elementClasses 元素类
* @return JavaType Java类型
* @since 1.0
*/
private fun getCollectionType(collectionClass: Class<*>, vararg elementClasses: Class<*>): JavaType {
return objectMapper!!.typeFactory.constructParametricType(collectionClass, *elementClasses)
}
}
这个类会进行 JPAQueryFactory
初始化,和一些方便 解析 List<com.querydsl.core.Tuple>
相关的方法
五、创建封装给接口输出的对象
QueryDeviceVO
data class QueryDeviceVO(
@ApiModelProperty(value = "设备编号", dataType = "string")
var deviceNo: String? = null,
@ApiModelProperty(value = "设备名称", dataType = "string")
var deviceName: String? = null,
@ApiModelProperty(value = "脚本根路径", dataType = "string")
var shellRoot: String? = null,
@ApiModelProperty(value = "root密码", dataType = "string")
var rootPwd: String? = null,
@ApiModelProperty(value = "远程ssh端口", dataType = "long")
var sshProt: Long? = null,
@ApiModelProperty(value = "备注", dataType = "string")
var remarks: String? = null,
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@ApiModelProperty(value = "创建时间", dataType = "time")
var createAt: Date? = null,
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@ApiModelProperty(value = "修改时间", dataType = "time")
var updateAt: Date? = null,
@ApiModelProperty(value = "是否删除,1未删除 -1已删除", dataType = "int")
var deleted: Int? = null
)
六、创建一个分页的转换器
PageUtlis
object PageUtlis {
/**
* 分页 返回 PageResult
* val results = querydslUtlis.getQueryFactory()
* .select(*exprs)
* .from(qDevice)
* .where(ExpressionUtils.allOf(rredicate))
* .orderBy(qDevice.createAt.desc())
* .offset((aPageRequest.pageIndex!! - 1) * aPageRequest.pageSize!!)
* .limit(aPageRequest.pageSize!!)
* .fetchResults()
* return PageUtlis.retPage(results, querydslUtlis.getCollection(results.results, exprs, QueryDeviceVO::class.java) as Collection<QueryDeviceVO>)
*/
fun <E, R> retPage(queryResults: QueryResults<E>, list: Collection<R>): PageResult<R> {
val page = PageResult<R>()
page.result = list
val pageInfo = PageResult.PageInfo()
pageInfo.pageIndex = queryResults.offset
pageInfo.pageSize = queryResults.limit
pageInfo.total = queryResults.total
pageInfo.pageTotal = queryResults.results.size.toLong()
pageInfo.isLastPage = pageInfo.pageIndex!! * pageInfo.pageSize!! - pageInfo.total!! >= 0
page.pageInfo = pageInfo
return page
}
/**
* 分页 返回 PageResult
* 例如
* querydslUtlis.getQueryFactory()
* .select(*exprs)
* .from(qDeviceRules)
* .where(ExpressionUtils.allOf(rredicate))
* .leftJoin(qFlowRules)
* .on(qDeviceRules.rulesNo.eq(qFlowRules.rulesNo))
* .orderBy(qDeviceRules.createAt.desc())
* .offset((aPageRequest.pageIndex!! - 1) * aPageRequest.pageSize!!)
* .limit(aPageRequest.pageSize!!)
* .fetchResults(), Function<Tuple, QueryDeviceFlowRulesVO> {
* querydslUtlis.getBean(it, exprs, QueryDeviceFlowRulesVO::class.java) as QueryDeviceFlowRulesVO
* }
*/
fun <E, R> retPage(queryResults: QueryResults<E>, function: Function<E, R>): PageResult<R> {
val page = PageResult<R>()
page.result = toList(queryResults.results, function)
val pageInfo = PageResult.PageInfo()
pageInfo.pageIndex = queryResults.offset
pageInfo.pageSize = queryResults.limit
pageInfo.total = queryResults.total
pageInfo.pageTotal = queryResults.results.size.toLong()
pageInfo.isLastPage = pageInfo.pageIndex!! * pageInfo.pageSize!! - pageInfo.total!! >= 0
page.pageInfo = pageInfo
return page
}
private fun <E, R> toList(col: Collection<E>, function: Function<E, R>): List<R>? {
return col.stream().map(function).collect(Collectors.toList<R>())
}
/**
* 分页 返回 PageResult
*/
fun <T> retPage(data: Collection<T>, pageNumber: Long?, pageSize: Long?, totalCount: Long?): PageResult<T> {
val page = PageResult<T>()
val pageInfo = PageResult.PageInfo()
pageInfo.pageIndex = pageNumber
pageInfo.pageSize = pageSize
pageInfo.total = totalCount
pageInfo.pageTotal = data.size.toLong()
pageInfo.isLastPage = pageInfo.pageIndex!! * pageInfo.pageSize!! - pageInfo.total!! >= 0
page.result = data
page.pageInfo = pageInfo
return page
}
}
七、实现一个多表查询
@Service
class DeviceServiceImpl @Autowired
constructor(private val deviceRepository: DeviceRepository,
private val deviceRulesRepository: DeviceRulesRepository,
private val querydslUtlis: QuerydslUtlis,
private val deviceMapstruct: DeviceMapstruct) : DeviceService {
private val logger = LoggerFactory.getLogger(javaClass)
override fun queryDeviceFlowRules(aPageRequest: APageRequest): PageResult<QueryDeviceFlowRulesVO> {
val qDeviceRules = QDeviceRules.deviceRules
val qFlowRules = QFlowRules.flowRules
var rredicate: Predicate? = null
if (StringUtils.isNotBlank(aPageRequest.query)) rredicate = qDeviceRules.deviceNo.eq(aPageRequest.query)
val exprs = arrayOf<Expression<*>>(qDeviceRules.deviceNo, qDeviceRules.deleted, qFlowRules.rulesNo, qFlowRules.flowMax,
qFlowRules.startTime, qFlowRules.endTime)
val results = querydslUtlis.getQueryFactory()
.select(*exprs)
.from(qDeviceRules)
.where(ExpressionUtils.allOf(rredicate))
.leftJoin(qFlowRules)
.on(qDeviceRules.rulesNo.eq(qFlowRules.rulesNo))
.orderBy(qDeviceRules.createAt.desc())
.offset((aPageRequest.pageIndex!! - 1) * aPageRequest.pageSize!!)
.limit(aPageRequest.pageSize!!)
.fetchResults()
return PageUtlis.retPage(results, querydslUtlis.getCollection(results.results, exprs, QueryDeviceFlowRulesVO::class.java) as Collection<QueryDeviceFlowRulesVO>)
}
}
ps:
对数据类的操作,使用 data
声明的类可以使用自带的 copy
的方法来赋值成新的对象,同时也会生成一系列的 pojo
的默认方法。
到此使用 querydsl
的查询完成,到此可以发现 kotlin
下的开发根本不需要 lombok