Documentation Home
MySQL Utilities 1.5 Manual
Related Documentation Download this Manual
PDF (US Ltr) - 1.5Mb
PDF (A4) - 1.5Mb
HTML Download (TGZ) - 289.5Kb
HTML Download (Zip) - 301.7Kb

MySQL Utilities 1.5 Manual  /  ...  /  Using Hibernate with MySQL Fabric

8.9.9 Using Hibernate with MySQL Fabric

It is possible to use Hibernate 4's multi-tenancy support to work with a set of database servers managed by MySQL Fabric.

APIs necessary to implement MultiTenantConnectionProvider

We can use internal APIs included with Connector/J with MySQL Fabric support to implement Hibernate's MultiTenantConnectionProvider.

The following implementation is included in the package as the com.mysql.fabric.hibernate.FabricMultiTenantConnectionProvider class. An example of how to use it is included as the class demo.fabric.HibernateFabric.

To implement MultiTenantConnectionProvider, we use the com.mysql.fabric.FabricConnection class. This class connects to the MySQL Fabric manager to obtain information about servers and data sharding. This is an internal API and subject to change. The following FabricConnection methods can be used:

  • FabricConnection(String url, String username, String password) throws FabricCommunicationException

    Construct a new instance of a MySQL Fabric client and initiate the connection.

  • ServerGroup getServerGroup(String serverGroupName) throws FabricCommunicationException

    Retrieve an object representing the named server group. This includes the list of servers in the group including their mode (read-only or read-write).

  • ShardMapping getShardMapping(String database, String table) throws FabricCommunicationException

    Retrieve an object represending a shard mapping for the given database and table. The ShardMapping indicates the global group and the individual shards.

The following additional methods are used:

  • Set<Server> ServerGroup.getServers()

    Return the servers in this group.

  • String Server.getHostname()

    Return the server host name.

  • int Server.getPort()

    Return the server port.

Implementing MultiTenantConnectionProvider

To begin, we declare the class with members to keep necessary information for the connection and the constructor:

public class FabricMultiTenantConnectionProvider implements MultiTenantConnectionProvider {
    // a connection to the MySQL Fabric manager
    private FabricConnection fabricConnection;
    // the database and table of the sharded data
    private String database;
    private String table;
    // user and password for Fabric manager and MySQL servers
    private String user;
    private String password;
    // shard mapping for `database.table'
    private ShardMapping shardMapping;
    // global group for the shard mapping
    private ServerGroup globalGroup;

    public FabricMultiTenantConnectionProvider(
              String fabricUrl, String database, String table,
              String user, String password) {
        try {
            this.fabricConnection = new FabricConnection(fabricUrl, user, password);
            this.database = database;
            this.table = table;
            this.user = user;
            this.password = password;
            // eagerly retrieve the shard mapping and server group from the Fabric manager
            this.shardMapping = this.fabricConnection.getShardMapping(this.database, this.table);
            this.globalGroup = this.fabricConnection.
        } catch(FabricCommunicationException ex) {
            throw new RuntimeException(ex);

Next, create a method to create connections:

 * Find a server with mode READ_WRITE in the given server group and create a JDBC connection to it.
 * @returns a {@link Connection} to an arbitrary MySQL server
 * @throws SQLException if connection fails or a READ_WRITE server is not contained in the group
private Connection getReadWriteConnectionFromServerGroup(ServerGroup serverGroup)
            throws SQLException {
    // iterate the list of servers in the given group until we find a r/w server
    for (Server s : serverGroup.getServers()) {
        if (ServerMode.READ_WRITE.equals(s.getMode())) {
            // create a connection to the server using vanilla JDBC
            String jdbcUrl = String.format("jdbc:mysql://%s:%s/%s",
                      s.getHostname(), s.getPort(), this.database);
            return DriverManager.getConnection(jdbcUrl, this.user, this.password);
    // throw an exception if we are unable to make the connection
    throw new SQLException(
          "Unable to find r/w server for chosen shard mapping in group " + serverGroup.getName());

To implement the interface, the following methods must be implemented:

  • Connection getAnyConnection() throws SQLException

    This method should obtain a connection to the global group. We can implement it like this:

     * Get a connection that be used to access data or metadata not specific to any shard/tenant.
     * The returned connection is a READ_WRITE connection to the global group of the shard mapping
     * for the database and table association with this connection provider.
    public Connection getAnyConnection() throws SQLException {
        return getReadWriteConnectionFromServerGroup(this.globalGroup);
  • Connection getConnection(String tenantIdentifier) throws SQLException

    This method must use the tenantIdentifier to determine which server to access. We can look up the ServerGroup from the ShardMapping like this:

     * Get a connection to access data association with the provided `tenantIdentifier' (or shard
     * key in Fabric-speak). The returned connection is a READ_WRITE connection.
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        String serverGroupName = this.shardMapping.getGroupNameForKey(tenantIdentifier);
        try {
            ServerGroup serverGroup = this.fabricConnection.getServerGroup(serverGroupName);
            return getReadWriteConnectionFromServerGroup(serverGroup);
        } catch(FabricCommunicationException ex) {
            throw new RuntimeException(ex);

Finally, our trivial implementation to release connections:

 * Release a non-shard-specific connection.
public void releaseAnyConnection(Connection connection) throws SQLException {

 * Release a connection specific to `tenantIdentifier'.
public void releaseConnection(String tenantIdentifier, Connection connection)
          throws SQLException {

 * We don't track connections.
 * @returns false
public boolean supportsAggressiveRelease() {
    return false;

And finally to implement the Wrapped role:

public boolean isUnwrappableAs(Class unwrapType) {
    return false;

public <T> T unwrap(Class<T> unwrapType) {
    return null;

Using a custom MultiTenantConnectionProvider

The SessionFactory can be created like this:

// create a new instance of our custom connection provider supporting MySQL Fabric
FabricMultiTenantConnectionProvider connProvider =
          new FabricMultiTenantConnectionProvider(
                  fabricUrl, "employees", "employees", username, password);
// create a service registry with the connection provider to construct the session factory
ServiceRegistryBuilder srb = new ServiceRegistryBuilder();
srb.applySetting("hibernate.dialect", "org.hibernate.dialect.MySQLInnoDBDialect");

// create the configuration and build the session factory
Configuration config = new Configuration();
config.setProperty("hibernate.multiTenancy", "DATABASE");
return config.buildSessionFactory(srb.buildServiceRegistry());

Using Hibernate multi-tenancy

Once you have created a SessionFactory with your custom MultiTenantConnectionProvider, it is simple to use. Provide the shard key to the SessionFactory when creating the session:

// access data related to shard key = 40
Session session = sessionFactory.withOptions().tenantIdentifier("40").openSession();

Each Session is given a shard key (tenant identifier in Hibernate-speak) and uses it to obtain a connection to an appropriate server. This cannot be changed for the duration of the Session.

User Comments
Sign Up Login You must be logged in to post a comment.