JDBC and connection pools options

I have to implement a connection pool along with the standard JDBC DAO with an SQLite database. What are the simplest options to implement a connection pool that will reuse the database connections to reduce overhead of the web application? Here is what I have coded:

package persistance;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLite {

    Connection conn = null;
    Statement stat = null;

    public SQLite(String path) {
        String dbPath = path + "GTI525.db";
        System.out.println(dbPath);
        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath);
            stat = conn.createStatement();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public ResultSet query(String sql) {
        ResultSet rs = null;
        try {
            rs = stat.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }
开发者_JAVA技巧}

As others have mentioned, there are a number of prebuilt solutions that you might want to consider.

However, if you want the simplest possible custom pool implementation, you could use a circular array of some reasonable size, like 100. Then just populate it with SQLite objects and place some arbiter in front of it that hands queries out to the objects in the array. Something like:

public ResultSet runQuery(String sql) {
    SQLite connection = null;

    synchronized(this) {
        connection = connectionArray[currentIndex];
        currentIndex++;
        if (currentIndex >= connectionArray.length) {
            currentIndex = 0;
        }
    }

    return connection.query(sql);
}

In practice, however, there is little sense in allocating and opening 100 connections until the application actually needs that many. As such, you would probably want to, at a minimum, add an isBusy() method to your SQLite class, and implement your arbiter class such that it searches for the first SQLite that is not bust and uses that one, allocating a new one only when all the existing instances are busy (and only if there is still space in the array for a new one).

You may also want your SQLite class to verify that the connection is still open in query(), as most databases will close a connection if it sits idle for too long. And this is something that is bound to happen at least occasionally in your connection pool.

Don't write your own database connection pool. There are a number of great off-the-shelf open source implementations.

If it's a web application, all mainstream contemporary application servers include database connection pooling, usually configured via JNDI. Here is an overview of how to set up JNDI-based connection pooling in Tomcat.

Here are some off-the-shelf implementations:

• c3p0
• Proxool
• Apache DBCP
• DBPool

Using straight JDBC and creating your own Connection Pool is no longer in fashion!

I strongly recommend looking at ORM implementations, e.g. Hibernate. You can then plug C3P0 into it which provides connection pooling.

These problems have been solved and are no longer interesting to solve. Writing your own connection pool will be hard to get right and difficult to test. Unless you have a strong reason for writing your own, I strongly recommend Hibernate and C3P0.