Example 29-1 Building a Sharding Key

The following example shows how to build a sharding key:

import java.sql.Connection;
import java.sql.JDBCType;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ShardingKey;
import java.sql.Statement;

import oracle.jdbc.pool.OracleDataSource;

public class JDBCShardingExample {

    public static void main(String[] args) throws Exception {

        OracleDataSource ods = new OracleDataSource();
        ods.setURL("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=myhost)(PORT=<gsm-listener-port>)
            (PROTOCOL = tcp))(CONNECT_DATA = (SERVICE_NAME = gsmservicename)))
    ");
    ods.setUser(<db_user_name>);
    ods.setPassword(<db_password>);

    int empId = 1234;
    String location = "US";

    // Employee ID is the sharding key column
    ShardingKey shardingKey = ods.createShardingKeyBuilder()
        .subkey(empId, JDBCType.INTEGER)
        .build();

    // Employee location is the super sharding key column
    ShardingKey superShardingKey = ods.createShardingKeyBuilder()
        .subkey(location, JDBCType.VARCHAR)
        .build();

    // Get a direct connection to the shard that contains the record of the employee
    // with employee id = 1234 and location="US", using sharding key and super sharding key
    
    try (Connection connection = ods.createConnectionBuilder()
        .shardingKey(shardingKey)
        .superShardingKey(superShardingKey)
        .build()) {

              PreparedStatement pst = connection.prepareStatement("select * from employee where emp_id=? and location=?");
              pst.setInt(1, 1234);
              pst.setString(2, "US");
              ResultSet rs = pst.executeQuery();
              // Retrieve the employee details using resultset
              rs.close();
              pst.close();
          }      
      }

}

Example 29-3 Single Shard Transaction Support

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import oracle.jdbc.internal.OracleConnection;
import oracle.jdbc.pool.OracleDataSource; 

public class SingleShardTransaction {
       public static void main(String[] args) throws SQLException {
             SingleShardTransaction sample = new SingleShardTransaction();
             DataSource ds = sample.getDataSource();
             // Insert and update the details of following customers in a single transaction
             int[] customerIds = new int[] { 100, 101, 102, 103, 104, 105 };

             try (Connection conn = ds.getConnection()) {
                    conn.setAutoCommit(false);
                    for (int id : customerIds) {
                          sample.insertCustomerDetails(conn, id);
                          sample.displayCustomerDetails(conn, id);
                          sample.updateCustomerDetails(conn, id);
                          sample.displayCustomerDetails(conn, id);
                          conn.commit();
                    }

                    System.out.println(((OracleConnection) conn).getPercentageQueryExecutionOnDirectShard());
             }
       } 

       private void insertCustomerDetails(Connection conn, int id) throws SQLException {
             String sql = "insert into CUSTOMER values(?, ?, ?, ?)";

             try (PreparedStatement ps = conn.prepareStatement(sql)) {
                    ps.setInt(1, id);
                    ps.setString(2, name);
                    ps.setString(3, email);
                    ps.setString(4, phoneNumber);
                    ps.executeUpdate();
             }
       }

       private void updateCustomerDetails(Connection conn, int id) throws SQLException {
             String sql = "UPDATE CUSTOMER SET name = ?, email = ?, phoneNumber = ? WHERE customerId = ?";

             try (PreparedStatement ps = conn.prepareStatement(sql)) {
                    ps.setString(1, name);
                    ps.setString(2, email);
                    ps.setString(3, phoneNumber);
                    ps.setInt(4, id);
                    ps.executeUpdate();
             }
       }

       private void displayCustomerDetails(Connection conn, int id) throws SQLException {

             try (PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM CUSTOMER where ID = ?")) {
                    pstmt.setInt(1, id);

                    try (ResultSet rs = pstmt.executeQuery()) {
                          while (rs.next()) {
                                 // Print the customer details
                          }
                    }
             }
       }

       private DataSource getDataSource() throws SQLException  {
          OracleDataSource ds = new OracleDataSource();
          ds.setURL(<gsmURL>);
          ds.setUser(<userName>);
          ds.setPassword(<password>);
          Properties prop = new Properties();
          // Connection property to enable sharding data source feature
          prop.setProperty(OracleConnection.CONNECTION_PROPERTY_USE_SHARDING_DRIVER_CONNECTION, "true");

          // Connection property to enable single shard transaction support. If this property is not set,
          // by default all the transactions are started on catalog DB. When setting this property value
          // to "true", applications must ensure that all the transactions span over a single shard only.

          prop.setProperty(oracle.jdbc.OracleConnection.CONNECTION_PROPERTY_ALLOW_SINGLE_SHARD_TRANSACTION_SUPPORT, "true");
          ds.setConnectionProperties(prop);
          return ds;
  }
}