Getting paginated data from Cassandra database in Spring Boot application can be a huge challenge, if you want to do the things right.
DataStax Java driver supports data paging, up to some extent. Using the native paging support via cluster configuration might look like this:
// At initialization:
Cluster cluster = Cluster.builder()
.addContactPoint("127.0.0.1")
.withQueryOptions(new QueryOptions().setFetchSize(2000))
.build();
// Or at runtime:
cluster.getConfiguration().getQueryOptions().setFetchSize(2000);
The fetch size can also be set on a statement:
Statement statement = new SimpleStatement("your query");
statement.setFetchSize(2000);
If not set, the fetch size defaults to 5000.
Result set iteration is done this way:
ResultSet rs = session.execute("your query");
for (Row row : rs) {
if (rs.getAvailableWithoutFetching() == 100 && !rs.isFullyFetched())
rs.fetchMoreResults(); // this is asynchronous
// Process the row ...
System.out.println(row);
}
Now comes the part which usually gives you a headache, if you want to allow your API service call to collect and return Pageable data.
Native DataStax Java driver allows saving and reusing paging state:
ResultSet resultSet = session.execute("your query");
// iterate the result set...
PagingState pagingState = resultSet.getExecutionInfo().getPagingState();
String string = pagingState.toString();
byte[] bytes = pagingState.toBytes();
The URL to the next page is passed as http://someservice.com/results?page=<...>. The state value can be deserialised later and used in a query:
PagingState pagingState = PagingState.fromString(string);
Statement st = new SimpleStatement("your query");
st.setPagingState(pagingState);
ResultSet rs = session.execute(st);
Now, if you want to allow the frontend application to render a nice pagination component like this, the native state exchanging mechanism simply won't be enough:
We need to keep track of paging state in terms of page, size and total properties, required by frontend application.
This problem can be bypassed by creating two simple utility Java classes. The first one serves as a class to contain paging states and total number of rows, while the second one does the actual pagination:
package com.acme.library.utils;
import com.datastax.driver.core.PagingState;
import java.util.HashMap;
import java.util.Map;
public class PagingStats {
private Map<Integer, PagingState> states = new HashMap<>();
private Integer total = 0;
private PagingStats() {
}
private PagingStats(Map<Integer, PagingState> states, Integer total) {
this.states = states;
this.total = total;
}
public static PagingStats createStats(Map<Integer, PagingState> state, Integer total)
{
return new PagingStats(state, total);
}
public Map<Integer, PagingState> getStates() {
return states;
}
public void setStates(Map<Integer, PagingState> states) {
this.states = states;
}
public Integer getTotal() {
return total;
}
public void setTotal(Integer total) {
this.total = total;
}
}
package com.acme.library.utils;
import com.datastax.driver.core.PagingState;
import com.datastax.driver.core.ResultSet;
import com.datastax.driver.core.Session;
import com.datastax.driver.core.Statement;
import com.datastax.driver.mapping.Mapper;
import com.datastax.driver.mapping.Result;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import java.util.*;
public class PagingUtil {
private final static Logger log = LoggerFactory.getLogger(PagingUtil.class);
/**
*
* Retrieve Page of database rows for the specified pageable param.
*
* @param mapper mapper
* @param session session
* @param select select
* @param pageable pageable
* @return Page
* @param <T> T
*/
public static <T> Page<T> getPage(Mapper<T> mapper, Session session, Statement select, Pageable pageable) {
try {
return getAll(mapper, session, select, pageable);
} catch (Exception e) {
log.error(String.format("Error getting pageable data: %s", e.getMessage()));
return new PageImpl<>(new ArrayList<>(0), pageable, 0);
}
}
/**
* Retrieve paging stats with states and total
*
* @param session session
* @param select select statement
* @param size size
* @return PagingStats
*/
private static PagingStats getPagingStats(Session session, Statement select, Integer size) {
// Prepare stats variables
Map<Integer, PagingState> states = new HashMap<>();
PagingState state = null;
int n = 0;
int total = 0;
do {
// Get the result
select.setFetchSize(size).setPagingState(state);
ResultSet result = session.execute(select);
// Get stats values
state = result.getExecutionInfo().getPagingState();
total += result.getAvailableWithoutFetching();
if(state != null) {
states.put(++n, state);
}
} while (state != null);
// Return stats data
return PagingStats.createStats(states, total);
}
/**
* Map data rows
*
* @param mapper mapper
* @param session session
* @param statement select statement
* @return List
* @param <T> T
*/
private static <T> Result<T> mapRows(Mapper<T> mapper, Session session, Statement statement) {
// Get paged results
ResultSet results = session.execute(statement);
// Return results
return mapper.map(results);
}
/**
* Get all paged data
*
* @param mapper mapper
* @param session session
* @param select select session
* @param pageable pageable
* @return Page
* @param <T> T
*/
private static <T> Page<T> getAll(Mapper<T> mapper, Session session, Statement select, Pageable pageable) {
// Get paging stats
PagingStats stats = getPagingStats(session, select, pageable.getPageSize());
// Check for page validity
int start = pageable.getPageNumber() * pageable.getPageSize();
if (start > stats.getTotal()) {
return new PageImpl<>(new ArrayList<>(), pageable, 0L);
}
// Prepare command parameters
select.setPagingState(stats.getStates().get(pageable.getPageNumber()));
// Get and map rows
Result<T> rows = mapRows(mapper, session, select);
// Return paged rows
return new PageImpl<>(rows.all(), pageable, stats.getTotal());
}
}
The star of the show is the getPagingStats(…) method, which prepares all potential states for a given Pageable parameter all at once, keeping track of total number of rows too.
Then, the getPage(…) method does the actual pagination, executing the given Select statement for the open Session and Pageable parameter.
The Select statement gets the exact paginated data by looking up the hashed state, depending on given Pageable parameter.
The paginated data are mapped from raw data to entity format using given Mapper<…>.
The PagingUtil class is used like this in repository layer:
private Mapper<Action> mapper = new MappingManager(session).mapper(Action.class);
private PreparedStatement findAllStmt = session.prepare("SELECT * FROM action");
private Page<Action> getPageFromSelect(BoundStatement select, Pageable pageable) {
return PagingUtil.getPage(mapper, session, select, pageable);
}
public Page<Action> findAllPageable(Pageable pageable) {
return getPageFromSelect(findAllStmt.bind(), pageable);
}
Service layer might look like this:
public Page<Action> findAllPageable(Pageable pageable) {
return actionRepository.findAllPageable(pageable);
}
Then in REST controller:
/**
* GET /action/pageable : get all actions pageable.
*
* @param pageable the pagination information
* @return the ResponseEntity with status 200 (OK) and with body all actions
*/
@GetMapping("/action/pageable")
@Protected
@Audited
@ApiOperation(value = "Get all actions paged", response = Action.class, responseContainer = "List", authorizations = { @Authorization(value = "token"), @Authorization(value = "secret"), @Authorization(value = "basic") })
public ResponseEntity<List<Action>> getAllActionsPageable(@Param Pageable pageable) {
Page<Action> page = actionService.findAllPageable(pageable);
HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(page, "/action/pageable");
return new ResponseEntity<> (page.getContent(), headers, HttpStatus.OK);
}
REST API call executed from frontend client can have this format:
http://someservice.com/results/api/action/pageable?page=4&size=10
with the following json response:
[
{
"id": 41,
"name": "getAllRoles"
},
{
"id": 42,
"name": "getAllUsers"
},
...
]
The response headers are used for sending paging data to a client:
Link:
</action/pageable?page=5&size=10>; rel="next",</action/pageable?page=3&size=10>; rel="prev",</action/pageable?page=31&size=10>; rel="last",</action/pageable?page=0&size=10>; rel="first"
Total:
311
These paging data can finally be used to update pagination component:
This utility has been tested with ~100k records and works blazingly fast.
Top comments (0)