java - DataSource for standalone application - no application server

Question

I want to connect to a MySQL database from a standalone application. There is no application/web server.

This page suggests that I should use DriverManager to create the connection. However this page suggests that connections created from DriverManager cannot be pooled.

However connection pooling is a must for me because the application uses various threads to write to the database concurrently, so closing/recreating connections is not optimal.

1. Which DataSource implementation should I use for this purpose? I looked at MysqlConnectionPoolDataSource but I am not sure if it actually implements a connection pool. I also looked at the Tomcat DataSource which I am sure implements a connection pool but I am not sure if it can be used outside Tomcat.

2. When I close the standalone application what should I do to release the connections/pool properly?

EDIT: It looks like the Tomcat DataSource is usable from standalone application, so I am going to use it. It also has a close method which I assume will release the connections.

Answer 1

Take a look at Apache DBCP and DBUtils

DBCP will give you features for connection pool etc.

DBUtils will make jdbc easier for you.

As mentioned DBPool is also one, there is BoneCP and plenty pooling tools out there.

Take a look at some samples:

• Connection Pooling using Apache common DBCP And DBUtils
• Using Apache Commons DBUtils and DBCP.

Answer 2

Take a look at Apache Cayenne, in doc read about DataSource and faq about connection pools

EDIT:

As a bonus you get modeler (graphical tool for database) and easy integration with Eclipse.

Answer 3

1. I would advise to use a library like DBPool. It'll take a lot of headaches away from you because the developer already took care of them.
2. To ensure proper cleanup of your connection pool, read the documentation about a JVM Shutdown hook.