Spring Boot 结合 spring data jpa中的 DTO 映射查询《Spring Boot 开发实战》

package com.alibaba.microtek.mapper.microtek;

import com.alibaba.microtek.dto.SlowSqlTemplateDto;
import com.alibaba.microtek.model.microtek.XxptSlowQueyInfo;
import com.taobao.pandora.pandolet.annotation.Service;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface XxptSlowQueyInfoMapper {
   
    @Select("SELECT app_name appName,sum(slow_template_count) c  FROM `xxpt_slow_quey_info` GROUP BY app_name ORDER BY c desc limit 10")
    @ResultType(SlowSqlTemplateDto.class)
    List<SlowSqlTemplateDto> listSlowSqlTemplateDto();

}



package com.alibaba.microtek.dto;

public class SlowSqlTemplateDto {
    String appName;
    Integer c;

    public String getAppName() {
        return appName;
    }

    public void setAppName(String appName) {
        this.appName = appName;
    }

    public Integer getC() {
        return c;
    }

    public void setC(Integer c) {
        this.c = c;
    }
}

package com.slow.sql.speedo

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.RestController

@RestController
class XxptSlowQueyInfoController {

    @Autowired
    lateinit var syncSlowSqlDataJob: SyncSlowSqlDataJob
    @Autowired
    lateinit var xxptSlowQueyInfoDao: XxptSlowQueyInfoDao

    @GetMapping("/syncSlowSqlDataJob")
    fun syncSlowSqlDataJob() {
        syncSlowSqlDataJob.sync()
    }

    @GetMapping("/listSlowSqlTemplateDto")
    fun listSlowSqlTemplateDto(): List<SlowSqlTemplateDto> {
        return xxptSlowQueyInfoDao.listSlowSqlTemplateDto()
    }

}



package com.slow.sql.speedo

import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query

interface XxptSlowQueyInfoDao : JpaRepository<XxptSlowQueyInfo, Long> {
    fun save(record: XxptSlowQueyInfo): XxptSlowQueyInfo

    @Query("""
        SELECT 
        new com.slow.sql.speedo.SlowSqlTemplateDto( x.appName , sum(x.slowTemplateCount) ) 
        FROM XxptSlowQueyInfo x 
        GROUP BY x.appName 
        ORDER BY sum(x.slowTemplateCount) desc
    """)
    fun listSlowSqlTemplateDto(): List<SlowSqlTemplateDto>
}




package com.slow.sql.speedo

import com.alibaba.fastjson.annotation.JSONField
import java.util.*
import javax.persistence.*

@Entity
@Table(name = "xxpt_slow_quey_info")
class XxptSlowQueyInfo {
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Id
    var id: Long? = null

    @JSONField(name = "app_name")
    var appName: String? = null

    var owner: String? = null

    var ops: String? = null

    @JSONField(name = "need_optimize_slow_template_count")
    var needOptimizeSlowTemplateCount: Int? = null

    @JSONField(name = "need_optimize_slow_sql_count")
    var needOptimizeSlowSqlCount: Int? = null

    @JSONField(name = "slow_template_count")
    var slowTemplateCount: Int? = null

    @JSONField(name = "slow_sql_count")
    var slowSqlCount: Int? = null

    var date: Date? = null


}