java sqlite配置和自定义函数

资源

• jetty 

地址

• sqlite

 sqlite JDBC Driver 地址： 和 

jetty配置sqlite

在jetty里的配置（工程MWeb为例）

/MWeb/WebContent/WEB-INF/jetty-web.xml

<?xml version="1.0" encoding="UTF-8" ?>  
<!DOCTYPE Configure PUBLIC "-//Mort Bay Consulting//DTD Configure//EN" "http://www.eclipse.org/jetty/configure.dtd">

<!-- http://www.eclipse.org/jetty/documentation/current/using-jetty-jndi.html#jndi-name-scope -->
<Configure class="org.eclipse.jetty.webapp.WebAppContext">
    <!-- <Set name="contextPath">/server</Set> -->
    <!-- <Set name="war">/opt/myapp/myapp.war</Set> -->


    <!--依赖库： jetty-jndi-xxxxxx.jar和jetty-plus-xxxxxx.jar到%JETTY_HOME%/lib/ext -->
    <!--如果是普通java工程（即嵌入式），添加到构建目录，如lib中，能找到依赖就行，同时也把sqlite-jdbc-3.8.10.2.jar -->
    <!-- 在Configure标签下配置：应用数据源 -->
    <!-- These examples assume that all of the datasources are declared at the 
        JVM scope, but you can, of course, use other scopes. You can configure all 
        JNDI resources in a jetty.xml file or in a WEB-INF/jetty-env.xml file, or 
        a context XML file. -->
    <!-- http://www.eclipse.org/jetty/documentation/current/jndi-datasource-examples.html -->
    <New id="sqlite" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg></Arg>
        <Arg>jdbc/DSDevices</Arg>
        <Arg>
            <New class="org.sqlite.javax.SQLiteConnectionPoolDataSource">
                <!-- 这里使用的是相对路径 -->
                <Set name="Url">jdbc:sqlite:sqlite/data/devices.db</Set>
            </New>
        </Arg>
    </New>
</Configure> 

sqlite自定义函数

为sqlite自定义函数，发现包里有一个例子

Provides an interface for creating SQLite user-defined functions.

A subclass of org.sqlite.Function can be registered with Function.create() and called by the name it was given. All functions must implement xFunc(), which is called when SQLite runs the custom function.

Eg.

      Class.forName("org.sqlite.JDBC");
      Connection conn = DriverManager.getConnection("jdbc:sqlite:");

      Function.create(conn, "myFunc", new Function() {
          protected void xFunc() {
              System.out.println("myFunc called!");
          }
      });

      conn.createStatement().execute("select myFunc();");

Arguments passed to a custom function can be accessed using the protected functions provided. args() returns the number of arguments passed, while value_<type>(int) returns the value of the specific argument. Similarly a function can return a value using the result(<type>) function.

Aggregate functions are not yet supported, but coming soon.

