java sqlite配置和自定义函数

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自定义函数，发现包里有一个例子

org.sqlite.Function

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

 

 

从这里Create Or Redefine SQL Functions可以看出：

The first parameter is the database connection 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.

文章：

• JavaXT Connection Pool
• How to configure SQLite in Tomcat 6?
• SQLite Connection Pool in Java - Locked Database

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

https://web.archive.org/web/20130410192252/http://sqlite.phxsoftware.com/forums/p/682/3034.aspx

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.

http://www.sqlite.org/faq.html#q5

http://www.sqlite.org/threadsafe.html

 

Add support for ConnectionPool

bitbucket issue: https://bitbucket.org/xerial/sqlite-jdbc/issue/18

连接池例子 miniconnectionpoolmanager

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:

• DBCP (Apache Commons)
• c3p0
• Proxool
• BoneCP
• Tomcat JDBC Connection Pool

but these are huge complex packages.

Modern JDBC drivers provide implementations of ConnectionPoolDataSource and PooledConnection. 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:	MiniConnectionPoolManager.html
Source code:	MiniConnectionPoolManager.java
Download full package:	miniConnectionPoolManager.zip
Source code repository:	browse / Subversion URL
Related work 1:	org.opensolaris.db.DbDataSource (by Alan Burlison), a DataSource wrapper class for MiniConnectionPoolManager, which can be used in JSP SQL tags.
Related work 2:	org.h2.jdbcx.JdbcConnectionPool (source code), a version of MiniConnectionPoolManager back-ported to Java 1.4 and adapted to H2 by Thomas Müller.
Related work 3:	Extended version by Daniel Jurado: This version of MiniConnectionPoolManager closes unused connections after a timeout.
Related work 4:	Finnish translation of this page, by Oskari Laine.

Examples of how to use the MiniConnectionPoolManager class

For H2 (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 Derby (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 PostgreSQL:

Version for pgjdbc:

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 pgjdbc-ng:

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 JTDS:

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 SQL Server:

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 MaxDB:

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: Christian d‘Heureuse (www.source-code.biz, www.inventec.ch/chdh)

// 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

Single SQLite connection

If you look back at my earlier post, android sqlite locking, 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

• What are the best practices for SQLite on 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;
        }

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

        }

        @Override
        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);
         &