java sqlite配置和自定义函数

资源

jetty

sqlite

sqlite JDBC Driver 地址：bitbucket代码托管和 Github代码托管

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. -->
    <!-- https://wiki.eclipse.org/Jetty/Feature/JNDI -->
    <!-- http://www.eclipse.org/jetty/documentation/9.2.8.v20150217/using-jetty-jndi.html -->
    <!-- http://www.eclipse.org/jetty/documentation/current/jndi-datasource-examples.html -->
    <!-- http://www.eclipse.org/jetty/documentation/9.2.1.v20140609/jndi-embedded.html -->

    <New id="sqlite" class="org.eclipse.jetty.plus.jndi.Resource">
        <Arg></Arg> <!-- scope, but empty arg -->
        <Arg>jdbc/DSDevices</Arg> <!-- name to bind as -->
        <Arg>
            <New class="org.sqlite.javax.SQLiteConnectionPoolDataSource">
                <!-- 这里使用的是相对路径 -->
                <Set name="Url">jdbc:sqlite:db/devices.db</Set>
            </New>
        </Arg> <!-- value, the object to bind -->
    </New>
    <!-- new File("db").mkdirs() -->
    <New class="java.io.File">
        <Arg>db</Arg>
        <Call name="mkdirs"></Call>
    </New>
</Configure>

