Spring Data JPA动态选择列：多表无映射关联场景的实现方案

针对你遇到的重复编写SQL的问题，以下是几种可行的优化方案，适配你Spring Boot 2.7.6 + Kotlin的技术栈，且支持多表原生SQL查询场景：

方案1：利用Spring Data JPA的SpEL表达式动态替换列 #

通过SpEL表达式在@Query中动态替换WHERE子句的日期列，复用整个SQL结构，仅动态修改条件部分。

代码示例 #

@Repository
interface EmployeeRepository : JpaRepository<Employee, Long> {

    @Query(
        """ 
        select  
                t.a as A,
                t.b as B,
                tt.c as C,
                p.d as D,
                p.e as E
            from Employee p
                join Department t on p.some_id = t.id
                join PersonalData tt on tt.id = t.some_id
                left outer join SalaryInformation ps on p.id = ps.come_id
                left outer join ManagerInformation sbt on p.some_id = sbt.id
                -- 其他连接语句
            where p.id = :employeeId 
              and p.#{#dateType} >= :dateFrom 
              and p.#{#dateType} <= :dateTo
        """,
        nativeQuery = true
    )
    fun findByEmployeeIdAndDateRange(
        employeeId: Long,
        dateType: String,
        dateFrom: String,
        dateTo: String,
        pageable: Pageable
    ): Slice<EmployeeDetailsProjection>
}

注意事项 #

1. 列名校验与转换：在Service层先校验dateType是否属于允许的列表（interviewDate/joiningDate/resignationDate/lastWorkingDate），避免非法输入。如果数据库列是下划线命名（比如interview_date），需要把驼峰格式的dateType转换为下划线格式，例如：

   val dbColumnName = dateType.replaceFirstChar { it.lowercase() }.replace("Date", "_date")
   

2. 防SQL注入：通过参数校验确保dateType是合法列名，杜绝恶意输入。

方案2：自定义Repository实现，手动拼接SQL #

如果SpEL的灵活性不足，可自定义Repository实现类，手动控制SQL的生成逻辑，适合复杂的动态场景。

步骤示例 #

1. 定义基础Repository接口

interface EmployeeRepository : JpaRepository<Employee, Long>, EmployeeCustomRepository

2. 定义自定义查询接口

interface EmployeeCustomRepository {
    fun findByEmployeeIdAndDateRange(
        employeeId: Long,
        dateType: String,
        dateFrom: String,
        dateTo: String,
        pageable: Pageable
    ): Slice<EmployeeDetailsProjection>
}

3. 实现自定义接口

@Repository
class EmployeeCustomRepositoryImpl(
    @Autowired private val entityManager: EntityManager
) : EmployeeCustomRepository {

    override fun findByEmployeeIdAndDateRange(
        employeeId: Long,
        dateType: String,
        dateFrom: String,
        dateTo: String,
        pageable: Pageable
    ): Slice<EmployeeDetailsProjection> {
        // 校验dateType合法性
        val allowedDateTypes = setOf("interviewDate", "joiningDate", "resignationDate", "lastWorkingDate")
        require(dateType in allowedDateTypes) { "无效的dateType参数: $dateType" }

        // 转换为数据库下划线列名
        val dbColumnName = dateType.replaceFirstChar { it.lowercase() }.replace("Date", "_date")

        // 构建完整SQL
        val sql = """
            select  
                t.a as A,
                t.b as B,
                tt.c as C,
                p.d as D,
                p.e as E
            from Employee p
                join Department t on p.some_id = t.id
                join PersonalData tt on tt.id = t.some_id
                left outer join SalaryInformation ps on p.id = ps.come_id
                left outer join ManagerInformation sbt on p.some_id = sbt.id
                -- 其他连接语句
            where p.id = :employeeId 
              and p.$dbColumnName >= :dateFrom 
              and p.$dbColumnName <= :dateTo
        """.trimIndent()

        // 执行查询并处理分页
        val query = entityManager.createNativeQuery(sql, EmployeeDetailsProjection::class.java)
            .setParameter("employeeId", employeeId)
            .setParameter("dateFrom", dateFrom)
            .setParameter("dateTo", dateTo)
            .setFirstResult(pageable.pageNumber * pageable.pageSize)
            .setMaxResults(pageable.pageSize)

        val content = query.resultList as List<EmployeeDetailsProjection>
        // 判断是否有下一页
        val totalCount = entityManager.createNativeQuery("select count(1) from ($sql) as cnt")
            .setParameter("employeeId", employeeId)
            .setParameter("dateFrom", dateFrom)
            .setParameter("dateTo", dateTo)
            .singleResult as Long
        val hasNext = (pageable.pageNumber + 1) * pageable.pageSize < totalCount

        return SliceImpl(content, pageable, hasNext)
    }
}

