Magician Magician - 1 year ago 69
Java Question

Best way to configure multiple database connections dynamically

I developing a multi-tenant application using Java. In this case, I am using GlassFish for my Application Server. It will be run mainly on AngularJS, with Jersey's RESTful backend. I am not going to use ORM, since most database interactions will coded and optimized into stored procedures.

There is main database, that holds application data, configurations, and tenant lists. Now, every tenant will have their copy of identical database structure in their own database, each with their own unique data. The app will check each key, and determine which key belong to which user, and which tenant. It then will perform that user request on their respective database.

Problem is, Postgres does not allow me to switch database on-the-fly, so it has to be hard coded in connection string, and it won't let me read other database. The only way is to create multiple connections to database. Besides, I don't think it is wise to cross execute stored procedures in database tenant A from tenant B connection.

I have tried to implement using Apache's BasicDataSource successfully, but I can't help to keep thinking that I am missing something, since GlassFish has its own ConnectionPool. Using Apache's in GlassFish's Application server seems redundant, and will beat the purpose of using Application Server instead of plain Servlet. But I can't find any reference on how to configure GlassFish's DataSource without hardcode them in JNDI.

Below is my current Apache's implementation:

public static BasicDataSource createPool(String database) {
BasicDataSource ds = new BasicDataSource();
return ds;

It is impossible for me to create one JNDI for each tenant, besides, I don't know what will the database name be. The only one I know is my main configuration database.

Is there any suggestion on how I can open a connection to unknown database name in GlassFish? How? Or should I stick with Apache? If I stick with Apache, is it better I remove GlassFish altogether and use Tomcat instead?

Thank you

Answer Source

JNDI datasources on Java EE servers such as GlassFish are bind statically and must be fully configured for being working and must not be modified during their life.

A JDBC connection pool is a group of reusable connections for a particular database.

Else, how the server could be able to perform needed optimizations to the database connection pool if the pool was a mix of several database connection pool ? For more details, you can read information about Java EE DataSource here.

In your case, the statically configured pool provided by Glassfish seems irrelevant.
So, if you have installed GlassFish for the connection pool, indeed, you don't need it.
Besides, Java EE Servers have much more overhead (cpu, memory, etc...) that servlet containers as Tomcat since they provide much more features natively (even if GlassFish is not the worst at this game).
So as a general advise, if you don't use many features from Java EE Servers and you have the choice, you should not use them but a servlet container.
The other argument to use a Java EE Server could be using particularities only available for them : EJB, JTA, EAR, vendor specific features, a richer administration console ...

Your idea to use org.apache.commons.dbcp.BasicDataSource to create dynamically the datasources and Tomcat seems matching to your need.