以上xml配置等同于如下Java代码

    /**
     * Register a SQLite DataSource scoped to the webapp
     * <p/>
     * sets up some JNDI entries and deploys a webapp that references these JNDI
     * entries in code. We'll use some mocked up classes for the transaction
     * manager and the DataSource in this example for simplicity:
     * 
     * @param scope e.g. a WebAppContext
     * @see http://www.eclipse.org/jetty/documentation/9.2.1.v20140609/jndi-embedded.html
     */
    private static void registerSQLiteDataSource(Object scope) {

        new File("db").mkdirs();
        
        SQLiteConnectionPoolDataSource sqliteConnPoolDataSource = new SQLiteConnectionPoolDataSource();
        sqliteConnPoolDataSource.setUrl("jdbc:sqlite:db/devices.db");

        try {
            org.eclipse.jetty.plus.jndi.Resource jndiRes = new org.eclipse.jetty.plus.jndi.Resource(null,
                    "jdbc/DSDevices", sqliteConnPoolDataSource);

        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

sqlite自定义函数

为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.

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

运行时可加载扩展 Run-Time Loadable Extensions

可以通过加载外部二进制库（windows的dll，linux的so）扩展功能，而且可以动态加载

加载外部文件的API

sqlite3_load_extension()

int sqlite3_load_extension(
  sqlite3 *db,          /* Load the extension into this database connection */
  const char *zFile,    /* Name of the shared library containing extension */
  const char *zProc,    /* Entry point.  Derived from zFile if 0 */
  char **pzErrMsg       /* Put error message here if not 0 */
);

sqlite3_enable_load_extension()

sql

load_extension(X)

load_extension(X,Y)

The load_extension(X,Y) function loads SQLite extensions out of the shared library file named X using the entry point Y.

The result of load_extension() is always a NULL.

If Y is omitted then the default entry point name is used. The load_extension() function raises an exception if the extension fails to load or initialize correctly.

加载的外部库入口函数名字规则，默认从文件名字（windows排除后缀.dll，linux排除lib前缀和.so以及版本号）中获取，标记为x，则默认入库函数为sqlite3_X_init。

如/usr/lib/libmathfunc-4.8.so的入口点就是sqlite3_mathfunc_init，"./SpellFixExt.dll" 的入口就是"sqlite3_spellfixext_init"。

对于命令行，可以通过 .load ./YourCode 加载，如果非默认规则名字，需要如下命令：

.load ./YourCode nonstandard_entry_point

编写可加载扩展 Programming Loadable Extensions

/* Add your header comment here */
#include <sqlite3ext.h> /* Do not use <sqlite3.h>! */
SQLITE_EXTENSION_INIT1

/* Insert your extension code here */

#ifdef _WIN32
__declspec(dllexport)
#endif
/* TODO: Change the entry point name so that "extension" is replaced by
** text derived from the shared library filename as follows:  Copy every
** ASCII alphabetic character from the filename after the last "/" through
** the next following ".", converting each character to lowercase, and
** discarding the first three characters if they are "lib".
*/
int sqlite3_extension_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  int rc = SQLITE_OK;
  SQLITE_EXTENSION_INIT2(pApi);
  /* Insert here calls to
  **     sqlite3_create_function_v2(),
  **     sqlite3_create_collation_v2(),
  **     sqlite3_create_module_v2(), and/or
  **     sqlite3_vfs_register()
  ** to register the new features that your extension adds.
  */
  return rc;
}

静态地链接一个运行时扩展 Statically Linking A Run-Time Loadable Extension

自动加载静态链接扩展 Automatically Load Statically Linked Extensions 接口为：

sqlite3_auto_extension()

int sqlite3_auto_extension(void (*xEntryPoint)(void));

这样每个数据库连接（database connection）被创建时自动调用。

一些内置函数，部分

/************** Begin file func.c ********************************************/
/*
** 2002 February 23
**
** The author disclaims copyright to this source code.  In place of
** a legal notice, here is a blessing:
**
**    May you do good and not evil.
**    May you find forgiveness for yourself and forgive others.
**    May you share freely, never taking more than you give.
**
*************************************************************************
** This file contains the C-language implementations for many of the SQL
** functions of SQLite.  (Some function, and in particular the date and
** time functions, are implemented separately.)
*/
/* #include "sqliteInt.h" */
/* #include <stdlib.h> */
/* #include <assert.h> */
/* #include "vdbeInt.h" */

/*
** Return the collating function associated with a function.
*/
static CollSeq *sqlite3GetFuncCollSeq(sqlite3_context *context){
  VdbeOp *pOp;
  assert( context->pVdbe!=0 );
  pOp = &context->pVdbe->aOp[context->iOp-1];
  assert( pOp->opcode==OP_CollSeq );
  assert( pOp->p4type==P4_COLLSEQ );
  return pOp->p4.pColl;
}

/*
** Indicate that the accumulator load should be skipped on this
** iteration of the aggregate loop.
*/
static void sqlite3SkipAccumulatorLoad(sqlite3_context *context){
  context->skipFlag = 1;
}

/*
** Implementation of the non-aggregate min() and max() functions
*/
static void minmaxFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  int i;
  int mask;    /* 0 for min() or 0xffffffff for max() */
  int iBest;
  CollSeq *pColl;

  assert( argc>1 );
  mask = sqlite3_user_data(context)==0 ? 0 : -1;
  pColl = sqlite3GetFuncCollSeq(context);
  assert( pColl );
  assert( mask==-1 || mask==0 );
  iBest = 0;
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  for(i=1; i<argc; i++){
    if( sqlite3_value_type(argv[i])==SQLITE_NULL ) return;
    if( (sqlite3MemCompare(argv[iBest], argv[i], pColl)^mask)>=0 ){
      testcase( mask==0 );
      iBest = i;
    }
  }
  sqlite3_result_value(context, argv[iBest]);
}

/*
** Return the type of the argument.
*/
static void typeofFunc(
  sqlite3_context *context,
  int NotUsed,
  sqlite3_value **argv
){
  const char *z = 0;
  UNUSED_PARAMETER(NotUsed);
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE_INTEGER: z = "integer"; break;
    case SQLITE_TEXT:    z = "text";    break;
    case SQLITE_FLOAT:   z = "real";    break;
    case SQLITE_BLOB:    z = "blob";    break;
    default:             z = "null";    break;
  }
  sqlite3_result_text(context, z, -1, SQLITE_STATIC);
}


/*
** Implementation of the length() function
*/
static void lengthFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  int len;

  assert( argc==1 );
  UNUSED_PARAMETER(argc);
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE_BLOB:
    case SQLITE_INTEGER:
    case SQLITE_FLOAT: {
      sqlite3_result_int(context, sqlite3_value_bytes(argv[0]));
      break;
    }
    case SQLITE_TEXT: {
      const unsigned char *z = sqlite3_value_text(argv[0]);
      if( z==0 ) return;
      len = 0;
      while( *z ){
        len++;
        SQLITE_SKIP_UTF8(z);
      }
      sqlite3_result_int(context, len);
      break;
    }
    default: {
      sqlite3_result_null(context);
      break;
    }
  }
}

