CODEWITHSUNDEEP
javasqlspringspring-bootspring-data-jpa
Karl Doenitz
Karl Doenitz

Reputation: 2230

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 .

Upvotes: 1

Views: 1742

Answers (2)

Josh Ghiloni
Josh Ghiloni

Reputation: 1300

With spring-data-jpa, you need only use the org.springframework.data.domain.Page return type and org.springframework.data.domain.Pageable method parameter.

@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")
    Page<Report> findByCondition(
        @Param("startTime") long startTime,
        @Param("endTime") long endTime,
        @Param("name") String name,
        @Param("status") int[] status,
        Pageable pageParameters
    );
}

spring-data-jpa recognizes that return type and will marshal your data accordingly. The various members of Pageable will help you do limiting and offsets. Note that the ORDER BY clause in your query may conflict with the sorting options in the Pageable.

Upvotes: 1

degr
degr

Reputation: 1565

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);

Upvotes: 0

Related Questions