# 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

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