Micronaut(Kotlin)中使用Pageable实现分页排序遇异常求助

环境配置（build.gradle） #

plugins {
    id("org.jetbrains.kotlin.jvm") version "1.9.0"
    id("org.jetbrains.kotlin.plugin.allopen") version "1.9.0"
    id("org.jetbrains.kotlin.plugin.jpa") version "1.9.0"
    id("com.google.devtools.ksp") version "1.9.0-1.0.13"
    id("com.github.johnrengelman.shadow") version "8.1.1"
    id("io.micronaut.application") version "4.0.2"
    id("io.micronaut.aot") version "4.0.2"
}

version = "0.1"
group = "com.example"

val kotlinVersion = project.properties["kotlinVersion"]
repositories {
    mavenCentral()
}

dependencies {
    ksp("io.micronaut.data:micronaut-data-processor")
    ksp("io.micronaut.openapi:micronaut-openapi")
    ksp("io.micronaut.serde:micronaut-serde-processor")
    implementation("io.micronaut.serde:micronaut-serde-jackson")

    implementation("io.micronaut.reactor:micronaut-reactor")
    implementation("io.micronaut.data:micronaut-data-hibernate-reactive")
    implementation("io.micronaut.sql:micronaut-vertx-pg-client")
    implementation("com.ongres.scram:client:2.1")

    implementation("io.micronaut.xml:micronaut-jackson-xml")
    implementation("io.swagger.core.v3:swagger-annotations")
    implementation("org.jetbrains.kotlin:kotlin-reflect:${kotlinVersion}")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8:${kotlinVersion}")
    runtimeOnly("ch.qos.logback:logback-classic")
    runtimeOnly("com.fasterxml.jackson.module:jackson-module-kotlin")
    runtimeOnly("org.yaml:snakeyaml")
}


application {
    mainClass.set("com.example.ApplicationKt")
}
java {
    sourceCompatibility = JavaVersion.toVersion("17")
}

tasks {
    compileKotlin {
        compilerOptions {
            jvmTarget.set(org.jetbrains.kotlin.gradle.dsl.JvmTarget.JVM_17)
        }
    }
    compileTestKotlin {
        compilerOptions {
            jvmTarget.set(org.jetbrains.kotlin.gradle.dsl.JvmTarget.JVM_17)
        }
    }
}
graalvmNative.toolchainDetection.set(false)
micronaut {
    runtime("netty")
    testRuntime("junit5")
    processing {
        incremental(true)
        annotations("com.example.*")
    }
    aot {
        optimizeServiceLoading.set(false)
        convertYamlToJava.set(false)
        precomputeOperations.set(true)
        cacheEnvironment.set(true)
        optimizeClassLoading.set(true)
        deduceEnvironment.set(true)
        optimizeNetty.set(true)
    }
}

实体类 #

@Entity
@Serdeable
@Table(name = "roles")
data class RoleEntity(
        @Id
        @Column(name = "id")
        @JsonProperty("key")
        var roleId: String,

        @Column(name = "description")
        var description: String?
)

仓库接口 #

@Repository
interface RoleRepository : AbstractRepository<RoleEntity, String> {
    @Query(
        value = "from RoleEntity where lower(roleId) like concat('%', lower(:query), '%') or lower(description) like concat('%', lower(:query), '%')",
        countQuery = "select count(1) from RoleEntity where lower(roleId) like concat('%', lower(:query), '%') or lower(description) like concat('%', lower(:query), '%')"
    )
    override fun findByQuery(query: String, pageable: Pageable): Mono<Page<RoleEntity>>
}

注：AbstractRepository继承了ReactorPageableRepository和ReactorCrudRepository

分页参数解析代码 #

fun getPageable(request: HttpRequest<Any>): Pageable {
    val offset = request.parameters["offset"]?.toInt() ?: 0
    val pageSize = request.parameters["pageSize"]?.toInt() ?: 1
    val sorting = parseSorting(request)
    return Pageable.from(offset / pageSize, pageSize, sorting)
}

private fun parseSorting(request: HttpRequest<Any>): Sort {
    val sorting = mutableListOf<Sort.Order>()
    while (request.parameters["sorting[${sorting.count()}][field]"] != null) sorting.add(
        Sort.Order(
            request.parameters["sorting[${sorting.count()}][field]"],
            Sort.Order.Direction.valueOf(request.parameters["sorting[${sorting.count()}][order]"].uppercase()),
            true
        )
    )
    return Sort.of(sorting)
}

问题现象 #

当请求/roles?pageSize=3&sorting[0][field]=description&sorting[0][order]=asc时，生成的Pageable对象为：

Pageable(max=3, number=0, sort=DefaultSort(orderBy=[property = "description", direction = {Sort$Order$Direction@15418} "ASC", ignoreCase = true]))

但调用RoleRepository.findByQuery()时抛出异常：

org.hibernate.query.SemanticException: Could not interpret path expression 'roleEntity_.description'

如果传入不存在的字段参数，则抛出：

java.lang.IllegalArgumentException: Cannot sort on non-existent property path: desc

解决方案 #

问题出在自定义JPQL查询与Pageable排序的结合方式以及Sort.Order的ignoreCase参数使用，具体修复点如下：

• 给JPQL查询的实体添加别名
  你的JPQL查询语句没有给实体设置别名，当Micronaut Data自动拼接排序条件时，会使用默认的实体别名（比如roleEntity_），但查询中未定义该别名，导致Hibernate无法识别字段路径。

  修改仓库的@Query注解，为实体添加别名：

  @Query(
      value = "from RoleEntity re where lower(re.roleId) like concat('%', lower(:query), '%') or lower(re.description) like concat('%', lower(:query), '%')",
      countQuery = "select count(1) from RoleEntity re where lower(re.roleId) like concat('%', lower(:query), '%') or lower(re.description) like concat('%', lower(:query), '%')"
  )
  

• 调整ignoreCase参数的使用
  创建Sort.Order时设置了ignoreCase = true，但Hibernate对部分字段类型（如PostgreSQL的TEXT类型）进行忽略大小写排序时，需要显式使用lower()函数，而Micronaut Data的自动排序拼接不会处理这种场景。

  可选处理方式：

  1. 移除ignoreCase = true，确保前端传递的排序字段名与实体属性大小写一致，或者在参数解析时统一转换为实体属性的大小写格式；
  2. 手动在JPQL中拼接排序逻辑，不依赖Pageable的自动拼接（需注意SQL注入风险，优先使用Micronaut Data的动态查询能力）。

• 验证实体与数据库字段映射
  确认RoleEntity的description属性对应的数据库字段存在，且@Column(name = "description")的name值与实际数据库字段名完全匹配。

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