I have been trying to figure out the best way to manage the databases for a multi-organization software service, in which each organization has its own database. I have come up with the following solution, an implementation of the c3p0 ConnectionCustomizer:
Code:
package test.util.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import test.util.ThreadLocalDS;
import com.mchange.v2.c3p0.ConnectionCustomizer;
public class MyConnectionCustomizer implements ConnectionCustomizer {
public void onAcquire(Connection arg0, String arg1) throws Exception {}
public void onCheckIn(Connection arg0, String arg1) throws Exception {
String sql = "use idle"; // move this back to a generic DB
PreparedStatement prep = arg0.prepareStatement(sql);
prep.execute();
}
public void onCheckOut(Connection arg0, String arg1) throws Exception {
String sql = "use " + ThreadLocalDS.get();
PreparedStatement prep = arg0.prepareStatement(sql);
prep.execute();
}
public void onDestroy(Connection arg0, String arg1) throws Exception {}
}
Then the custom implementation is specified as a hibernate/c3p0 property in my hibernate.cfg.xml file:
Code:
<property name="hibernate.c3p0.connectionCustomizerClassName">test.util.db.MyConnectionCustomizer</property>
The ThreadLocalDS is just a thread local variable set to the database schema name, which is set when the application requests a singleton instance of the Hibernate SessionFactory object.
I have seen many people on this board and elsewhere looking for a solution to this type of problem, and this has worked pretty well for me recently. I am looking for any comments on how well this will hold up in a high load/concurrency situation. Obviously if transactions with multiple schemas are interleaved this could cause issues if the thread local variable isn't re-set properly. But in the case where each conversation is with a single database (for example a servlet thread uses the specified schema related to the requesting user), this should work, right? Is there any case where a connection to the wrong database could get passed back to the thread?
Obviously this is a fairly insecure solution because the database user used has to have access to all the databases in the pool.
Login
FAQ
Search