Spring Data JDBC实现数据库端分页排序与动态过滤方案问询

方案1：利用Spring Data SpEL表达式注入排序字段 #

Spring Data的@Query支持SpEL表达式（#{...}语法），可以直接将排序字段作为字符串注入SQL，避免参数绑定带来的引号问题。

代码实现 #

@Repository
public interface SomeRepository extends CrudRepository<SomeEntity, Long> {

    @Query(value = "SELECT * FROM some_table p WHERE p.some_column = :someValue " +
                   "ORDER BY #{#sortColumn} #{#sortDirection} LIMIT :pageSize OFFSET :offset",
           nativeQuery = true)
    List<SomeEntity> search(@Param("someValue") String someValue,
                            @Param("sortColumn") String sortColumn,
                            @Param("sortDirection") String sortDirection,
                            @Param("pageSize") int pageSize,
                            @Param("offset") long offset);
}

关键注意事项 #

• 使用#{#sortColumn}而非:sortColumn：SpEL会直接将参数值替换到SQL中，不会添加引号，确保排序字段被数据库正确识别。
• 必须做参数校验：在调用该方法的业务层，要校验sortColumn是否属于预定义的允许字段列表（比如["some_column", "other_column"]），sortDirection只能是ASC或DESC，防止SQL注入攻击。

方案2：用NamedParameterJdbcTemplate手动构建动态SQL（推荐复杂场景） #

如果需要支持多列动态过滤、更灵活的SQL控制，直接使用NamedParameterJdbcTemplate手动拼接SQL是更可靠的方案，完全避开Spring Data Repository的限制。

代码实现 #

@Repository
public class SomeRepositoryImpl {

    private final NamedParameterJdbcTemplate jdbcTemplate;
    // 预定义允许排序的字段白名单
    private static final List<String> ALLOWED_SORT_COLUMNS = Arrays.asList("some_column", "other_column", "create_time");
    private static final List<String> ALLOWED_SORT_DIRECTIONS = Arrays.asList("ASC", "DESC");

    public SomeRepositoryImpl(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<SomeEntity> search(String someValue, String sortColumn, String sortDirection, int pageSize, long offset) {
        // 校验并修正排序参数
        String safeSortColumn = ALLOWED_SORT_COLUMNS.contains(sortColumn) ? sortColumn : "some_column";
        String safeSortDirection = ALLOWED_SORT_DIRECTIONS.contains(sortDirection.toUpperCase()) ? sortDirection.toUpperCase() : "ASC";

        // 构建动态SQL
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM some_table p WHERE 1=1");
        Map<String, Object> params = new HashMap<>();

        // 添加动态过滤条件（可扩展多个条件）
        if (someValue != null && !someValue.isBlank()) {
            sqlBuilder.append(" AND p.some_column = :someValue");
            params.put("someValue", someValue);
        }
        // 示例：添加更多过滤条件
        // if (otherValue != null) {
        //     sqlBuilder.append(" AND p.other_column = :otherValue");
        //     params.put("otherValue", otherValue);
        // }

        // 拼接排序与分页
        sqlBuilder.append(" ORDER BY ").append(safeSortColumn).append(" ").append(safeSortDirection);
        sqlBuilder.append(" LIMIT :pageSize OFFSET :offset");
        params.put("pageSize", pageSize);
        params.put("offset", offset);

        // 执行查询并映射实体
        return jdbcTemplate.query(sqlBuilder.toString(), params, (rs, rowNum) -> {
            SomeEntity entity = new SomeEntity();
            entity.setId(rs.getLong("id"));
            entity.setSomeColumn(rs.getString("some_column"));
            // 映射其他字段...
            return entity;
        });
    }
}

方案优势 #

• 完全控制SQL生成逻辑，支持任意数量的动态过滤条件。
• 排序参数通过白名单校验，彻底杜绝SQL注入风险。
• 不受Spring Data Repository的限制，适配更复杂的业务场景。

通用注意事项 #

• 分页计算：offset需根据页码计算，公式为(pageNumber - 1) * pageSize，避免页码偏移错误。
• 参数校验：所有用户传入的排序、过滤参数都必须做合法性校验，禁止直接拼接未校验的用户输入到SQL中。

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