如果像下面这样使用，只能对这个数据源连接有效。。。

    private static void sqliteCustomFunction(boolean trueToCreateOrfalseToDestory) {
        DataSource ds;
        try {
            ds = (DataSource) InitialContext.doLookup("jdbc/DSDevices");
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return;
        }
        // try-with-resources
        try (Connection dsConn = ds.getConnection();) {
            if (trueToCreateOrfalseToDestory) {
                // PHONE_NUMBERS_EQUAL(String phoneNumber1, String phoneNumber2, int useStrictComparation);
                Function.create(dsConn, "PHONE_NUMBERS_EQUAL", new Function() {
                    @Override
                    protected void xFunc() throws SQLException {
                        int args = this.args();
                        if (args < 2) {
                            this.error("argument is too little");
                            return;
                        }
                        String phoneNumber1 = this.value_text(0);
                        String phoneNumber2 = this.value_text(1);
                        boolean useStrictComparation = args > 2 ? 1 == this.value_int(2) : false;
                        boolean ret = PhoneNumberUtils.compare(phoneNumber1, phoneNumber2, useStrictComparation);
                        System.out.println("PhoneNumberUtils.compare(" + phoneNumber1 + ", " + phoneNumber2 + ") = " + ret);
                        this.result(ret ? 1 : 0);
                    }
                });
            } else {
                Function.destroy(dsConn, "PHONE_NUMBERS_EQUAL");
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

根据

public abstract class DB implements Codes
{  
  /**
     * Create a user defined function with given function name and the function object.
     * @param name The function name to be created.
     * @param f SQLite function object.
     * @return <a href="http://www.sqlite.org/c3ref/c_abort.html">Result Codes</a>
     * @throws SQLException
     * @see <a href="http://www.sqlite.org/c3ref/create_function.html">http://www.sqlite.org/c3ref/create_function.html</a>
     */
    public abstract int create_function(String name, Function f) throws SQLException;
｝

从这里可以看出：

The first parameter is the  to which the SQL function is to be added. If an application uses more than one database connection then application-defined SQL functions must be added to each database connection separately.

需要为每个数据库连接都添加一下才可以。

连接池指导

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.

文章：

Since it is single file embedded database, pooling will hardly be beneficial.

Since the original site is down, I will provide the summary:

I have my pooling implemented and 1000 commands using a pooled connection and the pooled speed was much closer to the single-connection speed.

Many non-pooled connections : 2875.0 ms
Many pooled connections     :   93.8 ms
Pooled with reset           :  546.9 ms
One Connection              :   46.9 ms

So the consensus is that pooling is not very beneficial.

Add support for ConnectionPool

bitbucket issue:

连接池例子 

MiniConnectionPoolManager - A lightweight standalone Java JDBC connection pool manager

The standard Java library (JDK 1.5) does not provide a connection pool manager for JDBC database connections. There are open source connection pool managers like:

•  (Apache Commons)

but these are huge complex packages.

Modern JDBC drivers provide implementations of  and . This makes it possible to build a much smaller connection pool manager. If you just need connection pooling, MiniConnectionPoolManager might be the right choice.

MiniConnectionPoolManager may be used in Java servlets as well as in Java standalone applications. It only requires Java 1.5 or newer (or 1.6 if you use getValidConnection()) and has no dependencies on other packages.

API documentation:
Source code:
Download full package:
Source code repository:	/
Related work 1:	(by Alan Burlison), a DataSource wrapper class for MiniConnectionPoolManager, which can be used in JSP SQL tags.
Related work 2:	(), a version of MiniConnectionPoolManager back-ported to Java 1.4 and adapted to  by Thomas Müller.
Related work 3:	: This version of MiniConnectionPoolManager closes unused connections after a timeout.
Related work 4:	of this page, by Oskari Laine.

Examples of how to use the MiniConnectionPoolManager class

For  (embedded mode):

org.h2.jdbcx.JdbcDataSource dataSource = new org.h2.jdbcx.JdbcDataSource();
dataSource.setURL("jdbc:h2:file:c:/temp/testDB");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Apache  (embedded mode):

org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource dataSource = new org.apache.derby.jdbc.EmbeddedConnectionPoolDataSource();
dataSource.setDatabaseName("c:/temp/testDB");
dataSource.setCreateDatabase("create");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For :

Version for :

org.postgresql.ds.PGConnectionPoolDataSource dataSource = new org.postgresql.ds.PGConnectionPoolDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("testDB");
dataSource.setUser("user");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

Version for :

com.impossibl.postgres.jdbc.PGConnectionPoolDataSource dataSource = new com.impossibl.postgres.jdbc.PGConnectionPoolDataSource();
dataSource.setHost("localhost");
dataSource.setDatabase("testDB");
dataSource.setUser("user");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For :

net.sourceforge.jtds.jdbcx.JtdsDataSource dataSource = new net.sourceforge.jtds.jdbcx.JtdsDataSource();
dataSource.setServerName("localhost");
dataSource.setDatabaseName("Northwind");
dataSource.setUser("sa");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Microsoft :

com.microsoft.sqlserver.jdbc.SQLServerXADataSource dataSource = new com.microsoft.sqlserver.jdbc.SQLServerXADataSource();
// The sqljdbc 1.1 documentation, chapter "Using Connection Pooling", recommends to use SQLServerXADataSource
// instead of SQLServerConnectionPoolDataSource.
dataSource.setDatabaseName("Northwind");
dataSource.setServerName("localhost");
dataSource.setUser("sa");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For Oracle (example for Thin driver):

oracle.jdbc.pool.OracleConnectionPoolDataSource dataSource = new oracle.jdbc.pool.OracleConnectionPoolDataSource();
dataSource.setServerName("server1.yourdomain.com");
dataSource.setDriverType("thin");
dataSource.setPortNumber(1521);
dataSource.setServiceName("db1.yourdomain.com");
dataSource.setUser("system");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

For :

com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB dataSource = new com.sap.dbtech.jdbcext.ConnectionPoolDataSourceSapDB();
dataSource.setServerName("dbhost");
dataSource.setDatabaseName("dbname");
dataSource.setUser("user");
dataSource.setPassword("password");
MiniConnectionPoolManager poolMgr = new MiniConnectionPoolManager(dataSource, maxConnections);
...
Connection connection = poolMgr.getConnection();
...
connection.close();

Design pattern for working with JDBC connections

It is important to use error handling to ensure that Connection and Statement objects are always closed, even when an exception occurs.

Example:

public static String getFirstName (int personKey) throws Exception {
   Connection connection = null;
   PreparedStatement statement = null;
   try {
      connection = poolMgr.getConnection();
      final String sql = "select firstName from person where personKey = ?";
      statement = connection.prepareStatement(sql);
      statement.setInt(1, personKey);
      ResultSet rs = statement.executeQuery();
      if (!rs.next()) {
         throw new Exception("Person not found"); }
      return rs.getString(1); }
    finally {
      if (statement != null) {
         statement.close(); }
      if (connection != null) {
         connection.close(); }}}

Author:  (, )

// Copyright 2007-2011 Christian d‘Heureuse, Inventec Informatik AG, Zurich, Switzerland
// www.source-code.biz, www.inventec.ch/chdh
//
// This module is multi-licensed and may be used under the terms
// of any of the following licenses:
//
//  EPL, Eclipse Public License, http://www.eclipse.org/legal
//  LGPL, GNU Lesser General Public License, http://www.gnu.org/licenses/lgpl.html
//  MPL, Mozilla Public License 1.1, http://www.mozilla.org/MPL
//
// Please contact the author if you need another license.
// This module is provided "as is", without warranties of any kind.

package biz.source_code.miniConnectionPoolManager;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.concurrent.Semaphore;
import java.util.concurrent.TimeUnit;
import java.util.LinkedList;
import javax.sql.ConnectionEvent;
import javax.sql.ConnectionEventListener;
import javax.sql.ConnectionPoolDataSource;
import javax.sql.PooledConnection;

/**
* A lightweight standalone JDBC connection pool manager.
*
* <p>The public methods of this class are thread-safe.
*
* <p>Home page: <a href="http://www.source-code.biz/miniconnectionpoolmanager">www.source-code.biz/miniconnectionpoolmanager</a><br>
* Author: Christian d‘Heureuse, Inventec Informatik AG, Zurich, Switzerland<br>
* Multi-licensed: EPL / LGPL / MPL.
*/
public class MiniConnectionPoolManager {

private ConnectionPoolDataSource       dataSource;
private int                            maxConnections;
private long                           timeoutMs;
private PrintWriter                    logWriter;
private Semaphore                      semaphore;
private PoolConnectionEventListener    poolConnectionEventListener;

// The following variables must only be accessed within synchronized blocks.
// @GuardedBy("this") could by used in the future.
private LinkedList<PooledConnection>   recycledConnections;          // list of inactive PooledConnections
private int                            activeConnections;            // number of active (open) connections of this pool
private boolean                        isDisposed;                   // true if this connection pool has been disposed
private boolean                        doPurgeConnection;            // flag to purge the connection currently beeing closed instead of recycling it
private PooledConnection               connectionInTransition;       // a PooledConnection which is currently within a PooledConnection.getConnection() call, or null

/**
* Thrown in {@link #getConnection()} or {@link #getValidConnection()} when no free connection becomes
* available within <code>timeout</code> seconds.
*/
public static class TimeoutException extends RuntimeException {
   private static final long serialVersionUID = 1;
   public TimeoutException () {
      super("Timeout while waiting for a free database connection."); }
   public TimeoutException (String msg) {
      super(msg); }}

/**
* Constructs a MiniConnectionPoolManager object with a timeout of 60 seconds.
*
* @param dataSource
*    the data source for the connections.
* @param maxConnections
*    the maximum number of connections.
*/
public MiniConnectionPoolManager (ConnectionPoolDataSource dataSource, int maxConnections) {
   this(dataSource, maxConnections, 60); }

/**
* Constructs a MiniConnectionPoolManager object.
*
* @param dataSource
*    the data source for the connections.
* @param maxConnections
*    the maximum number of connections.
* @param timeout
*    the maximum time in seconds to wait for a free connection.
*/
public MiniConnectionPoolManager (ConnectionPoolDataSource dataSource, int maxConnections, int timeout) {
   this.dataSource = dataSource;
   this.maxConnections = maxConnections;
   this.timeoutMs = timeout * 1000L;
   try {
      logWriter = dataSource.getLogWriter(); }
    catch (SQLException e) {}
   if (maxConnections < 1) {
      throw new IllegalArgumentException("Invalid maxConnections value."); }
   semaphore = new Semaphore(maxConnections,true);
   recycledConnections = new LinkedList<PooledConnection>();
   poolConnectionEventListener = new PoolConnectionEventListener(); }

/**
* Closes all unused pooled connections.
*/
public synchronized void dispose() throws SQLException {
   if (isDisposed) {
      return; }
   isDisposed = true;
   SQLException e = null;
   while (!recycledConnections.isEmpty()) {
      PooledConnection pconn = recycledConnections.remove();
      try {
         pconn.close(); }
       catch (SQLException e2) {
          if (e == null) {
             e = e2; }}}
   if (e != null) {
      throw e; }}

/**
* Retrieves a connection from the connection pool.
*
* <p>If <code>maxConnections</code> connections are already in use, the method
* waits until a connection becomes available or <code>timeout</code> seconds elapsed.
* When the application is finished using the connection, it must close it
* in order to return it to the pool.
*
* @return
*    a new <code>Connection</code> object.
* @throws TimeoutException
*    when no connection becomes available within <code>timeout</code> seconds.
*/
public Connection getConnection() throws SQLException {
   return getConnection2(timeoutMs); }

private Connection getConnection2 (long timeoutMs) throws SQLException {
   // This routine is unsynchronized, because semaphore.tryAcquire() may block.
   synchronized (this) {
      if (isDisposed) {
         throw new IllegalStateException("Connection pool has been disposed."); }}
   try {
      if (!semaphore.tryAcquire(timeoutMs, TimeUnit.MILLISECONDS)) {
         throw new TimeoutException(); }}
    catch (InterruptedException e) {
      throw new RuntimeException("Interrupted while waiting for a database connection.",e); }
   boolean ok = false;
   try {
      Connection conn = getConnection3();
      ok = true;
      return conn; }
    finally {
      if (!ok) {
         semaphore.release(); }}}

private synchronized Connection getConnection3() throws SQLException {
   if (isDisposed) {                                       // test again within synchronized lock
      throw new IllegalStateException("Connection pool has been disposed."); }
   PooledConnection pconn;
   if (!recycledConnections.isEmpty()) {
      pconn = recycledConnections.remove(); }
    else {
      pconn = dataSource.getPooledConnection();
      pconn.addConnectionEventListener(poolConnectionEventListener); }
   Connection conn;
   try {
      // The JDBC driver may call ConnectionEventListener.connectionErrorOccurred()
      // from within PooledConnection.getConnection(). To detect this within
      // disposeConnection(), we temporarily set connectionInTransition.
      connectionInTransition = pconn;
      conn = pconn.getConnection(); }
    finally {
      connectionInTransition = null; }
   activeConnections++;
   assertInnerState();
   return conn; }

/**
* Retrieves a connection from the connection pool and ensures that it is valid
* by calling {@link Connection#isValid(int)}.
*
* <p>If a connection is not valid, the method tries to get another connection
* until one is valid (or a timeout occurs).
*
* <p>Pooled connections may become invalid when e.g. the database server is
* restarted.
*
* <p>This method is slower than {@link #getConnection()} because the JDBC
* driver has to send an extra command to the database server to test the connection.
*
* <p>This method requires Java 1.6 or newer.
*
* @throws TimeoutException
*    when no valid connection becomes available within <code>timeout</code> seconds.
*/
public Connection getValidConnection() {
   long time = System.currentTimeMillis();
   long timeoutTime = time + timeoutMs;
   int triesWithoutDelay = getInactiveConnections() + 1;
   while (true) {
      Connection conn = getValidConnection2(time, timeoutTime);
      if (conn != null) {
         return conn; }
      triesWithoutDelay--;
      if (triesWithoutDelay <= 0) {
         triesWithoutDelay = 0;
         try {
            Thread.sleep(250); }
          catch (InterruptedException e) {
            throw new RuntimeException("Interrupted while waiting for a valid database connection.", e); }}
      time = System.currentTimeMillis();
      if (time >= timeoutTime) {
         throw new TimeoutException("Timeout while waiting for a valid database connection."); }}}

private Connection getValidConnection2 (long time, long timeoutTime) {
   long rtime = Math.max(1, timeoutTime - time);
   Connection conn;
   try {
      conn = getConnection2(rtime); }
    catch (SQLException e) {
      return null; }
   rtime = timeoutTime - System.currentTimeMillis();
   int rtimeSecs = Math.max(1, (int)((rtime+999)/1000));
   try {
      if (conn.isValid(rtimeSecs)) {
         return conn; }}
    catch (SQLException e) {}
       // This Exception should never occur. If it nevertheless occurs, it‘s because of an error in the
       // JDBC driver which we ignore and assume that the connection is not valid.
   // When isValid() returns false, the JDBC driver should have already called connectionErrorOccurred()
   // and the PooledConnection has been removed from the pool, i.e. the PooledConnection will
   // not be added to recycledConnections when Connection.close() is called.
   // But to be sure that this works even with a faulty JDBC driver, we call purgeConnection().
   purgeConnection(conn);
   return null; }

// Purges the PooledConnection associated with the passed Connection from the connection pool.
private synchronized void purgeConnection (Connection conn) {
   try {
      doPurgeConnection = true;
      // (A potential problem of this program logic is that setting the doPurgeConnection flag
      // has an effect only if the JDBC driver calls connectionClosed() synchronously within
      // Connection.close().)
      conn.close(); }
    catch (SQLException e) {}
      // ignore exception from close()
    finally {
      doPurgeConnection = false; }}

private synchronized void recycleConnection (PooledConnection pconn) {
   if (isDisposed || doPurgeConnection) {
      disposeConnection(pconn);
      return; }
   if (activeConnections <= 0) {
      throw new AssertionError(); }
   activeConnections--;
   semaphore.release();
   recycledConnections.add(pconn);
   assertInnerState(); }

private synchronized void disposeConnection (PooledConnection pconn) {
   pconn.removeConnectionEventListener(poolConnectionEventListener);
   if (!recycledConnections.remove(pconn) && pconn != connectionInTransition) {
      // If the PooledConnection is not in the recycledConnections list
      // and is not currently within a PooledConnection.getConnection() call,
      // we assume that the connection was active.
      if (activeConnections <= 0) {
         throw new AssertionError(); }
      activeConnections--;
      semaphore.release(); }
   closeConnectionAndIgnoreException(pconn);
   assertInnerState(); }

private void closeConnectionAndIgnoreException (PooledConnection pconn) {
   try {
      pconn.close(); }
    catch (SQLException e) {
      log("Error while closing database connection: "+e.toString()); }}

private void log (String msg) {
   String s = "MiniConnectionPoolManager: "+msg;
   try {
      if (logWriter == null) {
         System.err.println(s); }
       else {
         logWriter.println(s); }}
    catch (Exception e) {}}

private synchronized void assertInnerState() {
   if (activeConnections < 0) {
      throw new AssertionError(); }
   if (activeConnections + recycledConnections.size() > maxConnections) {
      throw new AssertionError(); }
   if (activeConnections + semaphore.availablePermits() > maxConnections) {
      throw new AssertionError(); }}

private class PoolConnectionEventListener implements ConnectionEventListener {
   public void connectionClosed (ConnectionEvent event) {
      PooledConnection pconn = (PooledConnection)event.getSource();
      recycleConnection(pconn); }
   public void connectionErrorOccurred (ConnectionEvent event) {
      PooledConnection pconn = (PooledConnection)event.getSource();
      disposeConnection(pconn); }}

/**
* Returns the number of active (open) connections of this pool.
*
* <p>This is the number of <code>Connection</code> objects that have been
* issued by {@link #getConnection()}, for which <code>Connection.close()</code>
* has not yet been called.
*
* @return
*    the number of active connections.
**/
public synchronized int getActiveConnections() {
   return activeConnections; }

/**
* Returns the number of inactive (unused) connections in this pool.
*
* <p>This is the number of internally kept recycled connections,
* for which <code>Connection.close()</code> has been called and which
* have not yet been reused.
*
* @return
*    the number of inactive connections.
**/
public synchronized int getInactiveConnections() {
   return recycledConnections.size(); }

} // end class MiniConnectionPoolManager

http://stackoverflow.com/questions/2493331/what-are-the-best-practices-for-sqlite-on-android

If you look back at my earlier post, , I demonstrated that more than one sqlite connection was really bad. Bad-bad. OK, so now what? How do you keep one connection and manage it?

Back when I worked on some earlier versions of ORMLite, I coded this big, complex framework that would do reference counting, and close the connection when nobody had a reference to it, and that worked pretty good.  The downside?  You had to extend my base classes for Activity, Service, etc, and if you wanted to integrate my code with either your class hierarchy, or worse, a 3rd party, it was a huge pain.

It kind of dawned on me.  The way SQLite works, it would be basically impossible to corrupt your database, unless there’s a bug in the SQLite code, or a hardware issue.  What I’m going to say is controversial, but I’ve done some snooping and testing, and I’m 99.99% sure its the way to go.

 

Keep a single SQLiteOpenHelper instance in a static context.  Do lazy initialization, and synchronize that method.  When do you close it?  You don’t.  When the app shuts down, it’ll let go of the file reference, if its even holding on to it.

What?!?!?!?!?!?! You have to close your databases!!!!!!!

Settle down.  It works.  Chances are, if you have a db in your app, you’re probably using it in most/all of your Activities and Services.  The “connection” is just a file handle.  When you do writes, they are flushed to disk immediately.

“But Kevin, what about that ‘close() was never explicitly called on database’ exception?”

If you pay attention, you don’t get that exception when the connection is just “hanging out”.  You get it when you ALREADY have a connection that was opened, and you try to open another one.  If you’re doing it right, you only open the connection once.  Presumably, that error triggers when the system notices somebody else has a lock on the file (I’d guess a read lock, because even though you get the exception, you can still see the DB).

What would it look like?

public class DatabaseHelper extends OrmLiteSqliteOpenHelper
{
    private static DatabaseHelper instance;

    public static synchronized DatabaseHelper getHelper(Context context)
    {
        if (instance == null)
            instance = new DatabaseHelper(context);

        return instance;
    }
//Other stuff... 
}

Boom. That’s it. It’ll work.

Android上使用sqlite

基本操作的部分，大家都很熟悉了，这里根据个人切身经验，总结了一些经常遇到的，也需要注意的一些问题，与大家分享，水平有限，不妥或者错误的地方还望指出。

• 多线程读写

SQLite实质上是将数据写入一个文件，通常情况下，在应用的包名下面都能找到xxx.db的文件，拥有root权限的手机，可以通过adb shell，看到data/data/packagename/databases/xxx.db这样的文件。

我们可以得知SQLite是文件级别的锁：多个线程可以同时读，但是同时只能有一个线程写。Android提供了SqliteOpenHelper类，加入Java的锁机制以便调用。

如果多线程同时读写（这里的指不同的线程用使用的是不同的Helper实例），后面的就会遇到android.database.sqlite.SQLiteException: database is locked这样的异常。
对于这样的问题，解决的办法就是keep single sqlite connection，保持单个SqliteOpenHelper实例，同时对所有数据库操作的方法添加synchronized关键字。
如下所示：

复制内容到剪贴板

代码:

public class DatabaseHelper extends SQLiteOpenHelper {
        public static final String TAG = "DatabaseHelper";
        private static final String DB_NAME = "practice.db";
        private static final int DB_VERSION = 1;

        private Context mContext;
        private static DatabaseHelper mInstance;

        private DatabaseHelper(Context context) {
                super(context, DB_NAME, null, DB_VERSION);
        }

        public synchronized static DatabaseHelper getInstance(Context context) {
                if (mInstance == null) {
                        mInstance = new DatabaseHelper(context);
                }
                return mInstance;
        }

        
        public void onCreate(SQLiteDatabase db) {
                // TODO Auto-generated method stub

        }

        
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
                // TODO Auto-generated method stub

        }
public synchronized void queryMethod() {
                SQLiteDatabase readableDatabase = getReadableDatabase();
                //read operation
        }
        
        public void updateMethod() {
                SQLiteDatabase writableDatabase = getWritableDatabase();
                //update operation
        }
}

Android为我们提供了SqliteOpenHelper类，我们可以通过getWritableDatabase或者getReadableDatabase拿到SQLiteDatabase对象，然后执行相关方法。这2个方法名称容易给人误解，我也在很长的一段时间内想当然的认为getReadabeDatabase就是获取一个只读的数据库，可以获取很多次，多个线程同时读，用完就关闭，实际上getReadableDatabase先以读写方式打开数据库，如果数据库的磁盘空间满了，就会打开失败，当打开失败后会继续尝试以只读方式打开数据库。

复制内容到剪贴板

代码:

public synchronized SQLiteDatabase getReadableDatabase() {
        if (mDatabase != null && mDatabase.isOpen()) {
            return mDatabase;  // The database is already open for business
        }

        if (mIsInitializing) {
            throw new IllegalStateException("getReadableDatabase called recursively");
        }

        try {
            return getWritableDatabase();
        } catch (SQLiteException e) {
            if (mName == null) throw e;  // Can‘t open a temp database read-only!
            Log.e(TAG, "Couldn‘t open " + mName + " for writing (will try read-only):", e);
        }

        SQLiteDatabase db = null;
        try {
            mIsInitializing = true;
            String path = mContext.getDatabasePath(mName).getPath();
            db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READONLY);
            if (db.getVersion() != mNewVersion) {
                throw new SQLiteException("Can‘t upgrade read-only database from version " +
                        db.getVersion() + " to " + mNewVersion + ": " + path);
            }

            onOpen(db);
            Log.w(TAG, "Opened " + mName + " in read-only mode");
            mDatabase = db;
            return mDatabase;
        } finally {
            mIsInitializing = false;
            if (db != null && db != mDatabase) db.close();
        }
    }

在多线程中，如果第一个线程先调用getWritableDatabase，后面线程再次调用，或者第一个线程先调用getReadableDatabase，后面的线程调用getWritableDatabase，那么后面的这个方法是会失败的，因为数据库文件打开后会加锁，必须等前面的关闭后后面的调用才能正常执行，正是因为这个原因，可以1 Write+Many Read(有可能产生冲突，因为第一个getReadableDatabase有可能先于getWritableDatabase执行，导致后面的失败)，也可以Many Read，但是不可能Many Write。所以使用单例加上同步的数据库操作方法，就不会出现死锁的问题，这部分例子请参照附件，多线程可以运行的很好，另外关于Sqlite database locking collisions example，网上有很不错的一个例子，可以去下载。

其实我觉得理论上可以修改getReadableDatabase方法，打开的数据库都是Read Only的，这样就能同时1 Write+Many Read，只不过要保证打开之前，数据库要创建或者升级好，这样读操作就不会互斥写操作，效率相对更高。
关于数据库关闭的问题，在下面好的习惯中会专门说明。

• 事务

接触过数据库的人，对事务这个概念一定不陌生，它是原子性的，要么执行成功，执行一半失败后会回滚，这样就能保证数据的完整性。SQLiteDatabase也提供了Transaction的相关方法，常见用法：

复制内容到剪贴板

代码:

db.beginTransaction();
   try {
     ...
     db.setTransactionSuccessful();
   } finally {
     db.endTransaction();
   }

使用事务对于批量更新有极大的好处，因为单次更新会频繁的调用数据库，曾经我同步过联系人，没使用事务之前，300个联系人写入自己的数据库大概需要3~5秒钟的时间，引入事务后，读取联系人的时间没有减少，但是所有更新的时间降为200ms级，提升极为明显。

• 升级

在应用迭代多个版本后，随着功能的增加和改变，数据库改变是很常见的事情，由于数据库中的数据一般是需要永久保存的，应用更新后，用户不希望数据丢失，特别是如果应用有几十万，百万级的用户量，如果很粗鲁的丢弃旧版本数据库中数据，对用户体验是很不好的，如果你没有提供云端备份的方案，就需要为用户保留旧的数据，即便数据库结构要发生变化。
实际上多次数据库变动的升级是很痛苦的事情，要考虑每一个旧的版本，理论上用户可以从任何一个旧的版本直接升级到最新版本，我们需要考虑每一种情况。在onUpgrade方法中，针对每一种版本号，先把旧的临时数据保存下来，删去旧的表，创建新表，然后将数据根据情况插入到新表中，不需要的字段可以丢弃，新增字段填默认值，数据可以临时存放到一个数组中，或者可以临时cache到文件中，最后将临时文件清空。
更新操作可以使用事务提高效率，另外需要知道的是I/O操作时耗时的，如果数据量较大，还需要放到单独的线程中处理，防止阻塞UI。

• 数据初始化

我们也经常会遇到数据库中需要初始化数据，比如城市，机场，号码归属地等信息，如果数据量不是很大，我们可以处理后放到asset或者raw文件下，创建数据库后导入进去，并且在2.3以前，asset中文件有大小限制，文件大小不能超过1M，否则AssetManager或Resources classes方法来获取InputStream，将抛出DEBUG/asset(1123): Data exceeds UNCOMpreSS_DATA_MAX的java.io.IOException异常。

解决这个问题有4个方法：
1.改名称（最简单）：
aapt工具在打包apk文件时，会将资源文件压缩以减小安装包大小（raw文件夹下的资源则不受影响）。但是可以通过修改文件成下面的扩展名，逃避检查。

复制内容到剪贴板

代码:

/* these formats are already compressed, or don‘t compress well */ 
　　static const char* kNoCompressExt[] = { 
　　".jpg", ".jpeg", ".png", ".gif", 
　　".wav", ".mp2", ".mp3", ".ogg", ".aac", 
　　".mpg", ".mpeg", ".mid", ".midi", ".smf", ".jet", 
　　".rtttl", ".imy", ".xmf", ".mp4", ".m4a", 
　　".m4v", ".3gp", ".3gpp", ".3g2", ".3gpp2", 
　　".amr", ".awb", ".wma", ".wmv" 
　　};

2.压缩：
如果原文件能压缩到1M一下，可以先压缩成zip或者rar格式，然后解压将数据库文件释放到相应位置。
3.分割文件：
大的数据，分割成多个小数据文件，info1.dat,info2.dat…，分别读取这些文件数据插入数据库。
4.网络：
上面的几种方法都是将初始化数据放在安装包中，这样无疑会增加安装包大小，如果必要情况下，可以将数据放到服务器上，创建数据库后，通过HTTP请求，获取JSON，XML数据或者数据库文件，然后经过处理入库。

• 除此之外要有几点要注意：

1.关闭Cursor
Cursor如果不关闭，虽然不会导致出错，但是Log中会有错误提示，还是严谨点，Activity中有startManagingCursor的方法，Activity会在生命周期结束时关闭这些Cursor，其他地方，我们则需要用完关闭，以前需要Cursor的Adapter则需要在changeCursor时判断关闭old cursor，在Activity的onDestory方法中关闭cursor。
2.关闭DatabaseHelper
在上述单例Helper例子中，其实一直没有关闭数据库，但是我们阅读getReadabeDatabase和getWritableDatabas的方法，他们会关闭Old SQLiteDatabase的，我们只需要在Application的onTerminal方法中关闭即可，这样也能避免多线程中，一个线程关闭了数据库，导致其他线程使用的时候失败的问题。
实质上，数据库是一个文件引用，单例模式下，不关闭也不会出现问题，让它保持随单例的生命周期关闭就好了。
3.在循环外面获取ColumnIndex，如果表中列不是很多，每次查询又返回所有列的话，可以将列的index定义到TABLE_COLUMNS中去，这样每次获取指定列数据的话，就不用去查找index了。
4.数据库存放的数据类型
Android提供了多种数据存储的方法，文件，数据库，SharePreference，网络等，要根据情况选择合适的方式，不要把什么东西都往数据库中塞。
下面的几种情况就不适合放到数据库中：
1)图片等二进制数据：如果是图片的话，可以将文件名称或者路径保存到数据库中，真正的文件可以作为缓存文件保存在文件系统中。
2)临时数据：定位获取到的Location，登录的Session等。
3)日志数据：可以写入文件中，通常是log_xxxx.txt。