Learn Tips and Best Practices from the Oracle JDBC Development

  • August 11, 2018

Oracle Sharding with the Universal Connection Pool in Frameworks (Spring, Hibernate, MyBatis, and so on)

This article illustrates the steps to use Oracle Sharding with the Oracle Universal Connection Pool (UCP) in MyBatis-Spring framework. However, the same technique can be used with other frameworks too.
Below are two key steps:
1.  Create a custom UCP data source and override getConnection(user,password, labels) method.
2.  Pass the sharding key information to the datasource using a shared thread-local object.
Assume there is a simple sharded table customer with two columns “custid” and “name” where “custid” is the sharding key column.
1. Create a custom UCP datasource by extending oracle.ucp.jdbc.PoolDataSourceImpl class and override the getConnection(user, password, labels) method like below.
The overridden getConnection method gets the sharding key value from shared thread-local variable and uses it to get the connection from UCP.
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import oracle.jdbc.OracleShardingKey;
import oracle.jdbc.OracleType;
import oracle.ucp.jdbc.PoolDataSourceImpl;
public class ShardingDataSourceImpl extends PoolDataSourceImpl {
  public Connection getConnection(String username, String password,
      Properties labels) throws SQLException {
    String shardingkey = ShardingController.getShardingKeyContext().get();
    OracleShardingKey shardKey = this.createShardingKeyBuilder()
        .subkey(shardingkey, OracleType.VARCHAR2).build();
    return this.createConnectionBuilder().user(username).password(password)
2. Create a DAO class which sets the value of sharding key on the shared thread-local object before calling a method of sqlSession class which internally calls getConnection() method.
public class CustomerDaoImpl implements CustomerDao {
  private SqlSession sqlSession;

  public void setSqlSession(SqlSession sqlSession) {
    this.sqlSession = sqlSession;

  public Customer getCustomerDetails(String customerId) {
    return (Customer) sqlSession.selectOne("CustomerMapper.getCustomerDetails",

 * This class uses a thread-local variable to pass sharding key information from
 * application to UCP. The application code sets the value of sharding key on
 * the thread-local object, which is being read by UCP’s overridden
 * getConnection() method.
public class ShardingController {
  private static ThreadLocal<String> shardingKeyContext = new ThreadLocal<String>();

  public static ThreadLocal<String> getShardingKeyContext() {
    return shardingKeyContext;
The spring XML configuration file look like below:





The CustomerDaoImpl class uses sqlSessionTemplate which internally calls the Universal Connection Pool (UCP)  APIs for connection check-outs and check-ins. The main application code looks like the following. 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestUCPShardingWithMyBatisSpring {

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

    ApplicationContext ctx = new ClassPathXmlApplicationContext(
    String customerId = “cust123”;

    CustomerDaoImpl dao = (CustomerDaoImpl) ctx.getBean("customerDao");
    Customer customer = dao.getCustomerDetails(customerId);

    System.out.println("Customer ID = " + customer.getCustId());
    System.out.println("Customer Name = " + customer.getName());


Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.