SQLite多线程读写-sqlite3中的数据类型

http://bbs.51cto.com/thread-990260-1.html

http://www.cnblogs.com/kfqcome/archive/2011/06/27/2137000.html

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

多线程读写

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的每个sql数据库引擎）都使用静态的、刚性的类型，使用静态类型，数据的类型就由它的容器决定，这个容器是这个指被存放的特定列。

Sqlite使用一个更一般的动态类型系统，sqlite中，值的数据类型跟值本身相关，而不是与它的容器相关。Sqlite的动态类型系统和其他数据库的更为一般的静态类型系统相兼容，但同时，sqlite中的动态类型允许它能做到一些传统刚性类型数据库所不可能做到的事。

1．存储类和数据类型

每个存放在sqlite数据库中（或者由这个数据库引擎操作）的值都有下面中的一个存储类：

l NULL，值是NULL

l INTEGER，值是有符号整形，根据值的大小以1,2,3,4,6或8字节存放

l REAL，值是浮点型值，以8字节IEEE浮点数存放

l TEXT，值是文本字符串，使用数据库编码（UTF-8，UTF-16BE或者UTF-16LE）存放

l BLOB，只是一个数据块，完全按照输入存放（即没有准换）

从上可以看出存储类比数据类型更一般化。比如INTEGER存储类，包括6中不同长度的不同整形数据类型，这在磁盘上造成了差异。但是只要INTEGER值被从磁盘读出进入到内存进行处理，它们被转换成最一般的数据类型（8-字节有符号整形）。

Sqlite v3数据库中的任何列，除了整形主键列，可以用于存储任何一个存储列的值。sql语句中的中所有值，不管它们是嵌入在sql文本中或者是作为参数绑定到一个预编译的sql语句，它们的存储类型都是未定的。在下面描述的情况中，数据库引擎会在查询执行过程中在数值（numeric）存储类型（INTEGER和REAL）和TEXT之间转换值。

1.1布尔类型

Sqlite没有单独的布尔存储类型，它使用INTEGER作为存储类型，0为false，1为true

1.2 Date和Time Datatype

Sqlite没有另外为存储日期和时间设定一个存储类集，内置的sqlite日期和时间函数能够将日期和时间以TEXT，REAL或INTEGER形式存放

l TEXT 作为IS08601字符串（”YYYY-MM-DD HH:MM:SS.SSS”）

l REAL 从格林威治时间11月24日，4174 B.C中午以来的天数

l INTEGER 从 1970-01-01 00:00:00 UTC以来的秒数

程序可以任意选择这几个存储类型去存储日期和时间，并且能够使用内置的日期和时间函数在这些格式间自由转换

2.0 类型近似

为了使sqlite和其他数据库间的兼容性最大化，sqlite支持列上“类型近似”的观点，列的类型近似指的是存储在列上数据的推荐类型。这里必须记住一点，这个类型是被推荐，而不是必须的。任何列仍然能存储任意类型的数据。只是一些列，给予选择的话，将会相比于其他的一些类型优选选择一些存储类型，这个列优先选择的存储类型被称为它的“近似”。

每个sqlite3数据库中的列都被赋予下面类型近似中的一种：

l TEXT

l NUMERIC

l INTEGER

l REAL

l NONE

具有TEXT近似的列可以用NULL，TEXT或者BLOB类型存储数据。如果数值数据被插入到具有TEXT近似的列，在被存储前被转换为文本形式

一个有NUMERIC近似的列可以使用1中的所有5中存储类来存储数据。当文本数据被存放到NUMERIC近似的列中，这个文本的存储类被转换到INTEGER或REAL（根据优先级顺序），如果这个转换是无损的话。对于TEXT和REAL存储类间的转换，如果数据的前15位的被保留的话sqlite就认为这个转换是无损的、可反转的。如果TEXT到INTEGER或REAL的转换不可避免的会造成损失，那么数据将使用TEXT存储类存储。不会企图去转换NULL或BLOB值。

一个字符串可能看起来像浮点数据，有小数点或指数符号，但是只要这个数据可以使用整形存放，NUMERIC近似就会将它转换到整形。比如，字符串 ‘3.0e+5’存放到一个具有NUMERIC近似的列中，被存为300000，而不是浮点型值300000.0。

具有INTEGER近似的列和具有NUMERIC近似的列表现相同。它们之间的差别仅处于转换描述上。

具有REAL近似的列和具有NUMERIC近似的列一样，除了它将整形数据转换成浮点型形式。

具有NONE近似的列不会优先选择一个存储列，也不会强制将数据从一个存储类转换到另外一个类。

2.1 列近似的决定因素

列的近似由这个列的声明类型所决定，根据下面的顺序的规则：

<1> 如果声明类型包含”INT”字符串，那么这个列被赋予INTEGER近似

<2> 如果这个列的声明类型包含”CHAR”，”CLOB”，或者”TEXT”中的任意一个，那么这个列就有了TEXT近似。注意类型VARCHAR包含了”CHAR”字符串，那么也就被赋予了TEXT近似

<3> 如果列的声明类型中包含了字符串”BLOB”或者没有为其声明类型，这个列被赋予NONE近似

<4> 其他的情况，列被赋予NUMERIC近似

上面规则额顺序对于决定列的近似很重要。一个列的声明类型为”CHARINT”的话同时会匹配规则<1>和<2>，但是第一个规则占有优先级所以这个列的近似将是INTEGER。

2.2 近似名称例子

下面这个表显示了多少来自更传统的SQL操作的普通数据类型名称，使用上一节中的5个规则，被转换到近似类型。这个表只显示了sqlite能够接受的数据类名称的一个子集。注意到跟随类型名的圆括号内的数值参数（如：”VARCHAR(255)”）被sqlite忽略—sqlite不在字符串、BLOBS或者数值的长度上强加任何长度限制（除了一个全局的SQLITE_MAX_LENGTH限制）。

来自create table语句或者强转语句的范例类型名	产生的近似	用于决定近似的规则
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER	1
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT	2
BLOB no datatype specified	NONE	3
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL	4
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC	5

注意到声明类型为”FLOATING POINT”将被赋予INTEGER近似，而不是REAL近似，因为在”POINT”中的”INT”。声明类型为”STRING”的将被赋予NUMERIC，而不是TEXT（因为上述表中定义的类型中不存在STRING这一类型，它被归于到规则<4>中，属于其他情况）。

（从上面可以看出，sqlite3只是从声明类型字符串中去查找它知道的声明类型，比如”XINT”将被赋予INTEGER近似因为这个字符串里面有”INT”，所以这里并不需要一个单独的正确的声明类型，而是只要声明类型字符串里面包含了sqlite所知道的声明类型即可）

2.3 列近似操作例子

CREATE TABLE t1(

t TEXT, — text affinity by rule 2

nu NUMERIC, — numeric affinity by rule 5

i INTEGER, — integer affinity by rule 1

r REAL, — real affinity by rule 4

no BLOB — no affinity by rule 3

); //这里根据声明类型确定了列的类型近似

INSERT INTO t1 VALUES(‘500.0’, ‘500.0’, ‘500.0’, ‘500.0’, ‘500.0’);