The Reactive MSSQL Client is a client for Microsoft SQL Server with a straightforward API focusing on scalability and low overhead.
Features
-
Event driven
-
Lightweight
-
Built-in connection pooling
-
Direct memory to object without unnecessary copies
-
Java 8 Date and Time
Usage
To use the Reactive MSSQL Client add the following dependency to the dependencies section of your build descriptor:
-
Maven (in your
pom.xml
):
<dependency>
<groupId>io.vertx</groupId>
<artifactId>vertx-mssql-client</artifactId>
<version>4.1.0.CR1</version>
</dependency>
-
Gradle (in your
build.gradle
file):
dependencies {
compile 'io.vertx:vertx-mssql-client:4.1.0.CR1'
}
Getting started
Here is the simplest way to connect, query and disconnect
MSSQLConnectOptions connectOptions = new MSSQLConnectOptions()
.setPort(1433)
.setHost("the-host")
.setDatabase("the-db")
.setUser("user")
.setPassword("secret");
// Pool options
PoolOptions poolOptions = new PoolOptions()
.setMaxSize(5);
// Create the client pool
MSSQLPool client = MSSQLPool.pool(connectOptions, poolOptions);
// A simple query
client
.query("SELECT * FROM users WHERE id='julien'")
.execute(ar -> {
if (ar.succeeded()) {
RowSet result = ar.result();
System.out.println("Got " + result.size() + " rows ");
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
// Now close the pool
client.close();
});
Connecting to SQL Server
Most of the time you will use a pool to connect to MSSQL:
MSSQLConnectOptions connectOptions = new MSSQLConnectOptions()
.setPort(1433)
.setHost("the-host")
.setDatabase("the-db")
.setUser("user")
.setPassword("secret");
// Pool options
PoolOptions poolOptions = new PoolOptions()
.setMaxSize(5);
// Create the pooled client
MSSQLPool client = MSSQLPool.pool(connectOptions, poolOptions);
The pooled client uses a connection pool and any operation will borrow a connection from the pool to execute the operation and release it to the pool.
If you are running with Vert.x you can pass it your Vertx instance:
MSSQLConnectOptions connectOptions = new MSSQLConnectOptions()
.setPort(1433)
.setHost("the-host")
.setDatabase("the-db")
.setUser("user")
.setPassword("secret");
// Pool options
PoolOptions poolOptions = new PoolOptions()
.setMaxSize(5);
// Create the pooled client
MSSQLPool client = MSSQLPool.pool(vertx, connectOptions, poolOptions);
You need to release the pool when you don’t need it anymore:
pool.close();
When you need to execute several operations on the same connection, you need to use a client
connection
.
You can easily get one from the pool:
MSSQLConnectOptions connectOptions = new MSSQLConnectOptions()
.setPort(1433)
.setHost("the-host")
.setDatabase("the-db")
.setUser("user")
.setPassword("secret");
// Pool options
PoolOptions poolOptions = new PoolOptions()
.setMaxSize(5);
// Create the pooled client
MSSQLPool client = MSSQLPool.pool(vertx, connectOptions, poolOptions);
// Get a connection from the pool
client.getConnection().compose(conn -> {
System.out.println("Got a connection from the pool");
// All operations execute on the same connection
return conn
.query("SELECT * FROM users WHERE id='julien'")
.execute()
.compose(res -> conn
.query("SELECT * FROM users WHERE id='emad'")
.execute())
.onComplete(ar -> {
// Release the connection to the pool
conn.close();
});
}).onComplete(ar -> {
if (ar.succeeded()) {
System.out.println("Done");
} else {
System.out.println("Something went wrong " + ar.cause().getMessage());
}
});
Once you are done with the connection you must close it to release it to the pool, so it can be reused.
Configuration
Data Object
A simple way to configure the client is to specify a MSSQLConnectOptions
data object.
MSSQLConnectOptions connectOptions = new MSSQLConnectOptions()
.setPort(1433)
.setHost("the-host")
.setDatabase("the-db")
.setUser("user")
.setPassword("secret");
// Pool Options
PoolOptions poolOptions = new PoolOptions().setMaxSize(5);
// Create the pool from the data object
MSSQLPool pool = MSSQLPool.pool(vertx, connectOptions, poolOptions);
pool.getConnection(ar -> {
// Handling your connection
});
Running queries
When you don’t need a transaction or run single queries, you can run queries directly on the pool; the pool will use one of its connection to run the query and return the result to you.
Here is how to run simple queries:
client
.query("SELECT * FROM users WHERE id='julien'")
.execute(ar -> {
if (ar.succeeded()) {
RowSet<Row> result = ar.result();
System.out.println("Got " + result.size() + " rows ");
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
Prepared queries
You can do the same with prepared queries.
The SQL string can refer to parameters by position, using the database syntax {PREPARED_PARAMS}
client
.preparedQuery("SELECT * FROM users WHERE id=@p1")
.execute(Tuple.of("julien"), ar -> {
if (ar.succeeded()) {
RowSet<Row> rows = ar.result();
System.out.println("Got " + rows.size() + " rows ");
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
Query methods provides an asynchronous RowSet
instance that works for SELECT queries
client
.preparedQuery("SELECT first_name, last_name FROM users")
.execute(ar -> {
if (ar.succeeded()) {
RowSet<Row> rows = ar.result();
for (Row row : rows) {
System.out.println("User " + row.getString(0) + " " + row.getString(1));
}
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
or UPDATE/INSERT queries:
client
.preparedQuery("INSERT INTO users (first_name, last_name) VALUES (@p1, @p2)")
.execute(Tuple.of("Julien", "Viet"), ar -> {
if (ar.succeeded()) {
RowSet<Row> rows = ar.result();
System.out.println(rows.rowCount());
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
The Row
gives you access to your data by index
System.out.println("User " + row.getString(0) + " " + row.getString(1));
or by name
System.out.println("User " + row.getString("first_name") + " " + row.getString("last_name"));
The client will not do any magic here and the column name is identified with the name in the table regardless of how your SQL text is.
You can access a wide variety of of types
String firstName = row.getString("first_name");
Boolean male = row.getBoolean("male");
Integer age = row.getInteger("age");
You can use cached prepared statements to execute one-shot prepared queries:
connectOptions.setCachePreparedStatements(true);
client
.preparedQuery("SELECT * FROM users WHERE id = @p1")
.execute(Tuple.of("julien"), ar -> {
if (ar.succeeded()) {
RowSet<Row> rows = ar.result();
System.out.println("Got " + rows.size() + " rows ");
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
You can create a PreparedStatement
and manage the lifecycle by yourself.
sqlConnection
.prepare("SELECT * FROM users WHERE id = @p1", ar -> {
if (ar.succeeded()) {
PreparedStatement preparedStatement = ar.result();
preparedStatement.query()
.execute(Tuple.of("julien"), ar2 -> {
if (ar2.succeeded()) {
RowSet<Row> rows = ar2.result();
System.out.println("Got " + rows.size() + " rows ");
preparedStatement.close();
} else {
System.out.println("Failure: " + ar2.cause().getMessage());
}
});
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
Batches
You can execute prepared batch
List<Tuple> batch = new ArrayList<>();
batch.add(Tuple.of("julien", "Julien Viet"));
batch.add(Tuple.of("emad", "Emad Alblueshi"));
// Execute the prepared batch
client
.preparedQuery("INSERT INTO USERS (id, name) VALUES (@p1, @p2)")
.executeBatch(batch, res -> {
if (res.succeeded()) {
// Process rows
RowSet<Row> rows = res.result();
} else {
System.out.println("Batch failed " + res.cause());
}
});
Connect retries
You can configure the client to retry when a connection fails to be established.
options
.setReconnectAttempts(2)
.setReconnectInterval(1000);
Connection URI
As an alternative to configuring the client with a MSSQLConnectOptions
data object, you can use a connection URI:
String connectionUri = "sqlserver://dbuser:secretpassword@database.server.com:3211/mydb";
// Create the pool from the connection URI
MSSQLPool pool = MSSQLPool.pool(connectionUri);
// Create the connection from the connection URI
MSSQLConnection.connect(vertx, connectionUri, res -> {
// Handling your connection
});
The connection URI format is defined by the client in an idiomatic way: sqlserver://[user[:[password]]@]host[:port][/database][?attribute1=value1&attribute2=value2…]
Currently, the client supports the following parameter keywords in connection uri (keys are case-insensitive):
-
host
-
port
-
user
-
password
-
database
Tracing queries
The SQL client can trace query execution when Vert.x has tracing enabled.
The client reports the following client spans:
-
Query
operation name -
tags
-
db.user
: the database username -
db.instance
: the database instance -
db.statement
: the SQL query -
db.type
: sql
The default tracing policy is PROPAGATE
, the client
will only create a span when involved in an active trace.
You can change the client policy with setTracingPolicy
,
e.g you can set ALWAYS
to always report
a span:
options.setTracingPolicy(TracingPolicy.ALWAYS);
Data types supported
Currently, the client supports the following SQL Server types:
-
TINYINT(
java.lang.Short
) -
SMALLINT(
java.lang.Short
) -
INT(
java.lang.Integer
) -
BIGINT(
java.lang.Long
) -
BIT(
java.lang.Boolean
) -
REAL(
java.lang.Float
) -
DOUBLE(
java.lang.Double
) -
NUMERIC/DECIMAL(
BigDecimal
) -
CHAR/VARCHAR(
java.lang.String
) -
NCHAR/NVARCHAR(
java.lang.String
) -
DATE(
java.time.LocalDate
) -
TIME(
java.time.LocalTime
) -
DATETIME2(
java.time.LocalDateTime
) -
DATETIMEOFFSET(
java.time.OffsetDateTime
)
Tuple decoding uses the above types when storing values
Handling ENUM
SQL Server does not have ENUM data type but the client can map the retrieved string/numeric data type to enum.
You can encode Java enums as String like this:
client
.preparedQuery("INSERT INTO colors VALUES (@p1)")
.execute(Tuple.of(Color.red), res -> {
// ...
});
You can retrieve the ENUM column as Java enums like this:
client
.preparedQuery("SELECT color FROM colors")
.execute()
.onComplete(res -> {
if (res.succeeded()) {
RowSet<Row> rows = res.result();
for (Row row : rows) {
System.out.println(row.get(Color.class, "color"));
}
}
});
Collector queries
You can use Java collectors with the query API:
Collector<Row, ?, Map<Long, String>> collector = Collectors.toMap(
row -> row.getLong("id"),
row -> row.getString("last_name"));
// Run the query with the collector
client.query("SELECT * FROM users")
.collecting(collector)
.execute(ar -> {
if (ar.succeeded()) {
SqlResult<Map<Long, String>> result = ar.result();
// Get the map created by the collector
Map<Long, String> map = result.value();
System.out.println("Got " + map);
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});
The collector processing must not keep a reference on the Row
as
there is a single row used for processing the entire set.
The Java Collectors
provides many interesting predefined collectors, for example you can
create easily create a string directly from the row set:
Collector<Row, ?, String> collector = Collectors.mapping(
row -> row.getString("last_name"),
Collectors.joining(",", "(", ")")
);
// Run the query with the collector
client.query("SELECT * FROM users")
.collecting(collector)
.execute(ar -> {
if (ar.succeeded()) {
SqlResult<String> result = ar.result();
// Get the string created by the collector
String list = result.value();
System.out.println("Got " + list);
} else {
System.out.println("Failure: " + ar.cause().getMessage());
}
});