Cursors and streaming
At the time of writing, you cannot use the following features if you connect to your database through ProxySQL. If you try, you will see this error message: RECEIVED AN UNKNOWN COMMAND: 28 -- PLEASE REPORT A BUG This is because the proxy does not handle a type of command ( |
By default, prepared query execution fetches all rows, you can use a
Cursor
to control the amount of rows you want to read:
connection
.prepare("SELECT * FROM users WHERE age > ?")
.onComplete(ar1 -> {
if (ar1.succeeded()) {
PreparedStatement pq = ar1.result();
// Create a cursor
Cursor cursor = pq.cursor(Tuple.of(18));
// Read 50 rows
cursor
.read(50)
.onComplete(ar2 -> {
if (ar2.succeeded()) {
RowSet<Row> rows = ar2.result();
// Check for more ?
if (cursor.hasMore()) {
// Repeat the process...
} else {
// No more rows - close the cursor
cursor.close();
}
}
});
}
});
Cursors shall be closed when they are released prematurely:
cursor
.read(50)
.onComplete(ar2 -> {
if (ar2.succeeded()) {
// Close the cursor
cursor.close();
}
});
A stream API is also available for cursors, which can be more convenient, specially with the Rxified version.
connection
.prepare("SELECT * FROM users WHERE age > ?")
.onComplete(ar1 -> {
if (ar1.succeeded()) {
PreparedStatement pq = ar1.result();
// Fetch 50 rows at a time
RowStream<Row> stream = pq.createStream(50, Tuple.of(18));
// Use the stream
stream.exceptionHandler(err -> {
System.out.println("Error: " + err.getMessage());
});
stream.endHandler(v -> {
System.out.println("End of stream");
});
stream.handler(row -> {
System.out.println("User: " + row.getString("last_name"));
});
}
});
The stream read the rows by batch of 50
and stream them, when the rows have been passed to the handler,
a new batch of 50
is read and so on.
The stream can be resumed or paused, the loaded rows will remain in memory until they are delivered and the cursor will stop iterating.