自定义 Spring Data JPA 实现复杂分页

Spring Data JPA 简单分页示例

我们都知道JPA对于简单的分页查询使用是非常便利的，一种是通过 Specification 构造查询分页，一种是通过 @Query 注解自定义 SQL 实现查询分页，对应示例如下：

• Specification 的简单使用

val pageRequest = PageRequest.of(inUsePromotionCouponsVO.index - 1, inUsePromotionCouponsVO.size)

val specification = Specification<PromotionCoupon> { root, criteriaQuery, criteriaBuilder ->
    val predicate = criteriaBuilder.conjunction()
    predicate.expressions.add(criteriaBuilder.equal(root.get<Int>("couponBaseID"), inUsePromotionCouponsVO.couponBaseID))
    predicate.expressions.add(criteriaBuilder.equal(root.get<Int>("isDel"), inUsePromotionCouponsVO.isDel))
    predicate.expressions.add(criteriaBuilder.equal(root.get<Int>("isUse"), 1))
    if(inUsePromotionCouponsVO.useTime != null && inUsePromotionCouponsVO.useTime != "") predicate.expressions.add(criteriaBuilder.like(root.get<String>("useTime"), inUsePromotionCouponsVO.useTime + "%"))
    criteriaQuery.orderBy(criteriaBuilder.desc(root.get<Int>("id")))
    predicate
}
val page = promotionCouponRepository.findAll(specification, pageRequest)

优点：面向对象，不用接触 SQL 语句，单表动态条件支持非常好

缺点：多表操作很复杂，不支持字段子查询（待确认？）

• @Query 注解的简单使用

@Query(nativeQuery = true, value = "select a.*, (select shopName from shop where memID = a.memID limit 0, 1) shopName, " +
        "(select count(1) from promotionCoupon where couponBaseID in (select couponBaseID from promotionActivityCoupon where actID = a.id) and isUse = 0) receivedTotal, " +
        "(select GROUP_CONCAT(preferentialText SEPARATOR '&') from promotionCouponBase where id in (select couponBaseID from promotionActivityCoupon where actID = a.id)) preferentialText " +
        " from promotionActivity a where isDel = 0 and (name like CONCAT('%', ?1, '%') or ?1 is null) " +
        " and (status = ?2 or ?2 is null) and (actType = ?3 or ?3 is null) " +
        " and (memID = ?4 or ?4 is null) " +
        " and (isRecommend = ?5 or ?5 is null) " +
        " and (actMode = ?6 or ?6 is null) " +
        " order by id desc",
        countQuery = "select count(1) from promotionActivity where isDel = 0 and (name like CONCAT('%', ?1, '%') or ?1 is null) " +
                " and (status = ?2 or ?2 is null) and (actType = ?3 or ?3 is null) and (memID = ?4 or ?4 is null) and (isRecommend = ?5 or ?5 is null) and (actMode = ?6 or ?6 is null)")
fun findByPage(name: String?, status: Int?, actType: Int?, memID: Int?, isRecommend:Int?=null,actMode:Int?,pageable: Pageable): Page<PromotionActivityDO>

优点：直接写 SQL 语句，随心所欲

缺点：不支持动态条件，分页需要多写一条 COUNT 语句

通过 Spring Data JPA 自定义复杂分页

• 自定义动态 SQL 语句

class SimpleSQL {

    private val query = StringBuilder(50)
    private val countQuery = StringBuilder(50)
    private val params = mutableMapOf<String, Any?>()

    fun select(columns: String): SimpleSQL {
        query.append("SELECT ").append(columns).append(" ")
        countQuery.append("SELECT COUNT(1) ")
        return this
    }

    fun from(table: String): SimpleSQL {
        query.append(" FROM ").append(table).append(" ")
        countQuery.append(" FROM ").append(table).append(" ")
        return this
    }

    fun where(criteria: String): SimpleSQL {
        query.append(" WHERE ").append(criteria).append(" ")
        countQuery.append(" WHERE ").append(criteria).append(" ")
        return this
    }

    fun orderBy(orders: String): SimpleSQL {
        query.append(" ORDER BY ").append(orders).append(" ")
        return this
    }

    fun setParam(key: String, param: Any?) {
        params[key] = param
    }

    fun getParams(): Map<String, Any?> {
        return this.params.toMap()
    }

    fun getQuery(): String {
        return query.toString()
    }

    fun getCountQuery(): String {
        return countQuery.toString()
    }
}

• 使用 EntityManager 实现分页

