java - SQLite connection pool

Question

I would like to have a connection pool in android since parallel threads have to access the database simultaneously.

Android provides the PooledConnection interface wich is for use with javax.sql connections. Since jdbc for SQLite is not officially supported in Android I was thinking of another approach, to implement a connection pool of SQLite databases.

What do you think about this approach. What are the risks? Did I overlook something?

My code is as follows:

The connection wrapper class for the database:

public class PoolConnection {

    protected SQLiteDatabase sqlDb;
    protected long openedAt;

    private static final String DB_NAME = Environment
            .getExternalStorageDirectory()
            + "/data/DBNAME.sqlite";
    private static final int DB_VERSION = 1;

    private static class DatabaseHelper extends SQLiteOpenHelper {
        DatabaseHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }
        @Override
        public void onCreate(SQLiteDatabase db) {
            // nothing to do here
        }
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // no upgrade planned yet
        }
    }

    public PoolConnection(Context context) throws Exception {
        sqlDb = new DatabaseHelper(context).getWritableDatabase();
        openedAt = System.currentTimeMillis();
    }

    public boolean isAvailable() {
        if (!sqlDb.isOpen() || sqlDb.inTransaction()
                || sqlDb.isDbLockedByOtherThreads()) {
            return false;
        } else {
            return true;
        }
    }

    public void close() throws SQLException {
        sqlDb.close();
    }

    public SQLiteDatabase getConnection() throws SQLException {
        return sqlDb;
    }

    public long getOpenedAt() {
        return openedAt;
    }

}

And the connection pool class:

public class ConnectionPool {

    protected List<PoolConnection> connections;

    protected long maxIdleTime = 30 * 1000;

    public ConnectionPool() {
        connections = Collections
                .synchronizedList(new ArrayList<PoolConnection>());
        new PoolCleaner(maxIdleTime).start();
    }

    public PoolConnection getConnection(Context context) throws Exception {

        synchronized (connections) {

            PoolConnection poolCon = null;

            for (PoolConnection con : connections) {
                poolCon = con;
                if (poolCon.isAvailable()) {
                    return poolCon;
                }
            }

        }

        PoolConnection con = new PoolConnection(context);

        synchronized (connections) {
            connections.add(con);
        }

        return con;

    }

    public void removeExpired() {

        synchronized (connections) {
            for (int i = (connections.size() - 1); i >= 0; i--) {
                PoolConnection con = connections.get(i);
                if (con.isAvailable()
                        && maxIdleTime < (System.currentTimeMillis() - con
                                .getOpenedAt())) {
                    try {
                        con.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                    connections.remove(i);
                }
            }
        }

    }

    class PoolCleaner extends Thread {

        protected long cleaningInterval;
        protected boolean mustStop;

        public PoolCleaner(long cleaningInterval) {
            if (cleaningInterval < 0) {
                throw new IllegalArgumentException(
                        "cleaningInterval must be >= 0");
            }
            this.mustStop = false;
            this.cleaningInterval = cleaningInterval;

            setDaemon(true);
        }

        public void run() {
            while (!mustStop) {
                try {
                    sleep(cleaningInterval);
                } catch (InterruptedException ignore) {
                }

                if (mustStop) {
                    break;
                }

                removeExpired();
            }
        }

        public void halt() {
            mustStop = true;
            synchronized (this) {
                this.interrupt();
            }
        }
    }

}

Answer 1

Waitting for answers