Spring Boot中JPA Repository多条件分页查询实现方案

我定义了如下Attempt实体：

@Entity
public class Attempt {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private LocalDateTime attemptTime;
    private long processingTimeNanos;
    private double x;
    private double y;
    private double r;
    private boolean result;

    public Attempt() {
    }

    public Attempt(double x, double y, double r, boolean result) {
        this.x = x;
        this.y = y;
        this.r = r;
        this.result = result;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public double getX() {
        return x;
    }

    public void setX(double x) {
        this.x = x;
    }

    public double getY() {
        return y;
    }

    public void setY(double y) {
        this.y = y;
    }

    public double getR() {
        return r;
    }

    public void setR(double r) {
        this.r = r;
    }

    public boolean isResult() {
        return result;
    }

    public void setResult(boolean result) {
        this.result = result;
    }

    public LocalDateTime getAttemptTime() {
        return attemptTime;
    }

    public void setAttemptTime(LocalDateTime attemptTime) {
        this.attemptTime = attemptTime;
    }

    public long getProcessingTimeNanos() {
        return processingTimeNanos;
    }

    public void setProcessingTimeNanos(long processingTime) {
        this.processingTimeNanos = processingTime;
    }
}

我希望在Spring Boot的JPA Repository中实现一个支持任意数量字段搜索，同时可指定offset和limit的查询方法，编写了如下Repository代码：

@Repository
public interface AttemptsRepository extends JpaRepository<Attempt, Integer> {
    /**
     * Makes a search in the database by the given parameters with the given offset and size
     * @param offset
     * @param size
     * @param id
     * @param x
     * @param y
     * @param r
     * @param result
     * @param time
     * @param processingTime
     * @return
     */
    @Query("""
        select A from Attempt A
        where (?3 is null or A.id like %?1%)
        and (?4 is null or A.x like %?4%)
        and (?5 is null or A.y like %?5%)
        and (?6 is null or A.r like %?6%)
        and (?7 is null or A.result like %?7%)
        and (?8 is null or A.attemptTime like %?8%)
        and (?9 is null or A.processingTimeNanos like %?9%)
        order by A.id offset ?1 rows fetch next ?2 rows only
        """)
        List<Attempt> getPartAttempts(int offset, int size, String id, String x, String y, String r, String result, String time, String processingTime);
}

执行时抛出异常，使用的数据库是MySQL，请问该如何正确实现这类查询方法？

你的代码存在参数索引错误、类型不匹配、MySQL分页语法不兼容三个核心问题，以下是两种可行的修正方案：

方案一：修正原生@Query查询（适配MySQL） #

使用命名参数避免索引混乱，针对不同字段类型调整匹配逻辑，替换为MySQL支持的分页语法：

@Repository
public interface AttemptsRepository extends JpaRepository<Attempt, Integer> {
    @Query(value = """
        select * from attempt a
        where (:id is null or a.id = :id)
        and (:x is null or a.x = :x)
        and (:y is null or a.y = :y)
        and (:r is null or a.r = :r)
        and (:result is null or a.result = :result)
        and (:time is null or date_format(a.attempt_time, '%Y-%m-%d %H:%i:%s') like concat('%', :time, '%'))
        and (:processingTime is null or a.processing_time_nanos = :processingTime)
        order by a.id limit :offset, :size
        """, nativeQuery = true)
    List<Attempt> getPartAttempts(
            @Param("offset") int offset,
            @Param("size") int size,
            @Param("id") Integer id,
            @Param("x") Double x,
            @Param("y") Double y,
            @Param("r") Double r,
            @Param("result") Boolean result,
            @Param("time") String time,
            @Param("processingTime") Long processingTime
    );
}

关键说明： #

• 用@Param绑定命名参数，彻底避免位置参数索引混乱问题
• 数字、布尔类型使用等值匹配，若需模糊匹配可转为字符串（例如cast(a.x as char) like concat('%', :x, '%')）
• 日期类型通过date_format函数转为字符串后支持模糊搜索
• 采用MySQL标准分页语法limit offset, size替代原生SQL的fetch next

方案二：使用Specification动态查询（更灵活） #

这种方式无需硬编码SQL，可动态组合查询条件，扩展性更强：

1. 修改Repository接口 #

@Repository
public interface AttemptsRepository extends JpaRepository<Attempt, Integer>, JpaSpecificationExecutor<Attempt> {
}

2. 业务层实现动态查询逻辑 #

@Service
public class AttemptService {
    @Autowired
    private AttemptsRepository repository;

    public List<Attempt> searchAttempts(int offset, int size, Integer id, Double x, Double y, Double r, Boolean result, String time, Long processingTime) {
        Specification<Attempt> spec = (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = new ArrayList<>();
            // 按条件添加查询规则
            if (id != null) {
                predicates.add(criteriaBuilder.equal(root.get("id"), id));
            }
            if (x != null) {
                predicates.add(criteriaBuilder.equal(root.get("x"), x));
                // 若需模糊匹配x，替换为：
                // predicates.add(criteriaBuilder.like(criteriaBuilder.toString(root.get("x")), "%" + x + "%"));
            }
            if (y != null) {
                predicates.add(criteriaBuilder.equal(root.get("y"), y));
            }
            if (r != null) {
                predicates.add(criteriaBuilder.equal(root.get("r"), r));
            }
            if (result != null) {
                predicates.add(criteriaBuilder.equal(root.get("result"), result));
            }
            if (time != null) {
                // 模糊匹配日期字符串
                Expression<String> dateStr = criteriaBuilder.function("date_format", String.class, root.get("attemptTime"), criteriaBuilder.literal("%Y-%m-%d %H:%i:%s"));
                predicates.add(criteriaBuilder.like(dateStr, "%" + time + "%"));
            }
            if (processingTime != null) {
                predicates.add(criteriaBuilder.equal(root.get("processingTimeNanos"), processingTime));
            }
            return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
        };
        // 直接使用offset和size分页（Spring Data JPA 2.0+支持）
        return repository.findAll(spec, Sort.by("id").ascending(), offset, size);
    }
}

关键说明： #

• 实现JpaSpecificationExecutor接口获得动态查询能力
• 仅当参数不为null时才添加对应查询条件，天然支持任意字段组合搜索
• 分页可直接指定offset和size，无需转换页码

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