方案3：使用Querydsl SQL构建类型安全的动态查询 #

Querydsl支持类型安全的原生SQL构建，避免字符串拼接错误，同时提供灵活的动态条件支持。

步骤示例 #

1. 引入依赖（build.gradle.kts）

dependencies {
    implementation("com.querydsl:querydsl-sql:5.0.0")
    kapt("com.querydsl:querydsl-sql-codegen:5.0.0")
}

// 配置代码生成，生成对应数据库表的Q类
kapt {
    arguments {
        arg("querydsl.sql.entities", true)
        arg("querydsl.sql.schema", "public") // 替换为你的数据库schema
        arg("querydsl.sql.packageName", "com.yourpackage.querydsl") // Q类生成路径
    }
}

2. 编写动态查询代码

@Repository
class EmployeeQuerydslRepository(
    @Autowired private val sqlQueryFactory: SQLQueryFactory
) {

    fun findByEmployeeIdAndDateRange(
        employeeId: Long,
        dateType: String,
        dateFrom: LocalDate,
        dateTo: LocalDate,
        pageable: Pageable
    ): Slice<EmployeeDetailsProjection> {
        // 映射dateType到Querydsl的列对象
        val dateColumnMap = mapOf(
            "interviewDate" to QEmployee.employee.interviewDate,
            "joiningDate" to QEmployee.employee.joiningDate,
            "resignationDate" to QEmployee.employee.resignationDate,
            "lastWorkingDate" to QEmployee.employee.lastWorkingDate
        )
        val dateColumn = requireNotNull(dateColumnMap[dateType]) { "无效的dateType参数: $dateType" }

        // 构建查询
        val query = sqlQueryFactory.select(
            QDepartment.department.a.`as`("A"),
            QDepartment.department.b.`as`("B"),
            QPersonalData.personalData.c.`as`("C"),
            QEmployee.employee.d.`as`("D"),
            QEmployee.employee.e.`as`("E")
        )
            .from(QEmployee.employee)
            .innerJoin(QDepartment.department).on(QEmployee.employee.someId.eq(QDepartment.department.id))
            .innerJoin(QPersonalData.personalData).on(QPersonalData.personalData.id.eq(QDepartment.department.someId))
            .leftJoin(QSalaryInformation.salaryInformation).on(QEmployee.employee.id.eq(QSalaryInformation.salaryInformation.comeId))
            .leftJoin(QManagerInformation.managerInformation).on(QEmployee.employee.someId.eq(QManagerInformation.managerInformation.id))
            .where(
                QEmployee.employee.id.eq(employeeId),
                dateColumn.goe(dateFrom),
                dateColumn.loe(dateTo)
            )
            .offset(pageable.offset)
            .limit(pageable.pageSize.toLong())

        // 映射结果到Projection
        val content = query.fetch().map { tuple ->
            EmployeeDetailsProjection(
                A = tuple.get(QDepartment.department.a),
                B = tuple.get(QDepartment.department.b),
                C = tuple.get(QPersonalData.personalData.c),
                D = tuple.get(QEmployee.employee.d),
                E = tuple.get(QEmployee.employee.e)
            )
        }

        // 计算总条数判断是否有下一页
        val totalCount = sqlQueryFactory.select(QEmployee.employee.id.count())
            .from(QEmployee.employee)
            .innerJoin(QDepartment.department).on(QEmployee.employee.someId.eq(QDepartment.department.id))
            .innerJoin(QPersonalData.personalData).on(QPersonalData.personalData.id.eq(QDepartment.department.someId))
            .leftJoin(QSalaryInformation.salaryInformation).on(QEmployee.employee.id.eq(QSalaryInformation.salaryInformation.comeId))
            .leftJoin(QManagerInformation.managerInformation).on(QEmployee.employee.someId.eq(QManagerInformation.managerInformation.id))
            .where(
                QEmployee.employee.id.eq(employeeId),
                dateColumn.goe(dateFrom),
                dateColumn.loe(dateTo)
            )
            .fetchOne() ?: 0L
        val hasNext = (pageable.offset + pageable.pageSize) < totalCount

        return SliceImpl(content, pageable, hasNext)
    }
}

方案选择建议 #

• 若场景简单，优先用方案1（SpEL表达式），代码量最少，复用性最高；
• 若需要复杂的动态逻辑（比如动态增减连接表），选择方案2（自定义Repository）；
• 若追求类型安全、可维护性，且愿意引入额外依赖，推荐方案3（Querydsl）。

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