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:

The following additional methods are used:

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.
                    getServerGroup(this.shardMapping.getGlobalGroupName());
        } 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:

Finally, our trivial implementation to release connections:

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

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

/**
 * 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.addService(
          org.hibernate.service.jdbc.connections.spi.MultiTenantConnectionProvider.class,
          connProvider);
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");
config.addResource("com/mysql/fabric/demo/employee.hbm.xml");
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.