JpaSpecificationExecutor与Keyset滚动结合报错，求解决方案

你遇到的错误是因为Spring Data JPA自动生成的findBy方法在结合Specification和Keyset滚动（Window返回类型）时，无法正确处理Specification中定义的查询参数绑定，导致SQL查询生成后参数数量不匹配。

Specification/Criteria API并非只兼容偏移分页，只是自动生成Keyset滚动查询的逻辑对Specification的支持存在局限。

1. 自定义Repository实现类 #

放弃依赖Spring Data自动生成方法，手动实现结合Specification的Keyset滚动查询，手动构建包含Keyset条件的查询并返回Window对象。

步骤1：定义扩展Repository接口

@Repository
interface UserRepository : JpaRepository<User, Int>, JpaSpecificationExecutor<User>, UserRepositoryCustom {
}

// 自定义扩展接口
interface UserRepositoryCustom {
    fun findByWithKeysetScroll(spec: Specification<User>, limit: Limit, position: ScrollPosition): Window<User>
}

步骤2：实现自定义Repository类

class UserRepositoryImpl(private val entityManager: EntityManager) : UserRepositoryCustom {

    override fun findByWithKeysetScroll(spec: Specification<User>, limit: Limit, position: ScrollPosition): Window<User> {
        // 1. 构建基础CriteriaQuery
        val criteriaBuilder = entityManager.criteriaBuilder
        val query = criteriaBuilder.createQuery(User::class.java)
        val root = query.from(User::class.java)

        // 2. 应用传入的Specification
        val predicate = spec.toPredicate(root, query, criteriaBuilder)
        query.where(predicate)

        // 3. 处理Keyset滚动条件
        if (position is KeysetScrollPosition) {
            val keysetConditions = position.keyset.keys.map { entry ->
                val keyPath = root.get<Any>(entry.key)
                when (entry.value) {
                    is KeysetScrollPosition.Direction.AFTER -> criteriaBuilder.greaterThan(keyPath, entry.value.value)
                    is KeysetScrollPosition.Direction.BEFORE -> criteriaBuilder.lessThan(keyPath, entry.value.value)
                }
            }
            query.where(criteriaBuilder.and(predicate, *keysetConditions.toTypedArray()))
        }

        // 4. 设置分页限制
        val typedQuery = entityManager.createQuery(query)
        typedQuery.maxResults = limit.max

        // 5. 执行查询并获取结果，同时判断是否有下一页
        val results = typedQuery.resultList
        val hasNext = results.size == limit.max

        // 6. 构建Window对象（需根据实际Spring Data版本调整构造逻辑）
        val nextPosition = if (hasNext) {
            val lastEntity = results.last()
            ScrollPosition.keyset(
                "id" to KeysetScrollPosition.Direction.AFTER(lastEntity.id)
                // 如果有其他排序键，补充对应的条件
            )
        } else {
            ScrollPosition.keyset()
        }

        return Window.of(results, limit, position, nextPosition)
    }
}

步骤3：调用自定义方法

val spec: Specification<User> = Specification { root, query, criteriaBuilder ->
    criteriaBuilder.equal(root.get<String>("name"), "John")
}

val position = ScrollPosition.keyset()
val limit = Limit.of(10)

val rows = userRepository.findByWithKeysetScroll(spec, limit, position)

2. 替代方案：使用Querydsl结合Keyset滚动 #

如果你的项目已经引入Querydsl，它对Keyset滚动的支持更友好，结合动态查询（替代Specification）可以更简洁地实现需求：

@Repository
interface UserRepository : JpaRepository<User, Int>, QuerydslPredicateExecutor<User>, QuerydslRepositorySupport(User::class.java) {

    fun findAll(predicate: Predicate, limit: Limit, position: ScrollPosition): Window<User> {
        val query = from(QUser.user).where(predicate)

        // 处理Keyset条件
        if (position is KeysetScrollPosition) {
            position.keyset.keys.forEach { entry ->
                val path = when (entry.key) {
                    "id" -> QUser.user.id
                    "name" -> QUser.user.name
                    // 其他字段映射
                    else -> throw IllegalArgumentException("Unsupported key: ${entry.key}")
                }
                when (val direction = entry.value) {
                    is KeysetScrollPosition.Direction.AFTER -> query.where(path.gt(direction.value))
                    is KeysetScrollPosition.Direction.BEFORE -> query.where(path.lt(direction.value))
                }
            }
        }

        val results = query.limit(limit.max.toLong()).fetch()
        val hasNext = results.size == limit.max
        val nextPosition = if (hasNext) {
            val last = results.last()
            ScrollPosition.keyset("id" to KeysetScrollPosition.Direction.AFTER(last.id))
        } else {
            ScrollPosition.keyset()
        }

        return Window.of(results, limit, position, nextPosition)
    }
}

• 手动构建Window对象时，需要确保nextPosition的键与你的排序字段一致，否则后续滚动会出错
• 如果你的查询有排序逻辑，需要在CriteriaQuery中添加orderBy，确保Keyset的顺序稳定
• 对于复杂的多字段Keyset，需要在nextPosition中包含所有排序字段的条件

内容的提问来源于stack exchange，提问作者Soid