13.3 Sharding Data Source for Transparent Access to Sharded Databases

Oracle Database Release 21c introduced a new JDBC data source that enables Java connectivity to a sharded database without the need for an application to furnish a sharding key.

If you use the sharding data source, then you do not have to identify and build the sharding key and the super sharding key to establish a connection to the sharded database, as discussed in the earlier How to Checkout Connections from a Pool with a Sharding Key section. The sharding data source also eliminates the need to maintain a separate data source for multishard queries.

This data source scales out to sharded databases transparently if you set the connection property oracle.jdbc.useShardingDriverConnection to true.

See Also:

Oracle Database JDBC Developer's Guide

The following code snippet shows how to use the sharded data source:

Example 13-2 Using the Sharded Data Source

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.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

public class ShardingDataSourceUCP {
    public static void main(String[] args) throws SQLException {
        ShardingDataSourceUCP sample = new ShardingDataSourceUCP();
        DataSource ucpDataSource = sample.getDataSource();
        // Get the details of following customers
        int[] customerIds = new int[] {
            100,
            101,
            102,
            103,
            104,
            105
        };

        for (int id: customerIds) {
            try (Connection conn = ucpDataSource.getConnection()) {
                sample.displayCustomerDetails(conn, id);
                System.out.println(((OracleConnection) conn).getPercentageQueryExecutionOnDirectShard());
            }
        }
    }

    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 {
        PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
        pds.setURL("<gsmURL>");
        pds.setUser("<userName>");
        pds.setPassword("<password>");
        pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        Properties prop = new Properties();
        // Connection property to enable sharding datasource feature, when this property
        // is set you don't need to pass sharding key to UCP pool while borrowing the connection
        prop.setProperty(OracleConnection.CONNECTION_PROPERTY_USE_SHARDING_DRIVER_CONNECTION, "true");
        pds.setConnectionProperties(prop);
        return pds;
    }
}

See Also:

13.3.1 Support for Single Shard Transactions

The sharding data source enables you to limit your transactions to one single shard.

To enable single shard transaction support, you must set CONNECTION_PROPERTY_ALLOW_SINGLE_SHARD_TRANSACTION_SUPPORT. If this property is not set, then by default, all the transactions are started on the Shard Catalog. If you set the value of this property to true, then you must ensure that all the transactions span over a single shard only.

See Also:

Example 13-3 Enabling Single Shard Transactions

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 SingleShardTransactionUCP {
    public static void main(String[] args) throws SQLException {
        SingleShardTransactionUCP sample = new SingleShardTransactionUCP();
        DataSource ucpDS = sample.getDataSource();

        // Insert and update the details of following customers in a single transaction
        int[] customerIds = new int[] {
            100,
            101,
            102,
            103,
            104,
            105
        };

        for (int id: customerIds) {
            try (Connection conn = ucpDS.getConnection()) {
                conn.setAutoCommit(false);
                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 datasource 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;
    }
}