How to use database as backup/failover in hibernate?

Question

My application is based on the hibernate to fetched data from a mysql server. This mysql server is replicated to another mysql server instance. Today, I got a downtime as the primary database server was gone down without any notice. To avoid any future accidental problem, I am planing to add a functionality that will enable system to connect to secondary database if it finds the primary down.

Is there exists a way by which, I can leverage hibernate library to enable this functionality?

Answer 1

Today, I got a downtime as the primary database server was gone down without any notice.

Well, that's the first thing you should fix (with proper monitoring).

(...) Is there exists a way by which, I can leverage hibernate library to enable this functionality?

To my knowledge, Hibernate doesn't really provide any facility for this. Personally, I would investigate the fail-over support of MySQL and its JDBC driver. I can't provide a very concrete answer because I haven't implemented this with MySQL but here are some pointers:

• MySQL Failover Strategy using State Management, introducing MPP - Part 1
• 21.3.4.1. Driver/Datasource Class Names, URL Syntax and Configuration Properties for Connector/J
• 21.3.4.6. Using Master/Slave Replication with ReplicationConnection

And, as mentioned at the bottom of the latest link above (and also in this comment):

You may also want to investigate the Load Balancing JDBC Pool (lbpol) tool, which provides a wrapper around the standard JDBC driver and enables you to use DB connection pools that includes checks for system failures and uneven load distribution. For more information, see Load Balancing JDBC Pool (lbpool).

Answer 2

I have created following class after getting an idea form this thread It seems to be working well. I don't know if this is a good approach at all.

package com.vsd.server.hibernate;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.connection.C3P0ConnectionProvider;

public class FailoverConnectionProvider extends C3P0ConnectionProvider {

    String password;
    String username;
    String connectionString;
    String failover_connstring;

    @Override
    public Connection getConnection() throws SQLException {

        Connection conn = null;

        try {
            conn = DriverManager.getConnection(connectionString, username, password);
        } catch (Exception ex) {
            conn = DriverManager.getConnection(failover_connstring, username, password);
        }

        if(conn == null){
            throw new IllegalStateException("Database connection was not initialized");
        }

        return conn;

    }

    @Override
    public void configure(Properties properties) throws HibernateException {
        failover_connstring = properties.getProperty("hibernate.connection.failover.url");

        if (StringUtils.isBlank(connectionString)
                && StringUtils.isBlank(failover_connstring)
                && StringUtils.isBlank(username)
                && StringUtils.isBlank(password)) {
            throw new IllegalStateException("Unable to initialize connection provider for hibernate");
        }
    }
}