Spring Data JPA原生查询如何实现动态Where子句？

针对你的原生查询无法转HQL/Criteria，但需要动态添加范围、IN列表等条件且避免SQL注入的需求，以下是几个实用方案：

方案1：自定义Spring Data Repository实现（手动安全拼接） #

复用现有原生查询的基础上，手动拼接动态条件片段，通过参数绑定避免注入，同时处理分页逻辑。

首先定义自定义接口：

interface AreaRepositoryCustom {
    fun findNearby(lat: Double, lon: Double, pageable: Pageable, filter: AreaFilterDTO): Page<AreaSummary>
}

实现该接口：

class AreaRepositoryImpl : AreaRepositoryCustom {
    @PersistenceContext
    private lateinit var entityManager: EntityManager

    override fun findNearby(lat: Double, lon: Double, pageable: Pageable, filter: AreaFilterDTO): Page<AreaSummary> {
        // 从orm.xml提取的基础原生查询
        val baseSql = """
            SELECT a.id, a.name, a.population, a.house_type, ...
            FROM areas a
            WHERE ST_Distance_Sphere(a.location, POINT(:lon, :lat)) < 10000
        """.trimIndent()

        val sqlBuilder = StringBuilder(baseSql)
        val params = mutableMapOf<String, Any>()
        params["lat"] = lat
        params["lon"] = lon

        // 动态添加人口范围条件
        filter.minPopulation?.let {
            sqlBuilder.append(" AND a.population >= :minPop")
            params["minPop"] = it
        }
        filter.maxPopulation?.let {
            sqlBuilder.append(" AND a.population <= :maxPop")
            params["maxPop"] = it
        }

        // 动态添加房屋类型IN条件
        filter.houseType?.takeIf { it.isNotEmpty() }?.let { types ->
            sqlBuilder.append(" AND a.house_type IN (:houseTypes)")
            params["houseTypes"] = types.map { it.name } // 枚举转数据库存储值
        }

        // 处理总数查询
        val countSql = "SELECT COUNT(*) FROM ($sqlBuilder) t"
        val countQuery = entityManager.createNativeQuery(countSql)
        params.forEach { (key, value) -> countQuery.setParameter(key, value) }
        val total = (countQuery.singleResult as Long)

        // 处理分页和排序
        val finalSql = buildFinalSqlWithSort(sqlBuilder, pageable)
        val dataQuery = entityManager.createNativeQuery(finalSql, AreaSummary::class.java)
        params.forEach { (key, value) -> dataQuery.setParameter(key, value) }
        dataQuery.firstResult = pageable.pageNumber * pageable.pageSize
        dataQuery.maxResults = pageable.pageSize

        val results = dataQuery.resultList as List<AreaSummary>
        return PageImpl(results, pageable, total)
    }

    private fun buildFinalSqlWithSort(sqlBuilder: StringBuilder, pageable: Pageable): String {
        if (pageable.sort.isNotEmpty()) {
            sqlBuilder.append(" ORDER BY ")
            pageable.sort.forEachIndexed { index, order ->
                if (index > 0) sqlBuilder.append(", ")
                sqlBuilder.append("${order.property} ${order.direction}")
            }
        }
        return sqlBuilder.toString()
    }
}

让主Repository继承自定义接口：

interface AreaRepository : JpaRepository<Area, Long>, AreaRepositoryCustom

优点：完全复用原有原生查询，参数绑定彻底避免SQL注入，逻辑清晰易维护；缺点：需要手动处理分页和总数查询，代码量稍大。

方案2：QueryDSL SQL（类型安全动态构建） #

使用QueryDSL的SQL模块实现类型安全的原生查询动态构建，自动处理参数绑定，无需手动拼接字符串。

首先引入QueryDSL依赖（根据构建工具调整），通过插件自动生成对应数据库表的Q类。

实现查询逻辑：

@Service
class AreaQueryService {
    @Autowired
    private lateinit var sqlQueryFactory: SQLQueryFactory

    fun findNearby(lat: Double, lon: Double, pageable: Pageable, filter: AreaFilterDTO): Page<AreaSummary> {
        val a = QArea.area // QueryDSL自动生成的表实体

        // 基础查询逻辑
        var query = sqlQueryFactory.select(
            a.id, a.name, a.population, a.houseType
        ).from(a)
            .where(ST_DISTANCE_SPHERE(a.location, POINT(lon, lat)).lt(10000))

        // 动态添加条件
        filter.minPopulation?.let { query = query.where(a.population.goe(it)) }
        filter.maxPopulation?.let { query = query.where(a.population.loe(it)) }
        filter.houseType?.takeIf { it.isNotEmpty() }?.let { types ->
            query = query.where(a.houseType.`in`(types.map { it.name }))
        }

        // 处理分页和总数
        val total = query.fetchCount()
        val results = query.offset(pageable.offset)
            .limit(pageable.pageSize.toLong())
            .fetch()
            .map {
                AreaSummary(it.get(a.id), it.get(a.name), it.get(a.population), it.get(a.houseType))
            }

        return PageImpl(results, pageable, total)
    }
}

