I have a simple Spring rest webservice endpoint that exposes the result of a myqsl query as json:
@RestController
public class DbController {
@GetMapping(produces = APPLICATION_JSON_VALUE)
public List<Map<String, Object>> query() {
return crudRepository.queryForList(sql);
}
}
Problem: for large queries returning like 500k objects (means: rows), this consumes a lot of memory.
Is there any way I could write "partial" json results out to the response stream?
You can use Pagination to create a chunk of data for client per request.
You can do something like this:
Repository:
@Repository
public interface SomethingRepository extends PagingAndSortingRepository<Something, Long> {
Page<Something> findByMember_Id(Long memberId, Pageable pageable);
}
SomethingPageDto:
public class SomethingPageDto{
@JsonProperty("favourite_list")
private List<SomethingDto> somethings= new ArrayList<>();
@JsonProperty("total_pages")
private int totalPages;
@JsonProperty("current_page")
private int currentPage;
@JsonProperty("total_elements")
private Long totalElements;
@JsonProperty("current_page_elements")
private int currentPageElement;
}
Service:
@Autowired
private SomethingRepository repository;
public SomethingPageDTO getSomething(int pageNo, int pageSize, Long memberId) throws Exception {
Pageable paging = new PageRequest(pageNo, pageSize);
Page<Something> somethingPage = repository.findByMember_Id(memberId, paging);
//map EntityList to DTO
List<SomethingPageDTO > somethingDtos = new ArrayList<>();
return somethingDtos = somethingPage.getContent();
//somethingPage.getTotalElements();
//somethingPage.getTotalPages();
}
Controller:
@RestController
@RequestMapping("/some")
public class EmployeeController
{
@Autowired
somethingService service;
@GetMapping
public ResponseEntity<List<Something>> getAll(
@RequestParam Integer pageNo,
@RequestParam Integer pageSize,
@RequestParam Long memberId
{
List<somethingDto> list = service.getSomething(pageNo, pageSize, memberId);
return new ResponseEntity<List<SomethingDto>>(list, new HttpHeaders(), HttpStatus.OK);
}
}
more resources at:
https://dzone.com/articles/pagination-in-springboot-applications
https://howtodoinjava.com/spring-boot2/pagination-sorting-example/