Baratine on GitHub

JDBC Service

Overview

Note

JdbcService is currently only availabe in 1.0.1+.

JdbcService is a service to query databases that have JDBC drivers. Queries run asynchronously in the background without blocking the caller. Once done, the caller’s Result is executed in the caller’s context.

JdbcService is bounded to the jdbc: URL scheme inside Baratine. The service is implemented as a Vault backed by a pool of @Worker services.

Configuration

There are two ways to hook up JdbcService to your database. The first way is to configure it programmatically when starting up the server:

String serviceUrl = "jdbc:///foo";

Web.property(serviceUrl + ".url",  "jdbc:mysql://localhost/myDb")
Web.property(serviceUrl + ".user", "root");
Web.property(serviceUrl + ".pass", "mypassword");

Web.go();

The second way is with a YAML config file:

"jdbc:///foo.url"  : jdbc:mysql://localhost/myDb
"jdbc:///foo.user" : root
"jdbc:///foo.pass" : mypassword

Passing in the file via the command-line:

$ java -jar myapp.jar --conf jdbc.yaml

Then calling Web.go(args) with those arguments:

public static void main(String[] args) throws Exception {
  Web.include(...);

  Web.go(args);
}

The complete set of config options are:

  • url the JDBC url (required)
  • user the user
  • pass the password
  • poolSize size of the connection pool (default: 128)
  • testQueryBefore the query to test the connection with before executing the query
  • testQueryAfter the query to test the connection with after executing the query

If the query fails in testQueryBefore or testQueryBefore, then JdbcService will create a new connection.

Getting the JdbcService Instance

There are two ways to obtain the JdbcService. The first way is with injection:

@Inject @Service("jdbc:///foo")
private JdbcService jdbc;

The address of the service, jdbc:///foo, is the one we configured just earlier.

The other way is with Services.service():

JdbcService jdbc = Services.current().service("jdbc:///foo").as(JdbcService.class);

Sending Queries

The easiest way to execute queries is with this method:

void query(Result<JdbcResultSet> result, String sql, Object ... params);

Example:

jdbc.query(
  (JdbcResultSet rs, Throwable e) -> {
      int columns = rs.getColumnCount();
      int row = 0;
      for (JdbcRowSet rowSet : rs) {
        System.out.println("row: " + row++);
        for (int i = 0; i < columns; i++) {
          System.out.println("\t" + rowSet.getObject(i));
        }
      }
  },
  "SELECT * FROM test"
);

Or you can operate on the java.sql.Connection directly with an SQLFunction:

<T> void query(Result<T> result, SqlFunction<T> fun);

Example:

jdbc.query(
  (Long value, Throwable e) -> {
      System.out.println(value);
  },
  (Connection conn) -> {
      Statement stmt = conn.createStatement();

      stmt.execute("SELECT * FROM test");

      return stmt.getUpdateCount();
  }
);

Synchronous Interface

Instead of using JdbcService asynchronously, you may use the synchronous proxy JdbcServiceSync if you are allowed to block. Remember that the Baratine service is single-threaded and blocking would prevent other requests from being processed. JdbcServiceSync is easier to use, thus useful for @Worker services, JUnit testing, and @OnLoad/@OnSave.

Example:

@Inject @Service("jdbc:///foo")
private JdbcServiceSync jdbc;

@OnLoad
public void onLoad() {
  JdbcResultSet rs = jdbc.query("SELECT * FROM test");

  ...
}

Query Statistics

JdbcService can return the stats, like execution time, of recent queries:

void stats(Result<JdbcStat> result);

Example:

jdbc.stats((stat, e) -> {
    long totalQueryCount = stat.totalQueryCount();
    long totalFailedCount = stat.totalFailedCount();

    List<QueryStat> outstandingQueries = stat.outstandingQueries();
    List<QueryStat> recentQueries = stat.recentQueries();
    List<QueryStat> recentFailed = stat.recentFailed();

    for (QueryStat qStat : recentQueries) {
      System.out.println("query: " + qStat.query());
      System.out.println("time: " + (qStat.endTimeMs() - qStat.startTimeMs()) + "ms");
      System.out.println("exception: " + qStat.exception());
    }
});

Example

Putting it all together:

@Service
public class MyService {
  @Inject @Service("jdbc:///foo")
  private JdbcService jdbc;

  @Get
  public void doInit(RequestWeb request) {
    jdbc.query(request.then(), this::doInitImpl);
  }

  private String doInitImpl(Connection conn) throws Exception {
    Statement stmt = conn.createStatement();

    stmt.execute("CREATE TABLE test (id INT PRIMARY KEY, value VARCHAR(32))");

    stmt.execute("INSERT INTO test VALUES (111, 'aaa')");
    stmt.execute("INSERT INTO test VALUES (222, 'bbb')");
    stmt.execute("INSERT INTO test VALUES (333, 'ccc')");

    return "inserted 3 rows";
  }

  @Get
  public void doSelect(RequestWeb request) {
    jdbc.query(request.then(rs -> rs.toString()), "SELECT * FROM test");
  }

  public static void main(String[] args) throws Exception {
    Web.include(MyService.class);

    String url = "jdbc:///foo";

    Web.property(url + ".url", "jdbc:hsqldb:mem:testdb");

    Web.go(args);
  }
}

pom.xml:

<dependency>
  <groupId>io.baratine</groupId>
  <artifactId>baratine</artifactId>
  <version>1.0.1</version>
</dependency>

<dependency>
  <groupId>org.hsqldb</groupId>
  <artifactId>hsqldb</artifactId>
  <version>2.3.3</version>
</dependency>