优点：类型安全，避免手动拼接SQL出错，自动参数绑定防注入；缺点：需要额外配置QueryDSL依赖和代码生成插件。

方案3：@Query结合SpEL表达式（轻量动态拼接） #

如果动态条件不多，可直接在Spring Data的@Query注解中用SpEL表达式动态拼接SQL片段，同时保持参数绑定。

修改Repository接口：

interface AreaRepository : JpaRepository<Area, Long> {
    @Query(nativeQuery = true, value = """
        SELECT a.id, a.name, a.population, a.house_type, ...
        FROM areas a
        WHERE ST_Distance_Sphere(a.location, POINT(:longitude, :latitude)) < 10000
        #{#filter.minPopulation != null ? ' AND a.population >= :minPop' : ''}
        #{#filter.maxPopulation != null ? ' AND a.population <= :maxPop' : ''}
        #{#filter.houseType != null and !#filter.houseType.isEmpty() ? ' AND a.house_type IN (:houseTypes)' : ''}
        #{#pageable.sort.isNotEmpty() ? ' ORDER BY ' + #pageable.sort : ''}
    """, countQuery = """
        SELECT COUNT(*)
        FROM areas a
        WHERE ST_Distance_Sphere(a.location, POINT(:longitude, :latitude)) < 10000
        #{#filter.minPopulation != null ? ' AND a.population >= :minPop' : ''}
        #{#filter.maxPopulation != null ? ' AND a.population <= :maxPop' : ''}
        #{#filter.houseType != null and !#filter.houseType.isEmpty() ? ' AND a.house_type IN (:houseTypes)' : ''}
    """)
    fun findNearby(
        @Param("latitude") lat: Double,
        @Param("longitude") lon: Double,
        @Param("filter") filter: AreaFilterDTO,
        @Param("minPop") minPop: Int? = filter.minPopulation,
        @Param("maxPop") maxPop: Int? = filter.maxPopulation,
        @Param("houseTypes") houseTypes: List<String>? = filter.houseType?.map { it.name },
        pageable: Pageable
    ): Page<AreaSummary>
}

优点：代码简洁，无需额外实现类；缺点：动态条件过多时SQL可读性差，SpEL表达式调试难度略高。

方案4：Hibernate NativeQuery手动构建 #

直接使用Hibernate的Session创建NativeQuery，手动拼接条件并绑定参数，完全控制查询流程：

@Service
class AreaService {
    @PersistenceContext
    private lateinit var session: Session

    fun findNearby(lat: Double, lon: Double, pageable: Pageable, filter: AreaFilterDTO): Page<AreaSummary> {
        val baseSql = """
            SELECT a.id, a.name, a.population, a.house_type, ...
            FROM areas a
            WHERE ST_Distance_Sphere(a.location, POINT(:lon, :lat)) < 10000
        """.trimIndent()

        val sqlBuilder = StringBuilder(baseSql)
        val query = session.createNativeQuery(sqlBuilder.toString(), AreaSummary::class.java)
        query.setParameter("lat", lat)
        query.setParameter("lon", lon)

        // 动态添加条件
        filter.minPopulation?.let {
            sqlBuilder.append(" AND a.population >= :minPop")
            query.setParameter("minPop", it)
        }
        filter.maxPopulation?.let {
            sqlBuilder.append(" AND a.population <= :maxPop")
            query.setParameter("maxPop", it)
        }
        filter.houseType?.takeIf { it.isNotEmpty() }?.let { types ->
            sqlBuilder.append(" AND a.house_type IN (:houseTypes)")
            query.setParameter("houseTypes", types.map { it.name })
        }

        // 处理排序
        if (pageable.sort.isNotEmpty()) {
            sqlBuilder.append(" ORDER BY ")
            pageable.sort.forEachIndexed { index, order ->
                if (index > 0) sqlBuilder.append(", ")
                sqlBuilder.append("${order.property} ${order.direction}")
            }
        }

        // 处理分页
        query.firstResult = pageable.pageNumber * pageable.pageSize
        query.maxResults = pageable.pageSize

        // 统计总数
        val countSql = "SELECT COUNT(*) FROM ($sqlBuilder) t"
        val countQuery = session.createNativeQuery(countSql)
        // 复用参数绑定
        query.parameters.parameters.forEach { param ->
            countQuery.setParameter(param.name, param.value)
        }
        val total = (countQuery.uniqueResult() as Long)

        val results = query.list() as List<AreaSummary>
        return PageImpl(results, pageable, total)
    }
}

优点：完全自定义查询逻辑，适合极复杂的原生查询场景；缺点：需要手动处理参数复用和分页，代码冗余度较高。

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