数据库SQLiteOpenHelper的简单封装使用

建立一个数据库操作的单例类，跟数据类PlanInfo配合使用，单例类大致如下，具体使用都是通过单例类操作，例如PlanDBHelper.getInstance().xxx。

package com.ldw.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ldw.App.MyApplication;
import com.ldw.data.PlanInfo;

import java.util.ArrayList;

import timber.log.Timber;

/**
 * 数据库计划表。
 * 1、如果PlanInfo类增减成员，要更改该类相关位置。
 * 2、少量的数据操作可直接在主线程进行；大量的数据操作，时间长的建议在子线程中去执行。
 * Created by ldw on 16-11-12.
 */
public class PlanDBHelper extends SQLiteOpenHelper {

    //数据库名，默认路径是在data/data/包名/databases目录下
    private static final String DB_NAME = "plan.db";
    //数据库版本
    private static final int DB_VERSION = 1;
    //计划表名
    private static final String TABLE_NAME = "plan";

    private static PlanDBHelper dbHelper = null;

    public static PlanDBHelper getInstance() {
        if (dbHelper == null) {
            synchronized (PlanDBHelper.class){
                if (dbHelper == null) {
                    dbHelper = new PlanDBHelper(MyApplication.getInstance());
                }
            }
        }
        return dbHelper;
    }

    private PlanDBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        Timber.v("---PlanDBHelper---");
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Timber.v("---onCreate---");
        sqLiteDatabase.execSQL(SQL_CRATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        Timber.v("---onUpgrade---");
        sqLiteDatabase.execSQL(SQL_DELETE_TABLE);
        onCreate(sqLiteDatabase);
    }

    public PlanInfo[] queryUserPlans(int uid){
        PlanInfo[] planInfos = null;
        SQLiteDatabase db = getReadableDatabase();

        //方式一
        // 调用SQLiteDatabase对象的query方法进行查询，返回一个Cursor对象：由数据库查询返回的结果集对象
        // 第一个参数String：表名
        // 第二个参数String[]:要查询的列名，null则读取所有
        // 第三个参数String：查询条件
        // 第四个参数String[]：查询条件的参数
        // 第五个参数String:对查询的结果进行分组
        // 第六个参数String：对分组的结果进行限制
        // 第七个参数String：对查询的结果进行排序
        Cursor cursor = db.query(
                TABLE_NAME,
                null,
                Table.UID + " = ?",
                new String[] { String.valueOf(uid) },
                null,
                null,
                Table.START_TIME + " DESC");//降序DESC，升序ASC

        //方式二
        /*String sql = "SELECT * FROM " + Table.TABLE_NAME
                + " where " + Table.UID + " = " + uid
                + " ORDER BY " + Table.START_TIME + " DESC";
        cursor = db.rawQuery(sql, null);*/

        //方式三
        //cursor = db.rawQuery("select * from plan where uid = ?", new String[] {String.valueOf(uid)});

        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        int count = cursor.getCount();
        //Timber.v("---queryUserPlans---count=%d" , count);
        if(count > 0){
            planInfos = new PlanInfo[count];
            int i = 0;
            while (cursor.moveToNext()) {
                PlanInfo planInfo = new PlanInfo();
                planInfo.id = cursor.getInt(cursor.getColumnIndex(Table.ID));
                planInfo.uid = cursor.getInt(cursor.getColumnIndex(Table.UID));
                planInfo.bookName = cursor.getString(cursor.getColumnIndex(Table.BOOK_NAME));
                planInfos[i] = planInfo;
                i++;
            }
        }

        cursor.close();
        db.close();

        return planInfos;
    }
    public PlanInfo queryUserPlan(int uid,int pid){
        PlanInfo planInfo = new PlanInfo();
        SQLiteDatabase db = getReadableDatabase();

        //方式一
        // 调用SQLiteDatabase对象的query方法进行查询，返回一个Cursor对象：由数据库查询返回的结果集对象
        // 第一个参数String：表名
        // 第二个参数String[]:要查询的列名，null则读取所有
        // 第三个参数String：查询条件
        // 第四个参数String[]：查询条件的参数
        // 第五个参数String:对查询的结果进行分组
        // 第六个参数String：对分组的结果进行限制
        // 第七个参数String：对查询的结果进行排序
        Cursor cursor = db.query(
                TABLE_NAME,
                null,
                Table.UID + " = ? and " + Table.ID + " = ?",
                new String[] { String.valueOf(uid),String.valueOf(pid) },
                null,
                null,
                Table.START_TIME + " DESC");

        //方式二
        /*String sql = "SELECT * FROM " + Table.TABLE_NAME
                + " where " + Table.UID + " = " + uid
                + " ORDER BY " + Table.START_TIME + " DESC";
        cursor = db.rawQuery(sql, null);*/

        //方式三
        //cursor = db.rawQuery("select * from plan where uid = ?", new String[] {String.valueOf(uid)s});

        // 将光标移动到下一行，从而判断该结果集是否还有下一条数据，如果有则返回true，没有则返回false
        int count = cursor.getCount();
        if(count > 0){
            while (cursor.moveToNext()) {
                planInfo.id = cursor.getInt(cursor.getColumnIndex(Table.ID));
                planInfo.uid = cursor.getInt(cursor.getColumnIndex(Table.UID));
                planInfo.bookName = cursor.getString(cursor.getColumnIndex(Table.BOOK_NAME));
            }
            cursor.close();
            db.close();
            return planInfo;
        }
        cursor.close();
        db.close();
      return null;
    }

