kotlin+springboot+jpa实现从简单到复杂的查询，分页查询，动态条件，投影Projection

前提：kotlin+springboot+jpa

1.最简单的查询：

import org.springframework.data.repository.CrudRepository
...
interface AccountRepository : CrudRepository<Account, Long> {
	fun findByName(name: String): Account?
}

说明：根据name字段查询account表的信息

2.使用@Query的简单查询：

import org.springframework.data.jpa.repository.Query
...
interface AccountRepository : CrudRepository<Account, Long> {
	@Query(value = "from Account at where at.phone = ?1")
	fun findList(phone: String): List<Account>
}

说明：实现 根据phone查询account信息，返回list。

如果需要具体某几个字段：

@Query(value = "select new map(at.name, at.status, at.loginAcct) from Account at where at.phone = ?1")
fun findList(phone: String): List<Map<String, String>>

或者有聚合函数：

@Query(value = "select count(at.id),count(case when at.position is null then at.id end) from Account at where at.status= ?1")
fun countByStatus(status: Int): Array<Int>

3.使用@Query的分页查询：

多排序条件可以这样写 :
…Sort.Direction.DESC, “a”,“b”)
表示按照a,b倒序;
如果这样写：…Sort.Direction.DESC, “a,b”)，解析出来是order by a,b desc表示a正序，b倒序;
还有一种写法扩展性更好：
val sort = Sort(Sort.Direction.DESC,“a”).and(Sort(Sort.Direction.ASC,“b”))
val pg = PageRequest(currentPage, pageSize, sort)
表示a倒序，b正序。

import org.springframework.data.domain.PageRequest
import org.springframework.data.domain.Sort
...
val pageable = PageRequest(currentPage, pageSize, Sort.Direction.DESC, "updateAt")

import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
...
interface AccountRepository : CrudRepository<Account, Long> {
	@Query(value = "from Account as at where (at.name like %?1% or ?1 is null)")
	fun findAll(name: String?, pageable: Pageable): Page<Account>
 }

说明：page中参数为当前页（初始值是0），每页数量，排序方式，排序字段；实现 根据name模糊查询的account信息，按照更新时间（updateAt字段）倒序排序，返回page对象；content和totalElements对应查询结果和查询总数：

result.put("list", pg.content)
result.put("total", pg.totalElements)

4.使用nativeQuery的查询
比如需要查视图

	@Query(nativeQuery = true, value = "select * from v_cstm_linker_list a where a.link_phone = ?1")
	fun findViewByPhone(linkPhone: String): List<Any>

说明：nativeQuery=true表示使用本地sql查询的方式，sql中的字段应为表中字段（link_phone 而不是linkPhone,因为并没有视图的实体映射）

如果需要分页：

	@Query(nativeQuery = true, countQuery = "select count(*) from v_cstm_linker_list a where (a.link_name like %?1% or ?1 is null)", value = "select * from v_cstm_linker_list a where (a.link_name like %?1% or ?1 is null) order by ?#{#pageable}")
	fun findCstmLinker(linkName: String?, linkPhone: String?, pageable: Pageable): Page<Any>

说明：countQuery 计算总数，分页查询通过?#{#pageable}来实现
或者

	@Query(nativeQuery = true, value = "select * from (select a.*,rownum rn from (...) a where rownum < ?2) where rn >?1")
	fun findListPaginate(firstNow: Int, lastRow: Int): List<...>

这边用（…）表示可能子查询情况，需要计算firstNow和lastRow，注意初始页是0还是1，rownum是<还是<=

5.最近遇到的需求
要求：多表查询 +聚合 + 动态条件 + 根据聚合函数别名排序 + 分页
先看下完整sql:

select a.comp_name,
                       d.name as acct_name,
                       e.name as dpt_name,
                       a.erp_code,
                       COALESCE(bb.sum1, 0.0) as this_month_weight,
                       COALESCE(cc.sum2, 0.0) as last_month_weight
                  from zzzzz a
                  left join (select b.member_code as code1,
                                   sum(b.weight) as sum1
                              from xxxxx@crmstatdev b
                             where to_char(b.deal_date, 'yyyy-MM-dd') >=
                                   '2018-12-01'
                               and to_char(b.deal_date, 'yyyy-MM-dd') <=
                                   '2018-12-31'
                             group by b.member_code) bb
                    on bb.code1 = a.erp_code
                  left join (select c.member_code as code2,
                                   sum(c.weight) as sum2
                              from xxxxx@crmstatdev c
                             where to_char(c.deal_date, 'yyyy-MM-dd') >=
                                   '2018-11-01'
                               and to_char(c.deal_date, 'yyyy-MM-dd') <=
                                   '2018-11-30'
                             group by c.member_code) cc
                    on cc.code2 = a.erp_code
                  left join ccccc d
                    on a.fk_acct_id = d.id
                  left join vvvvv e
                    on a.fk_dpt_id = e.id
                  left join bbbbb f
                    on e.fk_org_id = f.id
                 where a.mark = 2
                   and a.status = 1
                   and a.erp_code is not null
                   and a.comp_name like '%测试名称%'
                   and d.name like '%测试人员%'
                   and e.name like '%测试部门%'
                   and (decode(COALESCE(cc.sum2, 0.0), 0,0,COALESCE(bb.sum1, 0.0) / COALESCE(cc.sum2, 0.0)) < 0.65)
                 order by this_month_weight desc