/*
** Implementation of the abs() function.
**
** IMP: R-23979-26855 The abs(X) function returns the absolute value of
** the numeric argument X. 
*/
static void absFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  assert( argc==1 );
  UNUSED_PARAMETER(argc);
  switch( sqlite3_value_type(argv[0]) ){
    case SQLITE_INTEGER: {
      i64 iVal = sqlite3_value_int64(argv[0]);
      if( iVal<0 ){
        if( iVal==SMALLEST_INT64 ){
          /* IMP: R-31676-45509 If X is the integer -9223372036854775808
          ** then abs(X) throws an integer overflow error since there is no
          ** equivalent positive 64-bit two complement value. */
          sqlite3_result_error(context, "integer overflow", -1);
          return;
        }
        iVal = -iVal;
      } 
      sqlite3_result_int64(context, iVal);
      break;
    }
    case SQLITE_NULL: {
      /* IMP: R-37434-19929 Abs(X) returns NULL if X is NULL. */
      sqlite3_result_null(context);
      break;
    }
    default: {
      /* Because sqlite3_value_double() returns 0.0 if the argument is not
      ** something that can be converted into a number, we have:
      ** IMP: R-01992-00519 Abs(X) returns 0.0 if X is a string or blob
      ** that cannot be converted to a numeric value.
      */
      double rVal = sqlite3_value_double(argv[0]);
      if( rVal<0 ) rVal = -rVal;
      sqlite3_result_double(context, rVal);
      break;
    }
  }
}

/*
** Implementation of the instr() function.
**
** instr(haystack,needle) finds the first occurrence of needle
** in haystack and returns the number of previous characters plus 1,
** or 0 if needle does not occur within haystack.
**
** If both haystack and needle are BLOBs, then the result is one more than
** the number of bytes in haystack prior to the first occurrence of needle,
** or 0 if needle never occurs in haystack.
*/
static void instrFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *zHaystack;
  const unsigned char *zNeedle;
  int nHaystack;
  int nNeedle;
  int typeHaystack, typeNeedle;
  int N = 1;
  int isText;

  UNUSED_PARAMETER(argc);
  typeHaystack = sqlite3_value_type(argv[0]);
  typeNeedle = sqlite3_value_type(argv[1]);
  if( typeHaystack==SQLITE_NULL || typeNeedle==SQLITE_NULL ) return;
  nHaystack = sqlite3_value_bytes(argv[0]);
  nNeedle = sqlite3_value_bytes(argv[1]);
  if( typeHaystack==SQLITE_BLOB && typeNeedle==SQLITE_BLOB ){
    zHaystack = sqlite3_value_blob(argv[0]);
    zNeedle = sqlite3_value_blob(argv[1]);
    isText = 0;
  }else{
    zHaystack = sqlite3_value_text(argv[0]);
    zNeedle = sqlite3_value_text(argv[1]);
    isText = 1;
  }
  while( nNeedle<=nHaystack && memcmp(zHaystack, zNeedle, nNeedle)!=0 ){
    N++;
    do{
      nHaystack--;
      zHaystack++;
    }while( isText && (zHaystack[0]&0xc0)==0x80 );
  }
  if( nNeedle>nHaystack ) N = 0;
  sqlite3_result_int(context, N);
}

/*
** Implementation of the printf() function.
*/
static void printfFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  PrintfArguments x;
  StrAccum str;
  const char *zFormat;
  int n;
  sqlite3 *db = sqlite3_context_db_handle(context);

  if( argc>=1 && (zFormat = (const char*)sqlite3_value_text(argv[0]))!=0 ){
    x.nArg = argc-1;
    x.nUsed = 0;
    x.apArg = argv+1;
    sqlite3StrAccumInit(&str, db, 0, 0, db->aLimit[SQLITE_LIMIT_LENGTH]);
    sqlite3XPrintf(&str, SQLITE_PRINTF_SQLFUNC, zFormat, &x);
    n = str.nChar;
    sqlite3_result_text(context, sqlite3StrAccumFinish(&str), n,
                        SQLITE_DYNAMIC);
  }
}

