title: SQLite connection pool
body: <p>I would like to have a connection pool in android since parallel threads have to access the database simultaneously.</p>
  
  <p>Android provides the <code>PooledConnection</code> 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.</p>
  
  <p>What do you think about this approach. What are the risks? Did I overlook something?</p>
  
  <p>My code is as follows:</p>
  
  <p><strong>The connection wrapper class for the database:</strong></p>
  
  <pre><code>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;
      }
  
  }
  </code></pre>
  
  <p><strong>And the connection pool class:</strong></p>
  
  <pre><code>public class ConnectionPool {
  
      protected List&lt;PoolConnection&gt; connections;
  
      protected long maxIdleTime = 30 * 1000;
  
      public ConnectionPool() {
          connections = Collections
                  .synchronizedList(new ArrayList&lt;PoolConnection&gt;());
          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 &gt;= 0; i--) {
                  PoolConnection con = connections.get(i);
                  if (con.isAvailable()
                          &amp;&amp; maxIdleTime &lt; (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 &lt; 0) {
                  throw new IllegalArgumentException(
                          "cleaningInterval must be &gt;= 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();
              }
          }
      }
  
  }
  </code></pre>