    public void insert(ContentValues values){
        //获取SQLiteDatabase实例
        SQLiteDatabase db = getWritableDatabase();
        //插入数据库中
        db.insert(TABLE_NAME, null, values);
        db.close();
    }

    public void update(int uid, int id, ContentValues values){
        if(values != null && values.size() > 0){
            SQLiteDatabase db = getWritableDatabase();
            db.update(TABLE_NAME,
                    values,
                    Table.UID + " = ? and " + Table.ID + " = ?",
                    new String[]{String.valueOf(uid), String.valueOf(id)});
            db.close();
        }
    }
    public void updateLastPlayCourseId(int uid,int pid,int cId){
        ContentValues contentValues = new ContentValues();
        contentValues.put(Table.LAST_PLAY_COURSE_ID,cId);
        SQLiteDatabase db = getWritableDatabase();
        db.update(TABLE_NAME,
                contentValues,
                Table.UID + " = ? and " + Table.ID + " = ?",
                new String[]{String.valueOf(uid), String.valueOf(pid)});
        db.close();
    }


    public void delete(int uid, int id){
        SQLiteDatabase db = getWritableDatabase();
        db.delete(TABLE_NAME,
                Table.UID + " = ? and " + Table.ID + " = ?",
                new String[]{String.valueOf(uid), String.valueOf(id)});
        db.close();
    }

    public void insertAllPlans(ArrayList<PlanInfo> planInfos){
        SQLiteDatabase db = getWritableDatabase();
        db.beginTransaction();
        for (PlanInfo planInfo: planInfos) {
            db.insert(TABLE_NAME, null, parsePlanInfo2ContentValues(planInfo));
        }
        db.setTransactionSuccessful();
        db.endTransaction();
        db.close();
    }

    public void deleteAllPlans(int uid){
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("DELETE FROM "+TABLE_NAME + " WHERE "+ Table.UID  + " = " + uid +";");
        db.close();
    }

    //创建表数据库语音
    private static final String SQL_CRATE_TABLE =
            "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
                    + " ("
                    + Table.ID + " INTEGER, "
                    + Table.UID + " INTEGER, "
                    + Table.BOOK_NAME + " TEXT, "
                    + "PRIMARY KEY(" + Table.ID + ", " + Table.UID + ")"
                    + ")";

    //删除表数据库语音
    private static final String SQL_DELETE_TABLE =
            "DROP TABLE IF EXISTS "+ TABLE_NAME;

    //计划表
    public static class Table {
        public static final String ID = "id";
        public static final String UID = "uid";
        public static final String BOOK_NAME = "book_name";
    }

    static public ContentValues parsePlanInfo2ContentValues(PlanInfo planInfo){
        if (planInfo != null) {
            ContentValues cvPlan = new ContentValues();
            cvPlan.put(Table.ID, planInfo.id);
            cvPlan.put(Table.UID, MyApplication.getInstance().getMyUid());
            cvPlan.put(Table.BOOK_NAME, planInfo.bookName);
            return cvPlan;
        }else{
            return  null;
        }
    }

}