Almost all vendors of JDBC drivers implement some sort of connection pooling, and there are many libraries out there that provide this functionality. However, if you want to customize database access with little overhead, it might make sense to implement your own connection pooling. Here is one simple way to do it. This code is built from ideas I came across all over the Internet ...
You can find the source code here. Apart from being efficient, the code also serves to make your data layer portable - can be used with any web server. All you have to do is ensure that the appropriate JDBC driver is available in the classpath, and that the database URL, password, user, and driver class are correct. In NetBeans or Eclipse, you add the JDBC driver by importing the JAR via project properties (libraries). Some IDEs allow you to package the additional libraries in the portable client, so take advantage of it.
The package includes a connection, a driver, and a pool implementation. You can then access them like this:
public class Database {
// Database connection settings
public static String DB_URL = "jdbc:mysql://localhost:3306/timeaccounting";
public static String DB_USER = "root";
public static String DB_PWD = "adminadmin";
public Database() {}
private static SConnectionDriver driver = null;
public static synchronized Connection getConnection() throws SQLException {
if(driver == null) {
try{
driver = new SConnectionDriver(
com.mysql.jdbc.Driver.class.getCanonicalName(),
DB_URL, DB_USER, DB_PWD);
} catch(Exception e){}
}
return DriverManager.getConnection("jdbc:strive:jdcpool");
}
public static void main(String[] args) {
Connection conn = null;
try {
// Obtain a connection from the pool
conn = getConnection();
// Use the connection somehow
if(conn == null) {
System.out.println("Bad connection ...");
} else {
System.out.println("Good connection ...");
}
System.exit(0);
} catch (SQLException ex) {
Logger.getLogger("global").log(Level.SEVERE, null, ex);
} finally {
// Return the connection to the pool
try { conn.close(); } catch (Exception e) {}
}
}
}
In this example, I have a MySQL database called "timeaccounting" that I am interested in accessing, so I set the url, user, and password as shown. I use the singleton approach to determine whether an initial driver has been initialized. Initializing the first driver also creates a pool and is the first connection available. The pool can create more copies as needed, and keep then after use.
It's probably no longer a good practice to use DriverManager (JDBC 1.0, old school), so I'll consider adapting this code to use JNDI soon. Andrew, perhaps you could complete this piece? That one project will need to move to this code ... it's painfully slow because it doesn't use pooling. With this code, your client's desire of database flexibility can be maintained.
