关闭

自己写一个可用并且好用的Sqlite工具

452人阅读 评论(0) 收藏 举报
分类:

相信很懂同学都接触过很多开源的数据库框架,具体的就不多说了,反正给我的感觉就是,用起来总是束手束脚的,哪怕说的再好也并没有自己做出来的那么趁手。另外,安卓本身对数据库的封装已经做得很不错了,我觉得自己封一个易用的并且好用的工具也并没有那么难,那么下面进入正题。

首先说说SQLiteOpenHelper这个工具类吧,其实这个工具类网上的教程资料很多,具体使用就不多说,主要说一下我是怎么写的吧。先自己写一个DatabaseHelper类继承SQLiteOpenHelper,这里为了更灵活我写了两个构造函数。代码如下:

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String name = "ZPDB"; //数据库名称

    private static final int version = 1;

    public DatabaseHelper(Context context) {
        this(context, name);
    }

    public DatabaseHelper(Context context, String dbname){
        super(context, dbname, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS person (personid integer primary key autoincrement," +
                " name varchar(20), password varchar(20), age INTEGER)");

        db.execSQL("create table if not exists story (storyid integer primary key autoincrement," +
                "title varchar(30), content varchar(1000), date varchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}
为什么要写这两个构造函数呢,第一个我用了一个默认的数据库名称,第二个可以自己传入数据库名称。这样做的目的是为了满足多数据库操作的需要,很多同学可能遇到过这个问题,比如一个聊天app,跟每个人的聊天记录保存在不同的文件下面,而且名字也不一样,这里就需要能够自己传入数据库名称了。说到不同文件下面的数据库,我们就得用到Context(上下文对象)这个玩意了。通常情况下,直接传入上下文对象,则数据库默认保存到系统目录下,而且是不可见的,那么如何定义数据库到指定位置呢?其实很简单,我们自定义一个类,继承ContextWrapper,代码如下:

public class DatabaseContext extends ContextWrapper {

    private String path;

    public DatabaseContext(Context base, String path){
        super(base);
        this.path = path;
    }

    @Override
    public File getDatabasePath(String name) {
        //判断是否存在sd卡
        boolean sdExist = android.os.Environment.MEDIA_MOUNTED.equals(android.os.Environment.getExternalStorageState());
        if(!sdExist){
            return null;
        }
        else{//如果存在
            //获取sd卡路径
            String dbDir=android.os.Environment.getExternalStorageDirectory().toString()+"/"+path;
            String dbPath = dbDir+"/"+name;//数据库路径
            //判断目录是否存在,不存在则创建该目录
            File dirFile = new File(dbDir);
            if(!dirFile.exists())
                dirFile.mkdirs();

            //数据库文件是否创建成功
            boolean isFileCreateSuccess = false;
            //判断文件是否存在,不存在则创建该文件
            File dbFile = new File(dbPath);
            if(!dbFile.exists())
            {
                try {
                    isFileCreateSuccess = dbFile.createNewFile();//创建文件
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            else {
                isFileCreateSuccess = true;
            }

            //返回数据库文件对象
            if(isFileCreateSuccess)
                return dbFile;
            else
                return null;
        }
    }

    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode,
                                               SQLiteDatabase.CursorFactory factory) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
        return result;
    }

    @Override
    public SQLiteDatabase openOrCreateDatabase(String name, int mode, SQLiteDatabase.CursorFactory factory,
                                               DatabaseErrorHandler errorHandler) {
        SQLiteDatabase result = SQLiteDatabase.openOrCreateDatabase(getDatabasePath(name), null);
        return result;
    }

}


构造函数中的path就是你想保存的数据库位置。那么具体怎么使用呢?这样就可以了
SQLiteOpenHelper sqlHelper = new DatabaseHelper(new DatabaseContext (context, path)); 或者
SQLiteOpenHelper sqlHelper = new DatabaseHelper(new DatabaseContext (context, path)); 或者
SQLiteOpenHelper sqlHelper = new DatabaseHelper(new DatabaseContext (context, path), dbname); 
这样你的数据库打开位置就是在path下面了,由于使用了SQLiteOpenHelper 这个帮助类,所以如果不存在的话就会自动帮你新建一个。

好了,数据库帮助类以及数据库打开位置搞定了,下面我们还要思考一个问题,那就是对数据的操作问题,数据库无外乎增删查改,如果每次都要重写依次,既浪费时间也不便于扩展和调整。我们应该把这些操作进行一下统一,这样如果有什么大的变动我们就只需要修改一个地方就可以了,而不用考虑其它的因素和可能影响到的地方。数据库操作工具类如下:

public class DatabaseManager2 {

    private SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase db;

    public DatabaseManager2(SQLiteOpenHelper sqLiteOpenHelper){
        this.mDatabaseHelper = sqLiteOpenHelper;
    }

    /**
     for example
     ContentValues values = new ContentValues();
     values1.put("name", "张龙");
     values1.put("address", "beijing");
     values1.put("sex", "male");
     insert("user", values);

     * @param table
     * @param values
     * @return
     */
    public boolean insert(String table, ContentValues values){
        db = mDatabaseHelper.getWritableDatabase();
        boolean flag = false;
        long id = -1;
        try{
            id = db.insert(table, null, values);
            flag = id != -1;
        }catch (Exception e){
            e.printStackTrace();
        }
        close();
        return flag;
    }

    /**
     for example
     delete("user", " id =? ", new String[]{"1"});

     * @param table
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean delete(String table, String whereClause, String[] whereArgs){
        db = mDatabaseHelper.getWritableDatabase();
        boolean flag = false;
        int count;
        try {
            count = db.delete(table, whereClause, whereArgs);
            flag = count > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        close();
        return flag;
    }

    /**
     for example
     ContentValues values = new ContentValues();
     values.put("name", "张三");
     values.put("address", "上海");
     values.put("sex", "男");
     update("user", values, " id=? ", new String[]{"2"});

     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean update(String table, ContentValues values, String whereClause,String[] whereArgs) {
        db = mDatabaseHelper.getWritableDatabase();
        boolean flag = false;
        int count;
        try {
            count = db.update(table, values, whereClause, whereArgs);
            flag = count > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        close();
        return flag;
    }

    /**
     for example
     view("user", " id=? ", new String[]{"2"});

     * @param table
     * @param selection
     * @param selectionArgs
     * @return
     */
    public Map<String, String> view(String table, String selection, String[] selectionArgs) {
        db = mDatabaseHelper.getReadableDatabase();
        Cursor cursor = null;
        Map<String, String> map = new HashMap<String, String>();
        try {
            cursor = db.query(true, table, null, selection,selectionArgs, null, null, null, null);
            int cols_len = cursor.getColumnCount();
            while (cursor.moveToNext()) {
                for (int i = 0; i < cols_len; i++) {
                    String cols_key = cursor.getColumnName(i);
                    String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_key, cols_value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(cursor != null){
            cursor.close();
        }
        close();
        return map;
    }

    /**
     for example
     listPersonMaps("user", null, null);

     * @param table
     * @param selection
     * @param selectionArgs
     * @return
     */
    public List<Map<String, String>> listPersonMaps(String table, String selection, String[] selectionArgs, String orderBy) {
        db = mDatabaseHelper.getReadableDatabase();
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        Cursor cursor = null;
        try {
            cursor = db.query(false, table, null, selection,selectionArgs, null, null, orderBy, null);
            int cols_len = cursor.getColumnCount();
            while (cursor.moveToNext()) {
                Map<String, String> map = new HashMap<String, String>();
                for (int i = 0; i < cols_len; i++) {
                    String cols_key = cursor.getColumnName(i);
                    String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_key, cols_value);
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(cursor != null){
            cursor.close();
        }
        close();
        return list;
    }


    /**
     * 批量插入
     * @param sTableName
     * @param ListOfMaps
     */
    public void InsertMaps(String sTableName, List<Map<String, Object>> ListOfMaps)
    {
        db = mDatabaseHelper.getWritableDatabase();
        try
        {
            boolean bFirst=true;
            if(ListOfMaps==null || ListOfMaps.size()<1)
            {
                return;
            }

            Log.e("insertbegin", "begin");
            db.beginTransaction();
            for(int i=0;i<ListOfMaps.size();i++)
            {
                bFirst= true;
                StringBuilder sb=new StringBuilder();
                StringBuilder columns=new StringBuilder();
                StringBuilder values=new StringBuilder();
                sb = sb.append("INSERT INTO [" + sTableName + "] (");

                ArrayList<Object>listOfValues = new ArrayList<Object>();
                Map<String, Object> map = ListOfMaps.get(i);

                Iterator<Map.Entry<String, Object>> iterator =map.entrySet().iterator();
                while(iterator.hasNext())
                {
                    Map.Entry<String, Object> next = iterator.next();
                    if(!bFirst)
                    {
                        columns = columns.append(", ");
                        values = values.append(", ");
                    }
                    columns.append("[").append(next.getKey()).append("]");
                    values.append("?");
                    listOfValues.add(next.getValue());
                    bFirst = false;
                }
                sb = sb.append(columns.toString()).append(") VALUES (").append(values.toString()).append(")");
                ExecNonQuery2(sb.toString(), listOfValues);
            }
            db.setTransactionSuccessful();
            db.endTransaction();
            Log.e("insertend", "end");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        close();
    }

    public void ExecNonQuery2(String Statement, List<Object> Args)
    {
        SQLiteStatement s = this.db.compileStatement(Statement);
        try
        {
            int numArgs = 0;
            if (Args != null)
            {
                numArgs = Args.size();
            }
            for (int i = 0; i < numArgs; i++)
            {
                DatabaseUtils.bindObjectToProgram(s, i + 1, Args.get(i));
            }
            s.execute();
        } finally
        {
            s.close();
        }
    }

    public void close(){
        if(isOpen()){
            db.close();
        }
    }

    public boolean isOpen(){
        if(db != null){
            return db.isOpen();
        }
        return false;
    }
}

具体就不多说,使用方法也是基于系统给的方法进行的简单封装,主要注意一下关于事务的操作就差不多了。

最后是关于如何使用的问题,在这里我的建议是针对每个表自己建一个类,对涉及到的增删查改进行具体操作,因为每次增删改查的具体字段都不相同,而且考虑数据库位置及名称等原因,这样更便于管理和修改,降低耦合度。示范代码如下:

public class TestDemo{

    public final String TABLE = "story";

    DatabaseManager2 databaseManager2;

    public TestDemo(Context context){
        this(context, null);
    }

    public TestDemo(Context context, String path){
        databaseManager2 = new DatabaseManager2(new DatabaseHelper(!TextUtils.isEmpty(path)?new DatabaseContext(context, path):context));
    }

    public void add(Story story) {
        ContentValues contentValues = new ContentValues();
        contentValues.put("title", story.getTitle());
        contentValues.put("content", story.getContent());
        contentValues.put("date", story.getDate());
        databaseManager2.insert(TABLE, contentValues);
    }
}
好了,以上是我使用数据库的一些心得体会,很多人可能还会考虑数据库的打开关闭问题,我目前的做法是每次操作完成都会关闭,当然,有的需求是比较特殊的,比如跟聊天相关的app可能涉及到数据库的持久操作,我的处理方式是,在activity的onresume中打开,在onstop中关闭,最后效果还不错,至于其它的地方基本保持随开随关的方式,对用户体验及性能上并没有太大影响,而且极大的减小了忘记关闭数据库带来的风险。最后补充一个关于数据库的线程操作问题,因为有的同学可能把比较耗时的数据库操作放到线程中去了,这里给出一个线程安全的数据库操作工具类:

public class DatabaseManager {

    private AtomicInteger mOpenCounter = new AtomicInteger();

    private static DatabaseManager instance;
    private static SQLiteOpenHelper mDatabaseHelper;
    private SQLiteDatabase db;

    public static synchronized void initializeInstance(SQLiteOpenHelper helper) {
        if (instance == null) {
            instance = new DatabaseManager();
            mDatabaseHelper = helper;
        }
    }

    public static synchronized DatabaseManager getInstance() {
        if (instance == null) {
            throw new IllegalStateException(DatabaseManager.class.getSimpleName() +
                    " is not initialized, call initializeInstance(..) method first.");
        }

        return instance;
    }

    public synchronized SQLiteDatabase openDatabase() {
        if(mOpenCounter.incrementAndGet() == 1) {
            // Opening new database
            db = mDatabaseHelper.getWritableDatabase();
        }
        return db;
    }

    public synchronized void closeDatabase() {
        if(mOpenCounter.decrementAndGet() == 0) {
            // Closing database
            db.close();
        }
    }

    /**
     for example
     ContentValues values = new ContentValues();
     values1.put("name", "张龙");
     values1.put("address", "beijing");
     values1.put("sex", "male");
     insert("user", values);

     * @param table
     * @param values
     * @return
     */
    public boolean insert(String table, ContentValues values){
        openDatabase();
        boolean flag = false;
        long id = -1;
        try{
            id = db.insert(table, null, values);
            flag = id != -1;
        }catch (Exception e){
            e.printStackTrace();
        }
        closeDatabase();
        return flag;
    }

    /**
     for example
     delete("user", " id =? ", new String[]{"1"});

     * @param table
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean delete(String table, String whereClause, String[] whereArgs){
        openDatabase();
        boolean flag = false;
        int count;
        try {
            count = db.delete(table, whereClause, whereArgs);
            flag = count > 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        closeDatabase();
         return flag;
    }

    /**
     for example
     ContentValues values = new ContentValues();
     values.put("name", "张三");
     values.put("address", "上海");
     values.put("sex", "男");
     update("user", values, " id=? ", new String[]{"2"});

     * @param table
     * @param values
     * @param whereClause
     * @param whereArgs
     * @return
     */
    public boolean update(String table, ContentValues values, String whereClause,String[] whereArgs) {
        openDatabase();
        boolean flag = false;
        int count;
        try {
            count = db.update(table, values, whereClause, whereArgs);
            flag = count > 0;
            } catch (Exception e) {
            e.printStackTrace();
            }
        closeDatabase();
        return flag;
    }

    /**
     for example
     view("user", " id=? ", new String[]{"2"});

     * @param table
     * @param selection
     * @param selectionArgs
     * @return
     */
    public Map<String, String> view(String table, String selection, String[] selectionArgs) {
        openDatabase();
        Cursor cursor = null;
        Map<String, String> map = new HashMap<String, String>();
        try {
            cursor = db.query(true, table, null, selection,selectionArgs, null, null, null, null);
            int cols_len = cursor.getColumnCount();
            while (cursor.moveToNext()) {
                for (int i = 0; i < cols_len; i++) {
                    String cols_key = cursor.getColumnName(i);
                    String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_key, cols_value);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(cursor != null){
            cursor.close();
        }
        closeDatabase();
        return map;
    }

    /**
     for example
     listPersonMaps("user", null, null);

     * @param table
     * @param selection
     * @param selectionArgs
     * @return
     */
    public List<Map<String, String>> listPersonMaps(String table, String selection, String[] selectionArgs, String orderBy) {
        openDatabase();
        List<Map<String, String>> list = new ArrayList<Map<String, String>>();
        Cursor cursor = null;
        try {
            cursor = db.query(false, table, null, selection,selectionArgs, null, null, orderBy, null);
            int cols_len = cursor.getColumnCount();
            while (cursor.moveToNext()) {
                Map<String, String> map = new HashMap<String, String>();
                for (int i = 0; i < cols_len; i++) {
                    String cols_key = cursor.getColumnName(i);
                    String cols_value = cursor.getString(cursor.getColumnIndex(cols_key));
                    if (cols_value == null) {
                        cols_value = "";
                    }
                    map.put(cols_key, cols_value);
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(cursor != null){
            cursor.close();
        }
        closeDatabase();
        return list;
    }


    /**
     * 批量插入
     * @param sTableName
     * @param ListOfMaps
     */
    public void InsertMaps(String sTableName, List<Map<String, Object>> ListOfMaps)
    {
        openDatabase();
        try
        {
            boolean bFirst=true;
            if(ListOfMaps==null || ListOfMaps.size()<1)
            {
                return;
            }

            Log.e("insertbegin", "begin");
            db.beginTransaction();
            for(int i=0;i<ListOfMaps.size();i++)
            {
                bFirst= true;
                StringBuilder sb=new StringBuilder();
                StringBuilder columns=new StringBuilder();
                StringBuilder values=new StringBuilder();
                sb = sb.append("INSERT INTO [" + sTableName + "] (");

                ArrayList<Object>listOfValues = new ArrayList<Object>();
                Map<String, Object> map = ListOfMaps.get(i);

                Iterator<Map.Entry<String, Object>> iterator =map.entrySet().iterator();
                while(iterator.hasNext())
                {
                    Map.Entry<String, Object> next = iterator.next();
                    if(!bFirst)
                    {
                        columns = columns.append(", ");
                        values = values.append(", ");
                    }
                    columns.append("[").append(next.getKey()).append("]");
                    values.append("?");
                    listOfValues.add(next.getValue());
                    bFirst = false;
                }
                sb = sb.append(columns.toString()).append(") VALUES (").append(values.toString()).append(")");
                ExecNonQuery2(sb.toString(), listOfValues);
            }
            db.setTransactionSuccessful();
            db.endTransaction();
            Log.e("insertend", "end");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        closeDatabase();
    }

    public void ExecNonQuery2(String Statement, List<Object> Args)
    {
        SQLiteStatement s = this.db.compileStatement(Statement);
        try
        {
            int numArgs = 0;
            if (Args != null)
            {
                numArgs = Args.size();
            }
            for (int i = 0; i < numArgs; i++)
            {
                DatabaseUtils.bindObjectToProgram(s, i + 1, Args.get(i));
            }
            s.execute();
        } finally
        {
            s.close();
        }
    }

    public boolean isOpen(){
        if(db != null){
            return db.isOpen();
        }
        return false;
    }
}


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:19145次
    • 积分:567
    • 等级:
    • 排名:千里之外
    • 原创:37篇
    • 转载:2篇
    • 译文:0篇
    • 评论:0条
    文章分类