问题场景描述:
在页面中用到了ViewPager控件,ViewPager中的内容分别是两个ListView,两个ListView的数据都来自本地数据库(先从网络下载数据,然后更新本地数据库),在实际的使用过程中发现会出现SQLiteDatabaseLockedException: database is locked的问题。
经网上搜索资料,发现是读写数据库时存在的同步问题,所以采用单例+同步锁的方法,并且在每次数据库操作后都关闭数据库,经测试后发现没有在出现上述问题。
以下是两个主类
DBHelper.java(这个类用来管理数据库)
public class DBHelper extends SQLiteOpenHelper { private final String TAG = this.getClass().getSimpleName(); public final static String DATABASE_NAME = "test.db"; public final static String TABLE = "table"; public final static int DATABASE_VERSION = 2; public DBHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } private static DBHelper mInstance; public synchronized static DBHelper getInstance(Context context) { if (mInstance == null) { mInstance = new DBHelper(context); } return mInstance; }; @Override public void onCreate(SQLiteDatabase db) { try { db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE + "(id INTEGER PRIMARY KEY ,data BLOB)"); } catch (SQLiteException e) { e.printStackTrace(); } } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // 删除原来的数据表 db.execSQL("DROP TABLE IF EXISTS " + TABLE); // 重新创建 onCreate(db); } public static byte[] objectToBytes(Object obj) throws Exception { ByteArrayOutputStream out = new ByteArrayOutputStream(); ObjectOutputStream sOut = new ObjectOutputStream(out); sOut.writeObject(obj); sOut.flush(); byte[] bytes = out.toByteArray(); return bytes; } public static Object bytesToObject(byte[] bytes) throws Exception { ByteArrayInputStream in = new ByteArrayInputStream(bytes); ObjectInputStream sIn = new ObjectInputStream(in); return sIn.readObject(); } }
DBStudentManager类(这里可以定义自己的管理类)
public class DBStudentManager { private DBHelper helper; private SQLiteDatabase db; public DBStudentManager(Context context) { helper = DBHelper.getInstance(context); db = helper.getWritableDatabase(); } // 插入 private void insert(Student student) { synchronized (helper) { // 看数据库是否关闭 if (!db.isOpen()) { db = helper.getWritableDatabase(); } // 开始事务 db.beginTransaction(); try { db.execSQL( "INSERT INTO " + DBHelper.TABLE + " VALUES(?,?)", new Object[] { student.mID, DBHelper.objectToBytes(student) }); db.setTransactionSuccessful(); // 设置事务成功完成 } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { db.endTransaction(); db.close(); } } } // 更新 private void update(Student student) { synchronized (helper) { if (!db.isOpen()) { db = helper.getWritableDatabase(); } db.beginTransaction(); try { db.execSQL("UPDATE " + DBHelper.TABLE + "SET data = ? WHERE id = ?", new Object[] { DBHelper.objectToBytes(student), student.mID }); db.setTransactionSuccessful(); // 设置事务成功完成 } catch (SQLException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } finally { db.endTransaction(); db.close(); } } } // 同步 public void synchronous(List<Student> students) { if (students == null) { return; } for (Student student : students) { if (query(student.mID) == null) { insert(student); } else { update(student); } } } // 删除指定数据 public void delete(String id) { synchronized (helper) { if (!db.isOpen()) { db = helper.getWritableDatabase(); } db.beginTransaction(); try { db.execSQL("DELETE FROM " + DBHelper.TABLE + " WHERE id = ? ", new String[] { id }); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { db.endTransaction(); db.close(); } } } // 删除所有数据 public void delete() { synchronized (helper) { if (!db.isOpen()) { db = helper.getWritableDatabase(); } db.beginTransaction(); try { db.execSQL("DELETE * FROM " + DBHelper.TABLE); db.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { db.endTransaction(); db.close(); } } } // 查找所有的Students public List<Student> query() { List<Student> students = new ArrayList<Student>(); synchronized (helper) { if (!db.isOpen()) { db = helper.getWritableDatabase(); } Cursor c = queryTheCursor(); Student student = null; try { while (c.moveToNext()) { byte[] bytes = c.getBlob((c.getColumnIndex("data"))); student = (Student) DBHelper.bytesToObject(bytes); students.add(student); } } catch (Exception e) { e.printStackTrace(); } finally { c.close(); } } return students; } // 查找指定ID的Student public Student query(String id) { Student student = null; synchronized (helper) { if (!db.isOpen()) { helper.getWritableDatabase(); } Cursor c = queryTheCursor(id); try { while (c.moveToNext()) { byte[] bytes = c.getBlob((c.getColumnIndex("data"))); student = (Student) DBHelper.bytesToObject(bytes); break; } } catch (Exception e) { e.printStackTrace(); } finally { c.close(); } } return student; } // 获取游标 public Cursor queryTheCursor(String id) { Cursor c = db.rawQuery("SELECT FROM " + DBHelper.TABLE + " WHERE id = ?", new String[] { id }); return c; } // 获取游标 public Cursor queryTheCursor() { Cursor c = db.rawQuery("SELECT * FROM " + DBHelper.TABLE); return c; } class Student { String mID; String mName; int mAge; } }