JDBC and connection pools options

Question

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;
    }
}

Answer 1

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.