/*
** Implementation of the substr() function.
**
** substr(x,p1,p2)  returns p2 characters of x[] beginning with p1.
** p1 is 1-indexed.  So substr(x,1,1) returns the first character
** of x.  If x is text, then we actually count UTF-8 characters.
** If x is a blob, then we count bytes.
**
** If p1 is negative, then we begin abs(p1) from the end of x[].
**
** If p2 is negative, return the p2 characters preceding p1.
*/
static void substrFunc(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  const unsigned char *z;
  const unsigned char *z2;
  int len;
  int p0type;
  i64 p1, p2;
  int negP2 = 0;

  assert( argc==3 || argc==2 );
  if( sqlite3_value_type(argv[1])==SQLITE_NULL
   || (argc==3 && sqlite3_value_type(argv[2])==SQLITE_NULL)
  ){
    return;
  }
  p0type = sqlite3_value_type(argv[0]);
  p1 = sqlite3_value_int(argv[1]);
  if( p0type==SQLITE_BLOB ){
    len = sqlite3_value_bytes(argv[0]);
    z = sqlite3_value_blob(argv[0]);
    if( z==0 ) return;
    assert( len==sqlite3_value_bytes(argv[0]) );
  }else{
    z = sqlite3_value_text(argv[0]);
    if( z==0 ) return;
    len = 0;
    if( p1<0 ){
      for(z2=z; *z2; len++){
        SQLITE_SKIP_UTF8(z2);
      }
    }
  }
#ifdef SQLITE_SUBSTR_COMPATIBILITY
  /* If SUBSTR_COMPATIBILITY is defined then substr(X,0,N) work the same as
  ** as substr(X,1,N) - it returns the first N characters of X.  This
  ** is essentially a back-out of the bug-fix in check-in [5fc125d362df4b8]
  ** from 2009-02-02 for compatibility of applications that exploited the
  ** old buggy behavior. */
  if( p1==0 ) p1 = 1; /* <rdar://problem/6778339> */
#endif
  if( argc==3 ){
    p2 = sqlite3_value_int(argv[2]);
    if( p2<0 ){
      p2 = -p2;
      negP2 = 1;
    }
  }else{
    p2 = sqlite3_context_db_handle(context)->aLimit[SQLITE_LIMIT_LENGTH];
  }
  if( p1<0 ){
    p1 += len;
    if( p1<0 ){
      p2 += p1;
      if( p2<0 ) p2 = 0;
      p1 = 0;
    }
  }else if( p1>0 ){
    p1--;
  }else if( p2>0 ){
    p2--;
  }
  if( negP2 ){
    p1 -= p2;
    if( p1<0 ){
      p2 += p1;
      p1 = 0;
    }
  }
  assert( p1>=0 && p2>=0 );
  if( p0type!=SQLITE_BLOB ){
    while( *z && p1 ){
      SQLITE_SKIP_UTF8(z);
      p1--;
    }
    for(z2=z; *z2 && p2; p2--){
      SQLITE_SKIP_UTF8(z2);
    }
    sqlite3_result_text64(context, (char*)z, z2-z, SQLITE_TRANSIENT,
                          SQLITE_UTF8);
  }else{
    if( p1+p2>len ){
      p2 = len-p1;
      if( p2<0 ) p2 = 0;
    }
    sqlite3_result_blob64(context, (char*)&z[p1], (u64)p2, SQLITE_TRANSIENT);
  }
}

