Multi-tenancy in Hibernate - multi databases (SQLite)

Question

I'm trying to do multi-tenancy with multi databases. From this chapter I took MultiTenantConnectionProviderImpl.

And here I have problem. Eclipse cannot find class ConnectionProviderUtils. I'm using Maven with dependency:

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
        <version>4.1.4.Final</version>
    </dependency>

score 5 · Accepted Answer · edited May 23 '17 at 12:02

I hate to disapoint you but I was running in the same problem a while back. The point is the ConnectionProviderUtil is quite misleading in the documentation. There is no such thing. The ConnectionProviderUtil is something you have to implement yourself. I implemented this by constructing my own DataSource (a c3p0 pooled one) in the MultiTenantConnectionProvider and handing out connection from there.

So you have to implement it from scratch yourself. For reference here is my way to a Solution. Setting up a MultiTenantConnectionProvider using Hibernate 4.2 and Spring 3.1.1

For the multi DB approach you can just autowire the different DataSources into the MultiTenantConnectionProvider and switch based on the TenantIdentifier. See this answer for more details: https://stackoverflow.com/a/16769595/2319179

Edit: If you use Spring you can set up a DataSource in the appcontext like this:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="<jdbcdriver>" />
            <property name="url" value="jdbc:SQLServer://<host>:<port>;databaseName=<dbname>" />
            <property name="username" value="<user>" />
            <property name="password" value="<pw>" />
       </bean>

If you need to build it from java you can do it like this:

        cpds = new DriverManagerDataSource();
cpds.setDriverClass(<jdbc.driver>);
cpds.setJdbcUrl(<jdbc.url>);
cpds.setUser("<user>");
cpds.setPassword("<pw>"));

A quick googlesearch should bring up the right driver.

How can I create DataSource for SQLite? Is there any given things or I need to code everything? — MAGx2, May 28 '13 at 13:06
I see that DriverManagerDataSource is only in Spring. Can I do it somehow in Java SE? — MAGx2, May 28 '13 at 13:39
there should be the java.sql.DataSource available. Which is also the core of the DriverManagerDataSource. — Carsten, May 28 '13 at 14:12
When you creating new tenanct where you creating new database (and all tables)? — MAGx2, May 29 '13 at 08:40
For a lack of a better solution for now I am using native SQL and JDBCTemplate to create a new Schema/DB. The tables are usualy created as outlined in your other question: http://stackoverflow.com/a/16807512/2319179 — Carsten, May 29 '13 at 08:57

score 0 · Answer 2 · answered Mar 31 '14 at 09:29

ConnectionProvider is what you use to customize your strategy for obtaining connections. Provided all schemas are the same, this is one of the best places to implement multi-tenancy.

Along with the ConnectionProvider, you'll need a ThreadLocal to hold the "tenancy" and probably a ServletFilter to set it up (from session variable, set at login). This is similar to how Spring's OpenSessionInViewFilter works.

All in all, this can provide a very simple & effective solution:

http://literatejava.com/hibernate/multi-tenancy-architecture-with-hibernate/

Multi-tenancy in Hibernate - multi databases (SQLite)

2 Answers2