我们知道，在 MyBatis 中直接 DTO 映射查询的时候，写起来非常简单。只要字段对应上即可：
下面是对应的 Java 代码实例：
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;
}
}
在 JPA 中，稍微有点费事，但也还好。下面是一个完整的 Kotlin 代码实例
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
}
