使用kotlin+springboot集成querydsl_querydsl fetchresults-CSDN博客

本文链接：https://blog.csdn.net/u012599432/article/details/105455477

通常在 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>)
    }
 }