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

	//创建数据库
	@Override
	public void onCreate(SQLiteDatabase db) {
        db.execSQL(createTable);
	}


	//数据库升级
	@Override
	public 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 String	TAG				= SDSQLiteOpenHelper.class.getSimpleName();
	private final Context		mContext;
	private final String		mName;
	private final CursorFactory	mFactory;
	private final int			mNewVersion;
	private SQLiteDatabase		mDatabase		= null;
	private boolean				mIsInitializing	= 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 String		TAG	= null;
	private static StudentSQLiteDao 	instance;
	private SQLiteDatabase			mDataBase;
	private StudentSQLiteOpenHelper 	mHelper;
	private static Context			mContext;
	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;
}
发布了4 篇原创文章 · 获赞 7 · 访问量 1万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览