Android SQLite数据库(事务)的使用，多线程CRUD并发操作（可用于实际开发）

1、概述

很多项目中都会用到android自带的SQLite数据库，这里结合我在项目中遇到的问题,将详细地介绍数据库的用法，这里就不介绍

SQLite优点、缺点等等一些乱七八糟的废话了。

2、SQLite的使用

1、写一个类继承SQLiteOpenHelper.class这个类，重写onCreate和onUpgrade方法，如下：

/**   * student数据库 * @author     hf  2016-6-6上午9:39:45    */public class StudentSQLiteOpenHelper extends SQLiteOpenHelper {private String createTable="create table STUDENT(_id integer primary key autoincrement," +"字段名1 varchar(8) unique," +                   "字段名2 integer not null default 0," + "字段名3 varchar(256))";private String sql = "DROP TABLE IF EXISTS STUDENT";public StudentSQLiteOpenHelper (Context context) {super(context, "stu.db", null, 1);}//创建数据库@Overridepublic void onCreate(SQLiteDatabase db) {        db.execSQL(createTable);}//数据库升级@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            db.execSQL(sql);        this.onCreate(db);}}

2、如果你想自定义数据库的存储位置，如sdcard,可以自定义一个类SDSQLiteOpenHelper.class,继承即可：

public abstract class SDSQLiteOpenHelper {private static final StringTAG= SDSQLiteOpenHelper.class.getSimpleName();private final ContextmContext;private final StringmName;private final CursorFactorymFactory;private final intmNewVersion;private SQLiteDatabasemDatabase= null;private booleanmIsInitializing= false;/** * Create a helper object to create, open, and/or manage a database. The database is not actually created or opened * until one of {@link #getWritableDatabase} or {@link #getReadableDatabase} is called. *  * @param context *            to use to open or create the database * @param name *            of the database file, or null for an in-memory database * @param factory *            to use for creating cursor objects, or null for the default * @param version *            number of the database (starting at 1); if the database is older, {@link #onUpgrade} will be used to *            upgrade the database */public SDSQLiteOpenHelper(Context context, String name, CursorFactory factory, int version) {if (version < 1)throw new IllegalArgumentException("Version must be >= 1, was " + version);mContext = context;mName = name;mFactory = factory;mNewVersion = version;}/** * Create and/or open a database that will be used for reading and writing. Once opened successfully, the database * is cached, so you can call this method every time you need to write to the database. Make sure to call * {@link #close} when you no longer need it. *  * <p> * Errors such as bad permissions or a full disk may cause this operation to fail, but future attempts may succeed * if the problem is fixed. * </p> *  * @throws SQLiteException *             if the database cannot be opened for writing * @return a read/write database object valid until {@link #close} is called */public synchronized SQLiteDatabase getWritableDatabase() {if (mDatabase != null && mDatabase.isOpen() && !mDatabase.isReadOnly()) {return mDatabase; // The database is already open for business}if (mIsInitializing) {throw new IllegalStateException("getWritableDatabase called recursively");}// If we have a read-only database open, someone could be using it// (though they shouldn't), which would cause a lock to be held on// the file, and our attempts to open the database read-write would// fail waiting for the file lock. To prevent that, we acquire the// lock on the read-only database, which shuts out other users.boolean success = false;SQLiteDatabase db = null;try {mIsInitializing = true;if (mName == null) {db = SQLiteDatabase.create(null);} else {String path = getDatabasePath(mName).getPath();db = SQLiteDatabase.openOrCreateDatabase(path, mFactory);}int version = db.getVersion();if (version != mNewVersion) {db.beginTransaction();try {if (version == 0) {onCreate(db);} else {onUpgrade(db, version, mNewVersion);}db.setVersion(mNewVersion);db.setTransactionSuccessful();} finally {db.endTransaction();}}onOpen(db);success = true;return db;} finally {mIsInitializing = false;if (success) {if (mDatabase != null) {try {mDatabase.close();} catch (Exception e) {}}mDatabase = db;} else {if (db != null)db.close();}}}/** * Create and/or open a database. This will be the same object returned by {@link #getWritableDatabase} unless some * problem, such as a full disk, requires the database to be opened read-only. In that case, a read-only database * object will be returned. If the problem is fixed, a future call to {@link #getWritableDatabase} may succeed, in * which case the read-only database object will be closed and the read/write object will be returned in the future. *  * @throws SQLiteException *             if the database cannot be opened * @return a database object valid until {@link #getWritableDatabase} or {@link #close} is called. */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 = getDatabasePath(mName).getPath();db = SQLiteDatabase.openDatabase(path, mFactory, SQLiteDatabase.OPEN_READWRITE);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();}}/** * Close any open database object. */public synchronized void close() {if (mIsInitializing)throw new IllegalStateException("Closed during initialization");if (mDatabase != null && mDatabase.isOpen()) {mDatabase.close();mDatabase = null;}}//这里的Constants.DATABASEPATH是自己自定义的路径public File getDatabasePath(String name) {File file = new File(Constants.DATABASEPATH);if (!file.exists()) {file.mkdirs();}return new File(Constants.DATABASEPATH + name);}/** * Called when the database is created for the first time. This is where the creation of tables and the initial * population of the tables should happen. *  * @param db *            The database. */public abstract void onCreate(SQLiteDatabase db);/** * Called when the database needs to be upgraded. The implementation should use this method to drop tables, add * tables, or do anything else it needs to upgrade to the new schema version. *  * <p> * The SQLite ALTER TABLE documentation can be found <a href="http://sqlite.org/lang_altertable.html">here</a>. If * you add new columns you can use ALTER TABLE to insert them into a live table. If you rename or remove columns you * can use ALTER TABLE to rename the old table, then create the new table and then populate the new table with the * contents of the old table. *  * @param db *            The database. * @param oldVersion *            The old database version. * @param newVersion *            The new database version. */public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);/** * Called when the database has been opened. Override method should check {@link SQLiteDatabase#isReadOnly} before * updating the database. *  * @param db *            The database. */public void onOpen(SQLiteDatabase db) {}}

3、写一个StudentSQLiteDao.class类用来执行CRUD操作,一般来说，为了提高用户的体验，一些数据库操作需要放在子线程里去执

行，一些数据库操作也可能会放在主线程执行，所以在同时进行读写等操作的时候可能会出现并发操作异常的问题，所以采用单例

模式的形式，并重新写了打开和关闭数据库的方法，必须严格地调用，不可以直接调用db.close()。

private static final StringTAG= null;private static StudentSQLiteDao instance;private SQLiteDatabasemDataBase;private StudentSQLiteOpenHelper mHelper;private static ContextmContext;private AtomicInteger mOpenCounter = new AtomicInteger();//自增长类//构造方法私有化private StudentSQLiteDao(Context context) {mHelper = new StudentSQLiteDao(context);mContext = context;}//供外界调用的获取实例的方法public static  StudentSQLiteDao getInstance(Context context) {if (instance == null) {synchronized (StudentSQLiteDao .class) {if (instance == null) {mHelper = new StudentSQLiteOpenHelper(context);instance = new StudentSQLiteDao(context);}}}return instance;}//打开数据库方法public synchronized SQLiteDatabase openDatabase() {    if (mOpenCounter.incrementAndGet() == 1) {//incrementAndGet会让mOpenCounter自动增长1        // Opening new database        try {        mDataBase = mHelper.getWritableDatabase();        } catch (Exception e) {            mDataBase = mHelper.getReadableDatabase();        }    }    return mDataBase;}//关闭数据库方法public synchronized void closeDatabase() {    if (mOpenCounter.decrementAndGet() == 0) {//decrementAndGet会让mOpenCounter自动减1        // Closing database    mDataBase.close();    }}

打开数据库的格式必须是这样的：mDataBase=getInstance(mContext).openDatabase();

关闭数据库的格式必须是这样的：getInstance(mContext).closeDatabase();

否则可能会报错。

三、增删改查--为了提高查询效率以及发生错误时能够回滚，使用事务的概念，一定要严格执行这四个方法，不能

漏掉，，当然不需要提高效率的也可以不用事务，但是一定要记得关闭数据库，格式是：

mDataBase.beginTransaction();try{//业务代码mDataBase.setTransactionSuccessful();}catch{}finally{mDataBase.endTransaction();//注意，一定要写成这种形式getInstance(mContext).closeDatabase();}

1、增加数据

insert(String table, String nullColumnHack, ContentValues values);

table :表名

nullColumnHack：不用管，一般为null

values：插入的数据，键值对形式

例如：批量插入数据

public boolean insert(List<Bean> list) {mDataBase=getInstance(mContext).openDatabase();boolean result = false;mDataBase.beginTransaction();try {for (Bean bean : list) {ContentValues value = new ContentValues();value.put("字段1", 值);value.put("字段2", 值);value.put("字段3", 值);value.put("字段4",值);value.put("字段5",值);value.put("字段6", 值);mDataBase.insert("STUDENT", null, value);}mDataBase.setTransactionSuccessful();result = true;} catch (Exception e) {e.printStackTrace();} finally {mDataBase.endTransaction();getInstance(mContext).closeDatabase();}return result;}

2、删除数据

delete(String table,String whereClause, String[] whereArgs)

table 表名

whereClause 哪一行的字段

whereArgs 该行字段所对应的值

当后面两个参数都为null的时候表示没有筛选条件，会直接删除所有

例如：

public void delete(MyChoiceBean bean) {mDataBase = getInstance(mContext).openDatabase();mDataBase.beginTransaction();try {mDataBase.delete("STUDENT", "字段=?", new String[] { 字符串 });mDataBase.setTransactionSuccessful();} catch (Exception e) {e.printStackTrace();} finally {mDataBase.endTransaction();getInstance(mContext).closeDatabase();}}

3、修改数据

update(String table,ContentValues values, String whereClause,String[] whereArgs)

table表名

values 字段集 ，以键值对形式保存

whereClause 条件的键 ，形式是：字段=?，多个字段可以用and连接

whereArgs 条件的值，形式是 new String[]{ 值}，多个子弹以逗号隔开

例如：

public int update(Bean bean) {mDataBase = getInstance(mContext).openDatabase();mDataBase.beginTransaction();int result = -1;try {ContentValues value = new ContentValues();value.put("字段1", 值);value.put("字段2", 值);value.put("字段3", 值);...result = mDataBase.update("STUDENT", value, "字段=?", new String[] {值});mDataBase.setTransactionSuccessful();} catch (Exception e) {e.printStackTrace();return result;} finally {mDataBase.endTransaction();getInstance(mContext).closeDatabase();}return result;}

4、查询数据

 query(String table,String[] columns, String selection,String[] selectionArgs,String groupBy,String having,String orderBy)

table表名

columns 查询哪一列，一般为null 

selection 筛选条件 形式是  字段=?

selectionArgs 对应筛选字段的值 形式是 new String[]{值}

后面就是附加条件，一般为null，如果想按时间排序，最后一个参数orderBy，“字段 DESC“” 这是降序，升序就改为ASC

例如：查询所有,注意，表的第一个字段是 _id，我并不需要id,所以我获取是从1开始的

public List<Bean> queryAll() {mDataBase = getInstance(mContext).openDatabase();Cursor cursor = mDataBase.query("STUDENT", null, null, null, null, null, null);if (cursor == null) {getInstance(mContext).closeDatabase();return null;}List<Bean> list = new ArrayList<Bean>();mDataBase.beginTransaction();try {while (cursor.moveToNext()) {String 字段1= cursor.getString(1);String 字段2= cursor.getString(2);String 字段3= cursor.getString(3);String 字段4= cursor.getString(4);String 字段5= cursor.getString(5);...Bean  newBean= new Bean(字段...);list.add(newBean);}mDataBase.setTransactionSuccessful();} catch (Exception e) {e.printStackTrace();} finally {mDataBase.endTransaction();cursor.close();getInstance(mContext).closeDatabase();}if (list.size() == 0) {return null;}return list;}