【Spring Boot + Kotlin 实战教程】Spring Data JPA 多表关联查询 映射到 Dto 的方法

TechArticle

package com.ak47.cms.cms.entity

import java.util.*
import javax.persistence.*

@Entity
@Table(indexes = arrayOf(
        Index(name = "idx_url", unique = true, columnList = "url")))
open class TechArticle {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Long = -1

    var url = "URL"
    var title = "Kotlin 使用 Spring WebFlux 实现响应式编程"

    @Lob
    var simpleContent = "文章摘要"

    @Lob
    var showContent = "文章内容"
    // TechArticleTag 表中的 tagId
    var tagId = -1
    var category = "编程语言"
    var gmtCreate = Date()
    var gmtModified = Date()

}

TechArticleTag

package com.ak47.cms.cms.entity

import java.util.*
import javax.persistence.Entity
import javax.persistence.GeneratedValue
import javax.persistence.GenerationType
import javax.persistence.Id


@Entity
class TechArticleTag {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    var id: Long = -1

    var tagId = -1
    // 文章标签：例如 Kotlin，Java，Spring Boot 等
    var tagDetail = "Kotlin"

    var gmtCreate = Date()
    var gmtModified = Date()
}

DTO 定义类： TechArticleDto

package com.ak47.cms.cms.dto

import com.ak47.cms.cms.entity.TechArticle
import java.util.*

class TechArticleDto : TechArticle {
    var tagDetail = ""

    constructor(
            id: Long,
            url: String,
            title: String,
            simpleContent: String,
            showContent: String,
            tagId: Int,
            tagDetail: String,
            gmtCreate: Date,
            gmtModified: Date
    ) : super() {
        this.id = id
        this.url = url
        this.title = title
        this.simpleContent = simpleContent
        this.showContent = showContent
        this.tagId = tagId
        this.tagDetail = tagDetail
        this.gmtCreate = gmtCreate
        this.gmtModified = gmtModified
    }
}

JPQL 语句的写法

package com.ak47.cms.cms.dao

import com.ak47.cms.cms.dto.TechArticleDto
import com.ak47.cms.cms.entity.TechArticle
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.query.Param

interface TechArticleRepository : JpaRepository<TechArticle, Long> {
    @Query("select count(*) from #{#entityName} a where a.url = :url")
    fun countByUrl(@Param("url") url: String): Int

    @Query("select new com.ak47.cms.cms.dto.TechArticleDto( a.id, a.url, a.title, a.simpleContent, a.showContent , a.tagId, b.tagDetail , a.gmtCreate, a.gmtModified ) " +
            "from TechArticle a, TechArticleTag b where a.tagId = b.tagId")
    fun listTechArticleDto(page: Pageable): Page<TechArticleDto>

    @Query("select new com.ak47.cms.cms.dto.TechArticleDto( a.id, a.url, a.title, a.simpleContent, a.showContent , a.tagId, b.tagDetail , a.gmtCreate, a.gmtModified ) " +
            "from TechArticle a left join TechArticleTag b on a.tagId = b.tagId where a.title like %:searchText% or a.showContent  like %:searchText%  ")
    fun searchTechArticleDto(page: Pageable, @Param("searchText") searchText: String): Page<TechArticleDto>

}

完整的工程示例源代码：

https://github.com/AK-47-D/cms/tree/picture_2017.11.11