五个表关联；获取本月和上月的sum统计数据；decode …< 0.65 这个条件是动态的；this_month_weight是聚合函数的别名（#pageable不能识别正确的别名）。

解决方法：使用entityManager，写分页，拼接sql

import javax.persistence.EntityManager
import javax.persistence.PersistenceContext
...
@PersistenceContext
private lateinit var entityManager: EntityManager //实体管理对象
...
// 计数
val countSql = "select count(*)$mainStr$warningStr" //mainStr就是复杂多表语句，warningStr就是动态条件语句
// 查询
 val queryStr = "select ...as last_month_weight $mainStr$warningStr$sortStr" // sortStr就是根据聚合函数的排序语句    
// 分页
val querySql = selectPageSql(currentPage, pageSize, queryStr)
// 原生查询
val countQuery = this.entityManager.createNativeQuery(countSql)
val count = countQuery.resultList
val query = this.entityManager.createNativeQuery(querySql)
val queryList = query.resultList as List<Any>
result["list"] = queryList
result["total"] = count[0].toString().toInt()

// 分页 currentPage从0开始
fun selectPageSql(currentPage: Int, pageSize: Int, queryStr: String): String{
	val start = currentPage * pageSize
	val end = (currentPage + 1) * pageSize + 1
	return "select * from (select rst.*,rownum rn from ($queryStr) rst where rownum < $end) where rn > $start"
}

最后整个查询1秒不到，如果有好的优化请评论。
以后补充。。。

--------------------------------------------------------2019-02-19------------------------------------------------------
补充jpa Projection的使用：
1.场景：比如我要查询多表数据或者一个视图然后返回我需要的字段，一般都是用List<Any>或者Page<Any>作为返回类型.

@Query(nativeQuery = true, value = "select a.name,a.main_status,a.phone,b.linkers_id,b.customers_id,c.comp_name from t_linker a, ref_cstm_linker b, t_customer c where ...")
fun findLinkAndCstmRelation(): List<Any>

这种数据是列表加数组形式的，如下图，没有文档都不知道对应的是什么，而且如果用这个数据做处理，一般都是要 list[0]，arry[0]这样去取数据，很容易出错而且不易维护。

网上说可以使用自定义实体+构造函数的方式，但是觉得如果字段一多构造方法就很长很烦，
所以采用基于接口的投影方式来解决这个问题
2.使用：
注意需要jpa版本1.10以上,1.10的返回有问题，即使在sql中是select C,D,B,A…还是按照首字母顺序（A,B,C,D）返回给你，使用1.11以上的则没有该问题，这边用的是springboot,所以只要修改springboot版本就行
首先创建需要的投影，命名get+名称，这个名称可以任意，因为数据是按照顺序去对应的，但是还是和需要的字段一致最好，然后注意抽象方法的返回，要和你的字段的类型一致（按顺序），比如我语句中写的是select a.name,a.main_status,a.phone,b.linkers_id,b.customers_id,c.comp_name ....，那么按照顺序去定义投影，如下：

interface LinkProjection {//jpa Projection
    fun getLinkName(): String
    fun getMainStatus(): Int
    fun getLinkPhone(): String
    fun getLinkId(): Long
    fun getCstmId(): Long
    fun getCompName(): String
}

遇到个坑，就是如果是分页的：

@Query(nativeQuery = true, countQuery = "...",value ="... ?#{#pageable}")
fun findLinkAndCstmRelation(...,pageable: Pageable): Page<LinkProjection>

那么需要加上rownum字段(不然报数组越界)：

interface LinkProjection {//jpa Projection
    fun getLinkName(): String
    fun getMainStatus(): Int
    fun getLinkPhone(): String
    fun getLinkId(): Long
    fun getCstmId(): Long
    fun getCompName(): String
    fun getRowNum(): String
}

查询接口中的返回类型就用Page<LinkProjection>,List<LinkProjection>就可以了，然后如果不需要对返回值处理，数据样子是：

如果需要处理，可以直接用get方法获取：

val relationMap = linkerRepo.findLinkAndCstmRelation.groupBy { m-> m.getLinkPhone() }