The road so far….

December 6, 2010

Embedded DB + AbstractRoutingDataSource + Hibernate = Data Router Application

Filed under: java — Tags: , — Rahul Sharma @ 9:40 pm

Spring 3 contains a new feature of embedded databases. You could efficiently use this feature for an application at the start of a developement or for tests concerning db access or for smaller use cases of data storage. Basically using this feature you could start any of three viz. HSQL, Derby, H2. These all create  in memory database instances and you could create schema and import data in them. The EmbededDatabaseBuilder API offered by Spring can be used to create instances of  EmbededDatabase. The EmbededDatabase is of type Datasource and can  be plugged into the SessionFactory. This makes it easier to use these APIs for tests and smaller use cases where you will be required to create the database, perform actions, verify/show results and  just shutdown.

While this is an interesting API offered by Spring 3, I tried to use this API with one of the interesting ones introduced in Spring 2, the AbstractRoutingDatasource API. Using this API we could have two different data bases and then depending on some criteria shuffle our data in different( Homogeneous/Heterogeneous ) databases. This is an abstract class and you have to implement it for the business criteria for shuffling data . Also the offered API is of type Datasource and is pluggable in the SessionFactory. But by doing this we need standards that will remain same across different databases i.e. SQL,  as the goal is to have an  unaware access to all the databases. So using SpringJdbc looked quite promising and thus accomplished the task.

But, today we  talk about ORMs and JPA standards and will no longer like to write SQL statements in our code. So I tried using this with Hibernate Annotations. Using Hibernate we can easily perform shuffling if the databases are Homogeneous as hibernate uses dialect, generates statements which are database type specific. So what about heterogeneous databases ? I found out if  I discard  some of the features offered by hibernate(HBM2DLL,  Autogenerate etc) I could use hibernate to shuffle data between HSQL and Derby but for H2 it did not work . Maybe be  hibernate can work with some more heterogeneous databases using this way but that I do not know as yet.

So what I ended up by using all these three is :

A Data Router Application

I created a XML file where I enable component scan and transaction support. Also I created a  factory class for loading the whole application context with the required dependencies.

Creating EmbededDasebases was easy, using the builder API we could cerate them. You need to add all the required drives for the database you are creating.

class DatabaseFactory {
    EmbeddedDatabase getHSQLInstance() {
        EmbeddedDatabaseBuilder databaseBuilder = new EmbeddedDatabaseBuilder();
        EmbeddedDatabase embeddedDatabase = databaseBuilder.setType(
        return embeddedDatabase;

    EmbeddedDatabase getDerbyInstance() {
        EmbeddedDatabaseBuilder databaseBuilder = new EmbeddedDatabaseBuilder();
        EmbeddedDatabase embeddedDatabase = databaseBuilder.setType(
        return embeddedDatabase;

I created the schema scripts for both of them as I hibernate would fail to generate schema for both of the. So I could not use the HBM2DLL feature of hibernate and I must genearte schema at the start.

Next I implemented the AbstractRoutingDatabase class and implemented a count based routing logic that would use the databases alternatively. And then plugged it into the DatabaseFactory.

public class CountBasedRoutingDataSource extends AbstractRoutingDataSource {
    long count;
    private int maxCount;

    CountBasedRoutingDataSource(int maxcount) {
        this.maxCount = maxcount;

    protected Object determineCurrentLookupKey() {
        long id = count++ % maxCount;
        return "" + id;
class DatabaseFactory {
    //....other methods
    AbstractRoutingDataSource getRoutingDB(Collection dataSources) {
        CountBasedRoutingDataSource routingDataSource = new CountBasedRoutingDataSource(
        int count = 0;
        for (DataSource dataSource : dataSources) {
            targetDataSources.put("" + count, dataSource);
        return routingDataSource;

Since this is small test application so I created an Employee pojo with Hibernate annotations, created a EmployeeDAO class and then configured the factory for the same.

@Table(name = "Employee")
public class Employee {
    @Column(name = "emp_id")
    private Long id;
    @Column(name = "name", nullable = false)
    private String name;
    @Column(name = "age")
    private int age;

    public String toString() {
        return "[Employee Details :" + id + "-" + name + "-" + age + "]";
public class EmployeeDAO {
    SessionFactory sessionFactory;
    private long nextAvailableId;

    @Transactional(readOnly = true)
    public List getAllEmployees() {
        Criteria criteria = this.sessionFactory.getCurrentSession()
        return criteria.list();

    @Transactional(readOnly = false, propagation = Propagation.REQUIRED)
    public void addEmployee(Employee employee) {

You must take care of generating the Ids for the pojo when using hetorgeneous databases as different mechanims are employed by hibernate to auto-generate id for different databses and it can not adapt.  Also while specifying Hibernate properties we could elimante Dialect property.

Testing such an application was easy  since I was running two databases so I saved some data and then verified the count.

public class TestDatabaseRouting {
    private EmployeeDAO employeedao;

    public void addEmployeeData() {
        Employee employee = new Employee();
        employee = new Employee();
        employee = new Employee();

    public void testDatasourceRouting() throws Exception {
        List<Employee> employees = employeedao.getAllEmployees();
        System.out.println("First List:----> " + employees);
        assertEquals(1, employees.size());
        employees = employeedao.getAllEmployees();
        System.out.println("Second List:----> " + employees);
        assertEquals(2, employees.size());

1 Comment »

  1. can you show the xml file also please

    Comment by Benjamin Dreux — September 14, 2013 @ 11:25 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: