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

前提：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的分页查询：

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秒不到，如果有好的优化请评论。
以后补充。。。