DataSource for Tomcat web app, Spring and Hibernate

Question

Web app runs on Tomcat. Datasource is configured with Spring configuration, and is used by Hibernate.

If we cannot use JNDI, what would you suggest to use as a DataSource?

org.springframework.jdbc.datasource.DriverManagerDataSource will be ok? It's not very good, but sincerely speaking, it can be used on production server, right? Just a bit of headache with too frequent connection reopening.

Also, we can use BasicDataSource from Apache. It's much better of course, but here's the question. IF WE DON'T USE JNDI, THEN:

If every instance of an app will create its own copy of a DataSource, and every DataSource can have 5 open connections, what do we get?
Num_of_running_apps * Num_of_max_active_connections = max active open connection on a DB for this user?

Second question: from the perspective of Hibernate, is there any difference about what datasource implementation is used? Will it work with no matter what datasource perfectly and in a stable way?

Answer 1

If we cannot use JNDI, what would you suggest to use as a DataSource?

Certainly not org.springframework.jdbc.datasource.DriverManagerDataSource in production, this class is just not a connection pool as written in the javadoc:

NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call.

Useful for test or standalone environments outside of a J2EE container, either as a DataSource bean in a corresponding ApplicationContext or in conjunction with a simple JNDI environment. Pool-assuming Connection.close() calls will simply close the Connection, so any DataSource-aware persistence code should work.

Use a standalone connection pool like C3P0 or DBPC. Personally, I would go for C3P0 which is known to behave better than DBCP. Have a look at c3p0 vs. dbcp on the Spring forums and this previous question here on SO.

If every instance of an app will create its own copy of a DataSource, and every DataSource can have 5 open connections, what do we get?

Total # of connections = # of instances of the application x 5

from the perspective of Hibernate, is there any difference about what datasource implementation is used?

There is no difference. Hibernate will use the connection it gets from Spring.