Technical Preview

Reactive MSSQL Client

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

Not supported yet

Prepared queries caching
Batch and cursor
Row streaming
Some data types are not supported

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</version>
</dependency>

Gradle (in your build.gradle file):

dependencies {
 compile 'io.vertx:vertx-mssql-client:4.1.0'
}

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
});

Connection URI

As an alternative to configuring the client with a MSSQLConnectOptions data object, you can use a connection URI:

String connectionUri = "sqlserver://dbuser:[email protected]: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

Connect retries

You can configure the client to retry when a connection fails to be established.

options
  .setReconnectAttempts(2)
  .setReconnectInterval(1000);

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 `@1`, `@2`, etc…

client
  .preparedQuery("SELECT * FROM users WHERE [email protected]")
  .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());
    }
  });

Using connections

Getting a connection

When you need to execute sequential queries (without a transaction), you can create a new connection or borrow one from the pool. Remember that between acquiring the connection from the pool and returning it to the pool, you should take care of the connection because it might be closed by the server for some reason such as an idle time out.

pool
  .getConnection()
  .compose(connection ->
    connection
      .preparedQuery("INSERT INTO Users (first_name,last_name) VALUES (@p1, @p2)")
      .executeBatch(Arrays.asList(
        Tuple.of("Julien", "Viet"),
        Tuple.of("Emad", "Alblueshi")
      ))
      .compose(res -> connection
        // Do something with rows
        .query("SELECT COUNT(*) FROM Users")
        .execute()
        .map(rows -> rows.iterator().next().getInteger(0)))
      // Return the connection to the pool
      .eventually(v -> connection.close())
  ).onSuccess(count -> {
  System.out.println("Insert users, now the number of users is " + count);
});

Prepared queries can be created:

connection
  .prepare("SELECT * FROM users WHERE first_name LIKE @p1")
  .compose(pq ->
    pq.query()
      .execute(Tuple.of("Julien"))
      .eventually(v -> pq.close())
  ).onSuccess(rows -> {
  // All rows
});

Simplified connection API

When you use a pool, you can call withConnection to pass it a function executed within a connection.

It borrows a connection from the pool and calls the function with this connection.

The function must return a future of an arbitrary result.

After the future completes, the connection is returned to the pool and the overall result is provided.

pool.withConnection(connection ->
  connection
    .preparedQuery("INSERT INTO Users (first_name,last_name) VALUES (@p1, @p2)")
    .executeBatch(Arrays.asList(
      Tuple.of("Julien", "Viet"),
      Tuple.of("Emad", "Alblueshi")
    ))
    .compose(res -> connection
      // Do something with rows
      .query("SELECT COUNT(*) FROM Users")
      .execute()
      .map(rows -> rows.iterator().next().getInteger(0)))
).onSuccess(count -> {
  System.out.println("Insert users, now the number of users is " + count);
});

Using transactions

Transactions with connections

You can execute transaction using SQL BEGIN/COMMIT/ROLLBACK, if you do so you must use a SqlConnection and manage it yourself.

Or you can use the transaction API of SqlConnection:

pool.getConnection()
  // Transaction must use a connection
  .onSuccess(conn -> {
    // Begin the transaction
    conn.begin()
      .compose(tx -> conn
        // Various statements
        .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
        .execute()
        .compose(res2 -> conn
          .query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')")
          .execute())
        // Commit the transaction
        .compose(res3 -> tx.commit()))
      // Return the connection to the pool
      .eventually(v -> conn.close())
      .onSuccess(v -> System.out.println("Transaction succeeded"))
      .onFailure(err -> System.out.println("Transaction failed: " + err.getMessage()));
  });

When the database server reports the current transaction is failed (e.g the infamous current transaction is aborted, commands ignored until end of transaction block), the transaction is rollbacked and the completion future is failed with a TransactionRollbackException:

tx.completion()
  .onFailure(err -> {
    System.out.println("Transaction failed => rolled back");
  });

Simplified transaction API

When you use a pool, you can call withTransaction to pass it a function executed within a transaction.

It borrows a connection from the pool, begins the transaction and calls the function with a client executing all operations in the scope of this transaction.

The function must return a future of an arbitrary result:

when the future succeeds the client will commit the transaction
when the future fails the client will rollback the transaction

After the transaction completes, the connection is returned to the pool and the overall result is provided.

pool.withTransaction(client -> client
  .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
  .execute()
  .flatMap(res -> client
    .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
    .execute()
    // Map to a message result
    .map("Users inserted")))
  .onSuccess(v -> System.out.println("Transaction succeeded"))
  .onFailure(err -> System.out.println("Transaction failed: " + err.getMessage()));

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)
BINARY/VARBINARY(io.vertx.core.buffer.Buffer)

Tuple decoding uses the above types when storing values.

Using Java `enum` types

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 like this:

client
  .preparedQuery("INSERT INTO colors VALUES (@p1)")
  .execute(Tuple.of(Color.red),  res -> {
    // ...
  });

You can decode a Java enum 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"));
      }
    }
  });

Handling `NULL`

If you modify a Tuple with one of the addXXX methods, null values are handled transparently.

The client can infer the right SQL type when executing a prepared query:

Tuple tuple = Tuple.tuple()
  .addInteger(17)
  .addString("The Man Who Knew Too Much")
  .addString(null);
client
  .preparedQuery("INSERT INTO movies (id, title, plot) VALUES (@p1, @p2, @p3)")
  .execute(tuple, res -> {
    // ...
  });

Otherwise, you should declare the type explicitely using one of the NullValue constants or the NullValue.of method:

Tuple tuple = Tuple.of(17, "The Man Who Knew Too Much", NullValue.String);
client
  .preparedQuery("INSERT INTO movies (id, title, plot) VALUES (@p1, @p2, @p3)")
  .execute(tuple, res -> {
    // ...
  });

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());
    }
  });