Developing applications that use ConnectionBuilder and ShardingKey for efficient data access

You can write applications that use cached ConnectionBuilder and ShardingKey instances for database connection requests. Reusing existing cached connections is more efficient because a new connection isn't created for each database connection request.

Before you begin

To use ConnectionBuilder and sharding, your Liberty application requires:

  • A database that supports sharding
  • A JDBC driver that supports connectivity to the database at the JDBC 4.3 level, and which also supports sharding
  • A Liberty server that is configured to use the jdbc-4.3 feature and that runs on Java SE 11

About this task

The JDBC 4.3 specification introduces the ConnectionBuilder as a new way to request database connections. ConnectionBuilder allows a sharding key and super sharding key to be specified on a connection request, as well as a user and password.

When connecting through a data source that is managed by the application server, the data source aims to fulfill connection requests by matching and reusing an existing connection from the connection pool rather than creating a new connection. The JDBC 4.3 specification leaves out an important detail for matching connections that have a sharding key, in that it places no requirement on the JDBC driver to implement a ShardingKey.equals method, such that multiple instances can be compared for equality. This means that instances of ShardingKey that logically represent the same key cannot match, causing new connections to be created rather than reusing existing pooled connections, which later end up being discarded. Some JDBC drivers might choose to implement the method even though the specification does not require it, so that connections can match. However, even if the JDBC driver you are using does not do so, you can write your application to make connection requests efficiently. You can cache a single instance of ShardingKey within your application and always use the cached copy when requesting connections.

In addition to caching the sharding key, you can also cache preconfigured ConnectionBuilder instances that you use to make all of your connection requests. When caching ConnectionBuilder, ConnectionBuilder is not threadsafe for writes. Therefore, perform all the write operations from a single thread prior to invoking the build method; for example:

conBuilder = dataSource.createConnectionBuilder()
                       .user(u)
                       .password(p)
                       .shardingKey(sk)
                       .superShardingKey(ssk);

It is then safe to cache and reuse the connection builder instance and to invoke its build method from any number of other threads, including concurrently, as long as you avoid invoking the write methods after this point.

Procedure

  1. Enable the jdbc-4.3 feature in the server.xml file. 
    <featureManager>
   <feature>jdbc-4.3</feature>
</featureManager>
  2. Configure one or more dataSource elements.

    The following server.xml file enables the jdbc-4.3 feature and configures a dataSource element.

    <server>
  <featureManager>
    <feature>jdbc-4.3</feature>
    <feature>jndi-1.0</feature>
    <feature>servlet-4.0</feature>
  </featureManager>

  <application location="exampleApp.war"/>

  <dataSource id="DefaultDataSource" jndiName="jdbc/oracle">
    <jdbcDriver libraryRef="OracleLib"/>
    <properties.oracle URL="jdbc:oracle:thin:@//localhost:1521/SAMPLEDB"/>
    <containerAuthData user="user1" password="pwd1"/>
  </dataSource>

  <library id="OracleLib">
    <file name="C:/Oracle/lib/ojdbcx.jar"/>
  </library>
</server>
  3. Cache a ConnectionBuilder in a servlet. The following example caches a ConnectionBuilder and, implicitly, its sharding keys in a servlet. 
    @WebServlet(urlPatterns = "/ExampleServlet")
public class ExampleServlet extends HTTPServlet {
  @Resource
  DataSource dataSource;

  ConnectionBuilder conBuilder;

  public void init() throws ServletException {
    ShardingKey shardKey = dataSource.createShardingKeyBuilder()
                           .subkey(SUBKEY1, JDBCType.VARCHAR);
    ShardingKey superKey = dataSource.createShardingKeyBuilder()
                           .subkey(SUBKEY2, JDBCType.VARCHAR);
    // user/password are omitted due to container authentication
    conBuilder = dataSource.createConnectionBuilder()
                           .shardingKey(shardKey)
                           .superShardingKey(superKey);
  }

  public void doGet(HttpServletRequest req, HttpServletResponse res)
         throws ServletException, IOException {
    try (Connection con = conBuilder.build()) {
       PreparedStatement ps = con.prepareStatement(sql);
       ...
    } catch (SQLException x) {
      throw new ServletException(x);
    }
  }
}