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. 
Please note that extending vendor-specific classes such as the PoolDataSourceImpl is not a recommended best practice except in specific cases such as this blog and this works with JDK8 only.
  
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 {
  @Override
  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)
        .labels(labels).shardingKey(shardKey).build();
  }
}
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) {
    ShardingController.getShardingKeyContext().set(customerId);
    return (Customer) sqlSession.selectOne("CustomerMapper.getCustomerDetails",
        customerId);
  }
} 

/*
 * 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(
        "spring-config.xml");
    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());

  }
}