Simple SQL Operations

There are times when you will want to run a single SQL operation, e.g.: a single select of a row, or a update to a
set of rows which do not require to be part of a transaction or have dependencies on the previous or next operation.

For these cases, clients provide a boilerplate-less API SQLOperations. This interface will
perform the following steps for you:

You can also retrieve the rows as a list of Json object instances with getRows -
this can give you a somewhat simpler API to work with, but please be aware that SQL results can contain duplicate
column names - if that’s the case you should use getResults instead.

Here’s an example of iterating through the results as Json object instances:

Note that the index of the arguments matches the index of the ? and that the output parameters expect to be a
String describing the type you want to receive.

To avoid ambiguation the implementations are expected to follow the following rules:

When a place holder in the IN array is NOT NULL it will be taken

When the IN value is NULL a check is performed on the OUT
When the OUT value is not null it will be registered as a output parameter
When the OUT is also null it is expected that the IN value is the NULL value.

The registered OUT parameters will be available as an array in the result set under the output property.

Batch operations

The SQL common interface also defines how to execute batch operations. There are 3 types of batch operations:

Multiple ResultSet responses

In some cases your query might return more than one result set, in this case and to preserve the compatibility when
the returned result set object is converted to pure json, the next result sets are chained to the current result set
under the property next. A simple walk of all result sets can be achieved like this:

while (rs != null) {
// do something with the result set...// next step
rs = rs.getNext();
}

Streaming

When dealing with large data sets, it is not advised to use API just described but to stream data since it avoids
inflating the whole response into memory and JSON and data is just processed on a row by row basis, for example:

You still have full control on when the stream is pauses, resumed and ended. For cases where your query returns
multiple result sets you should use the result set ended event to fetch the next one if available. If there is more
data the stream handler will receive the new data, otherwise the end handler is invoked.

connection.queryStream("SELECT * FROM large_table; SELECT * FROM other_table", stream -> {
if (stream.succeeded()) {
SQLRowStream sqlRowStream = stream.result();
sqlRowStream
.resultSetClosedHandler(v -> {
// will ask to restart the stream with the new result set if any
sqlRowStream.moreResults();
})
.handler(row -> {
// do something with the row...
})
.endHandler(v -> {
// no more data available...
});
}
});

Using transactions

To use transactions first set auto-commit to false with setAutoCommit.

You then do your transactional operations and when you want to commit or rollback use
commit or
rollback.

Once the commit/rollback is complete the handler will be called and the next transaction will be automatically started.

RxJava 2 API

SQL Transaction management with RxJava

Vert.x provides observable transformers that you can apply to your flows with compose to make them transactional:

SQLClientHelper#txFlowableTransformer

SQLClientHelper#txObservableTransformer

SQLClientHelper#txSingleTransformer

SQLClientHelper#txMaybeTransformer

SQLClientHelper#txCompletableTransformer

These transformers wrap the corresponding source of events with SQL transaction management. Let’s take an example.

In your music library application, you need to insert a row in table albums, then some rows in table tracks.
These two steps shall be part of the same atomic transaction.
If it succeeds, the application must return results from a query involving both tables.

After your got an instance of io.vertx.reactivex.ext.sql.SQLConnection, you can use it to perform the SQL operations:

Source transformers provide maximum flexibility: you are still able to execute operations with the connection after the transaction completes.

But more often than not, you do not need the connection after the changes are commited or rollbacked.
In this case, you may simply create you source observable with one of the transactional helper methods in io.vertx.reactivex.ext.sql.SQLClientHelper.