/*
** Implementation of the round() function
*/
#ifndef SQLITE_OMIT_FLOATING_POINT
static void roundFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  int n = 0;
  double r;
  char *zBuf;
  assert( argc==1 || argc==2 );
  if( argc==2 ){
    if( SQLITE_NULL==sqlite3_value_type(argv[1]) ) return;
    n = sqlite3_value_int(argv[1]);
    if( n>30 ) n = 30;
    if( n<0 ) n = 0;
  }
  if( sqlite3_value_type(argv[0])==SQLITE_NULL ) return;
  r = sqlite3_value_double(argv[0]);
  /* If Y==0 and X will fit in a 64-bit int,
  ** handle the rounding directly,
  ** otherwise use printf.
  */
  if( n==0 && r>=0 && r<LARGEST_INT64-1 ){
    r = (double)((sqlite_int64)(r+0.5));
  }else if( n==0 && r<0 && (-r)<LARGEST_INT64-1 ){
    r = -(double)((sqlite_int64)((-r)+0.5));
  }else{
    zBuf = sqlite3_mprintf("%.*f",n,r);
    if( zBuf==0 ){
      sqlite3_result_error_nomem(context);
      return;
    }
    sqlite3AtoF(zBuf, &r, sqlite3Strlen30(zBuf), SQLITE_UTF8);
    sqlite3_free(zBuf);
  }
  sqlite3_result_double(context, r);
}
#endif

/*
** Allocate nByte bytes of space using sqlite3Malloc(). If the
** allocation fails, call sqlite3_result_error_nomem() to notify
** the database handle that malloc() has failed and return NULL.
** If nByte is larger than the maximum string or blob length, then
** raise an SQLITE_TOOBIG exception and return NULL.
*/
static void *contextMalloc(sqlite3_context *context, i64 nByte){
  char *z;
  sqlite3 *db = sqlite3_context_db_handle(context);
  assert( nByte>0 );
  testcase( nByte==db->aLimit[SQLITE_LIMIT_LENGTH] );
  testcase( nByte==db->aLimit[SQLITE_LIMIT_LENGTH]+1 );
  if( nByte>db->aLimit[SQLITE_LIMIT_LENGTH] ){
    sqlite3_result_error_toobig(context);
    z = 0;
  }else{
    z = sqlite3Malloc(nByte);
    if( !z ){
      sqlite3_result_error_nomem(context);
    }
  }
  return z;
}

/*
** Implementation of the upper() and lower() SQL functions.
*/
static void upperFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  char *z1;
  const char *z2;
  int i, n;
  UNUSED_PARAMETER(argc);
  z2 = (char*)sqlite3_value_text(argv[0]);
  n = sqlite3_value_bytes(argv[0]);
  /* Verify that the call to _bytes() does not invalidate the _text() pointer */
  assert( z2==(char*)sqlite3_value_text(argv[0]) );
  if( z2 ){
    z1 = contextMalloc(context, ((i64)n)+1);
    if( z1 ){
      for(i=0; i<n; i++){
        z1[i] = (char)sqlite3Toupper(z2[i]);
      }
      sqlite3_result_text(context, z1, n, sqlite3_free);
    }
  }
}
static void lowerFunc(sqlite3_context *context, int argc, sqlite3_value **argv){
  char *z1;
  const char *z2;
  int i, n;
  UNUSED_PARAMETER(argc);
  z2 = (char*)sqlite3_value_text(argv[0]);
  n = sqlite3_value_bytes(argv[0]);
  /* Verify that the call to _bytes() does not invalidate the _text() pointer */
  assert( z2==(char*)sqlite3_value_text(argv[0]) );
  if( z2 ){
    z1 = contextMalloc(context, ((i64)n)+1);
    if( z1 ){
      for(i=0; i<n; i++){
        z1[i] = sqlite3Tolower(z2[i]);
      }
      sqlite3_result_text(context, z1, n, sqlite3_free);
    }
  }
}

/*
** Some functions like COALESCE() and IFNULL() and UNLIKELY() are implemented
** as VDBE code so that unused argument values do not have to be computed.
** However, we still need some kind of function implementation for this
** routines in the function table.  The noopFunc macro provides this.
** noopFunc will never be called so it doesn't matter what the implementation
** is.  We might as well use the "version()" function as a substitute.
*/
#define noopFunc versionFunc   /* Substitute function - never called */

