view raw
Karl Doenitz Karl Doenitz - 8 months ago 102
SQL Question

Spring boot use jpa sql and pageable

I am a freshman with java.I wanna select data from database and use Pageable.And I must use hql in jpa.

The codes in

public class ReportController {

private static final Logger logger = LoggerFactory.getLogger(ReportController.class);
private static Validator validator;

private ReportService reportService;

protected void initBinder(WebDataBinder binder) {
ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
validator = factory.getValidator();

@RequestMapping(value = "/reports", method = RequestMethod.POST, produces = MediaTypes.JSON_UTF_8)
public JSONObject getReports(@RequestBody ReportQueryRequest reportQueryRequest){"[Monitor List] partial / Params: {}", reportQueryRequest);
Set<ConstraintViolation<ReportQueryRequest>> errors = validator.validate(reportQueryRequest);
String name = reportQueryRequest.getReportName();
int status = reportQueryRequest.getReportStatus();
String createTime = reportQueryRequest.getCreateTime();
int statusArray[];
if (status == 3) {
statusArray = new int[2];
statusArray[0] = 0;
statusArray[1] = 1;
}else {
statusArray = new int[1];
statusArray[0] = status;
if (name == null){
name = "";
if (createTime == null || !createTime.contains(",")){
return ResponseWrapper.buildResponse(RTCodeEnum.C_PARAM_ERROR, "createTime error");
createTime = createTime.replace(" ", "");
String[] createTimeArray = createTime.split(",");
long startTime = Long.valueOf(createTimeArray[0]);
long endTime = Long.valueOf(createTimeArray[1]);
List<Report> reportList = reportService.getReportList(startTime, endTime, name, statusArray);
return ResponseWrapper.buildResponse(RTCodeEnum.C_OK, reportList);

The codes in

public class ReportService extends CrudService<Report, ReportRepository> {
public void setRepo(ReportRepository repo) {
this.repo = repo;

public Report copy(Report from, Report to) {
to = from;
return to;

private ReportRepository reportRepository;

public List<Report> getReportList(long startTime, long endTime, String name, int[] status) {
return reportRepository.findByCondition(startTime, endTime, name, status);

The codes in

public interface ReportRepository extends CrudRepository<Report,Long>{
@Query(value = "select r from Report r where r.createTime >= :startTime and r.createTime <= :endTime and like %:name% and r.status in :status order by r.updateTime DESC")
List<Report> findByCondition(
@Param("startTime") long startTime,
@Param("endTime") long endTime,
@Param("name") String name,
@Param("status") int[] status

My program run successfully. Now, I wanna use
but I don't know how to page on the data return from the method named


How about this solution:


public class MvcConfig extends WebMvcConfigurerAdapter {

    public void addArgumentResolvers(List<HandlerMethodArgumentResolver> argumentResolvers) {
        PageableHandlerMethodArgumentResolver resolver = new PageableHandlerMethodArgumentResolver();


@RequestMapping(value = "/getAll", method = RequestMethod.GET)
    public List<YourObjects> allBatches(Pageable pageable) {
        return service.getAllBatches(pageable);


 public List<YourObjects> findAll(Pageable pageable) {
        return dao.findAll(pageable.getOffset(), pageable.getPageSize());


@Query(value = "select * from table limit :offset, :pageSize, nativeQuery = true)
List<YourObjects> findAll(@Param("offset") Integer offset, @Param("pageSize") Integer pageSize);