import org.springframework.beans.factory.annotation.Autowired
import org.springframework.data.domain.Page
import org.springframework.data.domain.PageImpl
import org.springframework.data.domain.Pageable
import org.springframework.stereotype.Component
import javax.persistence.EntityManager

/**
 * Created by pxc on 2019-11-25
 * Spring Data JPA复杂SQL分页
 */
@Component
class ComplexPageSQLUtils {

    @Autowired
    private lateinit var entityManager: EntityManager

    fun <T> execute(pageable: Pageable, simpleSQL: SimpleSQL, tClass: Class<T>, vararg params: Any): Page<T> {

        val createQuery = entityManager.createNativeQuery(simpleSQL.getQuery(), tClass)

        val createCountQuery = entityManager.createNativeQuery(simpleSQL.getCountQuery())

        simpleSQL.getParams().forEach { t, u ->
            createQuery.setParameter(t, u)
            createCountQuery.setParameter(t, u)
        }

        createQuery.firstResult = pageable.pageNumber * pageable.pageSize
        createQuery.maxResults = pageable.pageSize

        val resultList = createQuery.resultList as List<T>
        val aLong = createCountQuery.resultList[0].toString().toLong()

        return PageImpl<T>(resultList, pageable, aLong)

    }
}

• 单元测试

import com.linkershow.common.vo.InGetPromotionActivitysVO
import com.linkershow.common.vo.OutGetPromotionActivitysPageVO
import com.linkershow.common.vo.OutGetPromotionActivitysVO
import com.linkershow.data.entity.PromotionActivityDO
import com.linkershow.data.util.ComplexPageSQLUtils
import com.linkershow.data.util.SimpleSQL
import org.junit.Test
import org.junit.runner.RunWith
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.boot.test.context.SpringBootTest
import org.springframework.data.domain.PageRequest
import org.springframework.test.context.junit4.SpringRunner

@RunWith(SpringRunner::class)
@SpringBootTest
class ComplexPageSQLTest {

    @Autowired
    private lateinit var complexPageSQLUtils: ComplexPageSQLUtils

    /**
     * Spring Data JPA复杂SQL分页示例
     */
    @Test
    fun example() {

        val inGetPromotionActivitysVO = InGetPromotionActivitysVO("", 1, 2, 5, null)

        val pageRequest = PageRequest.of(0, 10)

        val simpleSQL = SimpleSQL()// 主SQL

        simpleSQL
                // 主查询结果集
                .select("a.*, (select shopName from shop where memID = a.memID limit 0, 1) shopName, " +
                " (select count(1) from promotionCoupon where couponBaseID in (select couponBaseID from promotionActivityCoupon where actID = a.id) and isUse = 0) receivedTotal, " +
                " (select GROUP_CONCAT(preferentialText SEPARATOR '&') from promotionCouponBase where id in (select couponBaseID from promotionActivityCoupon where actID = a.id)) preferentialText ")
                // 主表，支持多表联查
                .from("promotionActivity a")

        // 开始拼接WHERE条件
        val criteria = StringBuilder(" isDel = 0 ")

        if (inGetPromotionActivitysVO.name != null) {

            criteria.append(" and name like CONCAT('%', :name, '%') ")
            simpleSQL.setParam("name", inGetPromotionActivitysVO.name)
        }

        if (inGetPromotionActivitysVO.status != null) {

            criteria.append(" and status = :status ")
            simpleSQL.setParam("status", inGetPromotionActivitysVO.status)
        }

//        if (inGetPromotionActivitysVO.actType != null) {
//
//            criteria.append(" and actType = :actType ")
//            simpleSQL.setParam("actType", inGetPromotionActivitysVO.actType)
//        }
//
//
//        if (inGetPromotionActivitysVO.IsRecommend != null) {
//
//            criteria.append(" and isRecommend = :isRecommend ")
//            simpleSQL.setParam("isRecommend", inGetPromotionActivitysVO.IsRecommend)
//        }

        // 主条件
        simpleSQL.where(criteria.toString())

        // 主排序
        simpleSQL.orderBy("id")

        // 调用方法，返回分页
        val page = complexPageSQLUtils.execute(pageRequest, simpleSQL, PromotionActivityDO::class.java)

        // 生成结果
        val outGetPromotionActivitysPageVO = OutGetPromotionActivitysPageVO()
        outGetPromotionActivitysPageVO.create(page.isFirst, page.isLast, page.totalPages, page.totalElements, page.content as List<OutGetPromotionActivitysVO?>)

        println(outGetPromotionActivitysPageVO.list)
    }
}