/*
** Implementation of random().  Return a random integer.  
*/
static void randomFunc(
  sqlite3_context *context,
  int NotUsed,
  sqlite3_value **NotUsed2
){
  sqlite_int64 r;
  UNUSED_PARAMETER2(NotUsed, NotUsed2);
  sqlite3_randomness(sizeof(r), &r);
  if( r<0 ){
    /* We need to prevent a random number of 0x8000000000000000 
    ** (or -9223372036854775808) since when you do abs() of that
    ** number of you get the same value back again.  To do this
    ** in a way that is testable, mask the sign bit off of negative
    ** values, resulting in a positive value.  Then take the 
    ** 2s complement of that positive value.  The end result can
    ** therefore be no less than -9223372036854775807.
    */
    r = -(r & LARGEST_INT64);
  }
  sqlite3_result_int64(context, r);
}

/*
** Implementation of randomblob(N).  Return a random blob
** that is N bytes long.
*/
static void randomBlob(
  sqlite3_context *context,
  int argc,
  sqlite3_value **argv
){
  int n;
  unsigned char *p;
  assert( argc==1 );
  UNUSED_PARAMETER(argc);
  n = sqlite3_value_int(argv[0]);
  if( n<1 ){
    n = 1;
  }
  p = contextMalloc(context, n);
  if( p ){
    sqlite3_randomness(n, p);
    sqlite3_result_blob(context, (char*)p, n, sqlite3_free);
  }
}

/*
** Implementation of the last_insert_rowid() SQL function.  The return
** value is the same as the sqlite3_last_insert_rowid() API function.
*/
static void last_insert_rowid(
  sqlite3_context *context, 
  int NotUsed, 
  sqlite3_value **NotUsed2
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  UNUSED_PARAMETER2(NotUsed, NotUsed2);
  /* IMP: R-51513-12026 The last_insert_rowid() SQL function is a
  ** wrapper around the sqlite3_last_insert_rowid() C/C++ interface
  ** function. */
  sqlite3_result_int64(context, sqlite3_last_insert_rowid(db));
}

/*
** Implementation of the changes() SQL function.
**
** IMP: R-62073-11209 The changes() SQL function is a wrapper
** around the sqlite3_changes() C/C++ function and hence follows the same
** rules for counting changes.
*/
static void changes(
  sqlite3_context *context,
  int NotUsed,
  sqlite3_value **NotUsed2
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  UNUSED_PARAMETER2(NotUsed, NotUsed2);
  sqlite3_result_int(context, sqlite3_changes(db));
}

/*
** Implementation of the total_changes() SQL function.  The return value is
** the same as the sqlite3_total_changes() API function.
*/
static void total_changes(
  sqlite3_context *context,
  int NotUsed,
  sqlite3_value **NotUsed2
){
  sqlite3 *db = sqlite3_context_db_handle(context);
  UNUSED_PARAMETER2(NotUsed, NotUsed2);
  /* IMP: R-52756-41993 This function is a wrapper around the
  ** sqlite3_total_changes() C/C++ interface. */
  sqlite3_result_int(context, sqlite3_total_changes(db));
}

连接池指导

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.

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

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

多线程读写

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);          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。

更多关于SQLite，参考YY哥博客

浅谈SQLite——浅析Lemon

1、概述
　　Lemon是一个LALR(1)文法分析器生成工具。虽然它是SQLite作者针对SQLite写的一个分析器生成工具，但是它与bison和yacc类似，是一个可以独立于SQLite使用的开源的分析器生成工具。而且它使用与yacc(bison)不同的语法规则，可以减少编程时出现错误的机会。Lemon比yacc和bison更精致、更快，而且是可重入的，也是线程安全的——这对于支持多线程的程序是非常重要的。
　　Lemon的主要功能就是根据上下文无关文法(CFG)，生成支持该文法的分析器。程序的输入文件有两个：
　　(1) 语法规则文件；
　　(2) 分析器模板文件。
　　一般来说，语法规则是由程序员定义的；Lemon有一个适用于大多数应用程序的默认分析器模板。根据命令行选项，Lemon会生成以下一些文件：
　　(1) 分析器的C代码；
　　(2) 一个为每个终结符定义一个整型ID的头文件；
　　(3) 一个描述分析器状态的文件。
　　语法规范文件以”.y”为后缀，如果语法规范文件为”gram.y”，则可以使用如下命令生成分析器：
　　lemon gram.y

