Karl Doenitz Karl Doenitz - 3 months ago 32
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 RestController.java:

@RestController
@RequestMapping("/api")
public class ReportController {

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

@Autowired
private ReportService reportService;

@InitBinder
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){
logger.info("[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 Service.java:

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

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

@Autowired
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 Repository.java:

@Repository
public interface ReportRepository extends CrudRepository<Report,Long>{
@Query(value = "select r from Report r where r.createTime >= :startTime and r.createTime <= :endTime and r.name 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
Pageable
but I don't know how to page on the data return from the method named
findByCondition
.

Answer

How about this solution:

resolvers:

@Configuration
public class MvcConfig extends WebMvcConfigurerAdapter {

    @Override
    public void addArgumentResolvers(List<HandlerMethodArgumentResolver> argumentResolvers) {
        PageableHandlerMethodArgumentResolver resolver = new PageableHandlerMethodArgumentResolver();
        argumentResolvers.add(resolver);
    }
}

controller

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

service

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

dao:

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