Spring Support direct shard query routing in Spring JDBC

This PR adds Spring JDBC support for direct database operation routing in sharded databases. The main goal is to enable the routing of queries directly to specific shards. This functionality is achieved by acquiring a shard connection through the JDBC API DataSource.createConnectionBuilder().shardingKey(key).build().

Note: The sharding key is a value that determines which shard a particular data entry should be stored on or retrieved from within the sharded database.

The PR introduces two methods for executing direct shard queries:

1. Using DirectShardCallbackTemplate: This method involves utilizing a callback that contains the queries to be executed using direct routing. Here's an example of how to use it:

ShardingKeyDataSourceAdapter dataSourceAdapter = new ShardingKeyDataSourceAdapter(dataSource);
DirectShardCallbackTemplate directShardTemplate = new DirectShardCallbackTemplate(dataSourceAdapter);

JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourceAdapter);

directShardTemplate.execute(shardingKey, () -> {
    int id = jdbcTemplate.query(SQL1);
    jdbcTemplate.execute(SQL2, id);
});

1. Using ShardingKeyProvider: This approach is suitable for cases where the sharding key value can be set independently from the queries being executed. Here's an example of how to use it:

ShardingKeyDataSourceAdapter dataSourceAdapter = new ShardingKeyDataSourceAdapter(dataSource);

dataSourceAdapter.setShardingKeyProvider(new ShardingKeyProvider() {
    public ShardingKey getShardingKey() throws SQLException {
        // Example: The sharding key is derived from the user name.
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        return dataSource.createShardingKeyBuilder().subKey(name, JDBCType.VARCHAR).build();
    }

    public ShardingKey getSuperShardingKey() throws SQLException {
        return null;
    }
});

JdbcTemplate shardingJdbcTemplate = new JdbcTemplate(dataSourceAdapter);

// The SQL query will be executed directly in the shard corresponding 
// to the sharding key returned from the ShardingKeyProvider.getShardingKey() method.
shardingJdbcTemplate.execute(SQL);

Comment From: jhoeller

While sharding would definitely be an interesting addition to the Spring JDBC module, there are concerns about transaction integration: The common connection pools out there do not support the JDBC 4.3 ConnectionBuilder API, and Spring's common transaction management assumes that it can operate on a given DataSource, with the same DataSource configured for JdbcTransactionManager as well as all JDBC data access code operating within Spring-managed transactions. I can see a ShardingKeyDataSourceAdapter being used with such managed transactions but only really with a sharding-enabled pool.

As for DirectShardCallbackTemplate, that is not really idiomatic since it exclusively works without transactions, in contrast to every other accessor in the Spring JDBC module. While there is nothing wrong with using such an arrangement in custom applications, I don't see it as a candidate for inclusion in Spring itself from the perspective of design and usage consistency.

Comment From: Anir-Ln

Concerning the approach using a ShardingKeyProvider : since there is a concern regarding the connection pool's support for the JDBC 4.3 DataSource.createConnectionBuilder() API, and since JDBC provides two different ways to set the ShardingKey (either using the ConnectionBuilder API or using the Connection API) we can change the code so that if the connection pool does not support the JDBC 4.3 DataSource.createConnectionBuilder() API, the sharding key will be set in the Connection object. If neither of the APIs are implemented, it would mean that both the connection pool and the JDBC driver do not support sharding and there is no reason to use a ShardingKeyDataSourceAdapter.

@Override   
public Connection getConnection() throws SQLException { 
    try {
        return createConnectionBuilder().build();   
    }
    catch (SQLFeatureNotSupportedException ex) {
        Connection con = obtainDataSource().getConnection();
        con.setShardingKey(key);
    return con;
    }
}

Is that an acceptable solution for this issue ?

Comment From: meedbek

I will continue the work on this feature in a new PR https://github.com/spring-projects/spring-framework/pull/31506.