1.1、分析器接口
　　Lemon不会生成一个完整的、可以运行的程序。它仅仅生成一些实现分析器的子例程，然后由用户程序在适当的地方调用这些子例程，从而生成一个完整的分析器。

其他

文本值(literal value)是一个整数或浮点数。可以使用科学计数法。"."符号总是被当作小数点即使本地设定中用","来表示小数点 ——用","表示小数点会造成歧义。字符串常量由字符串加单引号(')构成。字符串内部的单引号可像Pascal 中一样用两个单引号来表示。C风格的加反斜线的表示法由于不是标准SQL而不被支持。 BLOB文本是以"x"或"X"开头的含有十六进制文本信息的文本值 For example:

X'53514697465'

文本值同样可以为"NULL".

表达式中插入文本值占位符的参数可以使用 sqlite3_bind API函数在运行时插入。参数可以是如下几种形式：

?NNN 问号跟随数字NNN为第NNN个参数占位。NNN需介于1和999之间。

? 不加数字的问号为下一个参数占位。

:AAAA 冒号带一个标识符名称为一个名为AAAA的参数占位。命名的参数同样可以使用序号占位，被赋予的参数序号为下一个尚未被使用的序号。建议不要混合使用命名代表的参数和序号代表的参数以免引起混淆。

$AAAA $符号带一个标识符名称也可以为一个名为AAAA的参数占位。在这一情况下标识符名称可以包括一个或更多的"::" 以及包含任何文本的"(...)"后缀。该语法是Tcl编程语言中的一个可变形式。

不使用sqlite3_bind赋值的参数被视为NULL.

SQLite对null值的处理（NULL Handling in SQLite Versus Other Database Engines）

nulls are distinct in a UNIQUE column，对于UNIQUE约束的列，不同行null是不一样的，即UNIQUE约束的列允许多个null。

CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     UNIQUE
);


INSERT INTO COMPANY VALUES(1, "fang1", null);
INSERT INTO COMPANY VALUES(2, "fang2", null);
INSERT INTO COMPANY VALUES(3, "fang3", 22);
INSERT INTO COMPANY VALUES(4, "fang4", 23);
INSERT INTO COMPANY VALUES(5, "fang5", 24);
INSERT INTO COMPANY VALUES(6, "fang6", 25);
INSERT INTO COMPANY VALUES(7, "fang7", null);
INSERT INTO COMPANY VALUES(8, "fang8", null);

这里一篇stackexchange上的文章《Why does a UNIQUE constraint allow only one NULL?》是针对sql-server

stackoverflow上 Sqlite NULL and unique? 2014-03-27 21:18:08Z

部分粗略译文

21 记录格式

一个表视图定义叶页的内容和任何的键索引视图定义页的输出条件作为任意序列先前提到的讨论一个小于另一个基元类型, 但没有定义什么"less 比"快速将地址这些只是在当前分区

有效负荷, 或者是表格内容或索引键, 不总是在"record 格式"对应的记录格式定义值序列的一个表或索引中的列记录格式指定数目的列, 则每个数据类型的列, 每个列的内容

记录格式提供的使用长度可变的整数或varint 表示的64 位有符号定义上面的整数.

记录包含一个标题和正文, 顺序这是要被用单个varint 头, 它确定头中的字节的总数varint值被头的大小以字节为单位包括的大小varint 本身如下varint 大小的一个或多个附加varints , 一个列这些附加"serial 被调用的varints 类型"数字和确定该数据类型的表中的每一列, 遵循下面的图表:

串行类型代码的记录格式

串行类型	目录大小	含义
0	0	NULL
1	1	8位twos-complement 整数
2	2	大端字节16 位twos-complement 整数
3	3	big-endian 24 位twos-complement 整数
4	4	big-endian 32 位twos-complement 整数
5	6	big-endian 正向twos-complement 整数
6	8	big-endian 64 位twos-complement 整数
7	8	big-endian IEEE 数字754-2008 64 位浮点数
8	0	整数常数0只适用于schema 格式4和更高版本
9	0	整数常量1只适用于schema 格式4和更高版本
10, 11		未使用保留的扩展
n≥12和偶数页	(n-12)/2	一个长度为(n-12)/2 字节在BLOB
n≥13和奇数	(n-13)/2	在数据库中一个字符串编码和长度(n-13)/2 个字节如果省略nul 的终止符

