【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>
}
完整的工程示例源代码：
