13

I need to manage connections to multiple databases in my web app. following are facts regarding the current implementation:

1- I use Tomcat

2- databases are created dynamically at runtime ( i am using mysql)

without a doubt, having a connection pool to manage database connections is optimal. Since the databases are not known at the start of the application, it was not possible for me to set up datasources and make connection pools. (I could not find a way in Tomcat to make dynamic connection pool: a connection pool that is created at runtime).

my question is: what other options do I have to work efficiently with connections to multiple databases ? (I don't have experience to implement connection pools myself) is there any library that can be used with tomcat and allow me to establish multiple connection pools to different databases at runtime ? if not what do you suggest that I do instead of connection pools ? i am fairly new with this issue therefore please correct and guide me if I am messing up concepts.

Thank you in advance.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • 1
    The word "database" is a bit ambiguous in this context. Do you mean "a database server" or "a database" as in `CREATE DATABASE` or "a database table" as in `CREATE TABLE`? – BalusC Aug 25 '11 at 18:45
  • database as in create database. i use a schema per customer (in mysql a schema is a database). thanks for your help. –  Aug 25 '11 at 18:50

2 Answers2

12

The MySQL JDBC driver allows omitting the database name from the connection URL as follows:

jdbc:mysql://localhost:3306

You only need to specify the database by Connection#setCatalog() or directly in the SQL queries. See also its reference documentation:

If the database is not specified, the connection will be made with no default database. In this case, you will need to either call the setCatalog() method on the Connection instance or fully specify table names using the database name (that is, SELECT dbname.tablename.colname FROM dbname.tablename...) in your SQL. Not specifying the database to use upon connection is generally only useful when building tools that work with multiple databases, such as GUI database managers.

This allows you for creating a single and reuseable connection pooled datasource in Tomcat. You'll perhaps only need to rewrite your connection manager and/or SQL queries.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • 1
    how do I express happiness politely ? I have spent more than a month now searching only to find out its so simple due to vendor specific implementation. Thank you. as you suggested, I will make a connection pooled datasource (something I already managed to do) and will simply rewrite the queries to address the correct schema. thank you very much again :)))))) –  Aug 25 '11 at 19:02
  • 1
    You're welcome. You could offer a bounty or send something from my Amazon wishlist (see also my user profile) :) – BalusC Aug 25 '11 at 19:02
  • 4
    It has arrived: https://plus.google.com/113917986381339001702/posts/2mVyJXuUa27 Once again, thank you very much :) – BalusC Sep 13 '11 at 18:42
  • is there an example we can refer to. i have 70 + databases . currently each of them have a pool where the connections to each db can be found inside it.i want to have a single pool and manage connections to each DB dynamically. any help will be appreciated – Sithija Piyuman Thewa Hettige Mar 07 '19 at 12:41
0

There are enough connection pooling framework in the open. Proxool is definitely among the best. Its pretty flexible and easy to use.

Suraj Chandran
  • 24,433
  • 12
  • 63
  • 94