注意, 因为的varints 定义, 页眉的大小varint 和串行类型varints 通常包括一个字节串行类型对于大型字符串和BLOBs varints , 可能会扩展两个或三个字节varints , 但也可以是异常而不是规则varint格式是在编码是非常高效的记录头

每一列的值在记录紧跟在页眉请注意, 对于串行类型0, 8、9、12 、13 .则该值为零个字节如果对这种类型的所有列都记录的正文部分为空

22 记录的排序次序

键在索引视图定义的顺序是由排序顺序记录的键表示记录按列比较两个列从左至右列的记录都被检查对列都将次序确定的相对顺序两个记录排序顺序是单独的列如下所示:

NULL 值(串口)排序类型"0"
数值(串口类型1至9)next 并按照数字顺序进行排序
文本值(甚至串行12 和更大的)排序的顺序由列比较函数
BLOB 值(奇数串行类型13 和更大的)确定memcmp() 顺序最后进行排序.

一个比较函数对于每一个列是必要的计算中的文本.SQLite 定义三种内置的比较功能:

BINARY 使用 memcmp()比较字符串数据, 不考虑文本编码.

NOCASE 和二进制一样,但在比较之前,26位的大写字母要被折合成相应的小写字母.

REVERSE 用倒序比较二进制文本.

RTRIM 像BINARY 除了空格的字符串是要比较之前, 请先elided

其他特定于应用程序的比较函数可以添加到SQLite 使用sqlite3_create_collation( )接口.

默认的比较函数的所有字符串是BINARY可选中指定的比较顺序的函数表的列可以是CREATE TABLE 使用COLLATE 子句的语句列定义仅当使用了列的索引是, 在执行比较函数中指定的CREATE TABLE 语句用于该列中的索引.默认情况下.虽然此可重写的使用COLLATE 子句的select 语句中CREATE INDEX 语句.

posted @ 2015-08-12 22:31 山岚的一缺阅读(2471) 评论(0) 编辑收藏举报

刷新页面返回顶部

java sqlite配置和自定义函数

资源

jetty配置sqlite

sqlite自定义函数

运行时可加载扩展 Run-Time Loadable Extensions

编写可加载扩展 Programming Loadable Extensions

静态地链接一个运行时扩展 Statically Linking A Run-Time Loadable Extension

连接池指导

Add support for ConnectionPool

MiniConnectionPoolManager - A lightweight standalone Java JDBC connection pool manager

Examples of how to use the MiniConnectionPoolManager class

For H2 (embedded mode):

For Apache Derby (embedded mode):

For PostgreSQL:

For JTDS:

For Microsoft SQL Server:

For Oracle (example for Thin driver):

For MaxDB:

Design pattern for working with JDBC connections

Single SQLite connection

Android上使用sqlite

What are the best practices for SQLite on Android?

SQLite多线程读写实践及常见问题总结

代码:

代码:

代码:

代码:

浅谈SQLite——浅析Lemon

其他

SQLite对null值的处理（NULL Handling in SQLite Versus Other Database Engines）

21 记录格式

22 记录的排序次序

公告

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.

?NNN		问号跟随数字NNN为第NNN个参数占位。NNN需介于1和999之间。
?		不加数字的问号为下一个参数占位。
:AAAA		冒号带一个标识符名称为一个名为AAAA的参数占位。命名的参数同样可以使用序号占位，被赋予的参数序号为下一个尚未被使用的序号。建议不要混合使用命名代表的参数和序号代表的参数以免引起混淆。
$AAAA		$符号带一个标识符名称也可以为一个名为AAAA的参数占位。在这一情况下标识符名称可以包括一个或更多的"::" 以及包含任何文本的"(...)"后缀。该语法是Tcl编程语言中的一个可变形式。

BINARY	使用 memcmp()比较字符串数据, 不考虑文本编码.
NOCASE	和二进制一样,但在比较之前,26位的大写字母要被折合成相应的小写字母.
REVERSE	用倒序比较二进制文本.
RTRIM	像BINARY 除了空格的字符串是要比较之前, 请先elided