Develop applications that use ConnectionBuilder and ShardingKey for efficient data access

We 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.

To use ConnectionBuilder and sharding, our 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 configured to use the jdbc-4.3 feature and that runs on Java SE 11

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 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 we are using does not do so, we can write the application to make connection requests efficiently. We can cache a single instance of ShardingKey within the application and always use the cached copy when requesting connections.

In addition to caching the sharding key, we can also cache preconfigured ConnectionBuilder instances we use to make all of the 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 we avoid invoking the write methods after this point.

1. Enable the jdbc-4.3 feature in server.xml.

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