Sqlite的操作示例代码

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

/**
 * 黑名单数据库
 */
public class BlackNumberOpenHelper extends SQLiteOpenHelper {


    public BlackNumberOpenHelper(Context context) {
        super(context, "blacknumber.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建表 number: 电话号码; mode: 拦截模式,0(拦截电话),1(拦截短信),2(拦截全部)
        String sql = "create table blacknumber(_id integer primary key autoincrement, number " +
                "varchar(30), mode integer)";
        db.execSQL(sql);
    }

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

    }
}

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import cn.loaderman.mobilesafe11.db.BlackNumberOpenHelper;
import cn.loaderman.mobilesafe11.domain.BlackNumberInfo;

/**
 * Created by Administrator on 2016/11/24 0024.
 * <p/>
 * 黑名单增删改查操作: crud
 * <p/>
 * 单例设计模式
 */
public class BlackNumberDao {

    private final BlackNumberOpenHelper mHelper;

    //3. 声明一个静态对象: 两种方式初始化: 1. 饿汉模式
    // private static BlackNumberDao mInstance = new BlackNumberDao();
    //2. 懒汉模式
    private static BlackNumberDao mInstance;

    //1. 私有构造方法
    private BlackNumberDao(Context ctx) {
        mHelper = new BlackNumberOpenHelper(ctx);
    }

    //2.公开方法,返回单例对象
    public static BlackNumberDao getInstance(Context ctx) {
        //懒汉: 考虑线程安全问题, 两种方式: 1. 给方法加同步锁 synchronized, 效率低; 2. 给创建对象的代码块加同步锁
        //读数据不会出现线程安全问题, 写数据会出现线程安全问题
        //A, B, C
        if (mInstance == null) {
            //B, C
            synchronized (BlackNumberDao.class) {
                //A
                if (mInstance == null) {
                    mInstance = new BlackNumberDao(ctx);
                }
            }
        }

        return mInstance;
    }

    public boolean add(String number, int mode) {
        SQLiteDatabase database = mHelper.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put("number", number);
        values.put("mode", mode);
        //返回的是插入记录的id, -1表示失败
        long insert = database.insert("blacknumber", null, values);
        database.close();
        return insert != -1;
    }

    public boolean delete(String number) {
        SQLiteDatabase database = mHelper.getWritableDatabase();
        //返回删除的行数
        int delete = database.delete("blacknumber", "number=?", new String[]{number});
        database.close();
        return delete > 0;
    }

    public boolean update(String number, int mode) {
        SQLiteDatabase database = mHelper.getWritableDatabase();
        ContentValues values = new ContentValues();
        //values.put("number", number);
        values.put("mode", mode);
        //返回更新的行数
        int update = database.update("blacknumber", values, "number=?", new String[]{number});
        database.close();
        return update > 0;
    }

    //查询某号码是否在黑名单数据库中
    public boolean find(String number) {
        SQLiteDatabase database = mHelper.getReadableDatabase();

        Cursor cursor = database.query("blacknumber", null,
                "number=?", new
                        String[]{number}, null, null, null);
        boolean exist = false;
        if (cursor != null) {
            if (cursor.moveToNext()) {
                //查询到了结果
                //return true;
                exist = true;
            }
            cursor.close();
        }
        database.close();

        return exist;
    }

    //查询某个号码的拦截模式
    //返回-1表示没有查到结果
    public int findMode(String number) {
        SQLiteDatabase database = mHelper.getReadableDatabase();

        Cursor cursor = database.query("blacknumber", new String[]{"mode"},
                "number=?", new
                        String[]{number}, null, null, null);

        int mode = -1;
        if (cursor != null) {
            if (cursor.moveToNext()) {
                //查询到了结果
                mode = cursor.getInt(0);
            }
            cursor.close();
        }
        database.close();
        return mode;
    }
    //查询所有黑名单集合
    public ArrayList<BlackNumberInfo> findAll() {
        SQLiteDatabase database = mHelper.getReadableDatabase();
        Cursor cursor = database.query("blacknumber", new String[]{"number", "mode"},
                null, null, null, null, null);
        ArrayList<BlackNumberInfo> list = new ArrayList<>();
        if (cursor != null) {
            while (cursor.moveToNext()) {
                BlackNumberInfo info = new BlackNumberInfo();
                String number = cursor.getString(0);
                int mode = cursor.getInt(1);
                info.number = number;
                info.mode = mode;
                list.add(info);
            }
            cursor.close();
        }
        database.close();

        return list;
    }

    //分页查询黑名单集合
    //index:查询的起始位置
    public ArrayList<BlackNumberInfo> findPart(int index) {
        SQLiteDatabase database = mHelper.getReadableDatabase();
        //select * from blacknumber limit 0,20
        //limit x,y : x代表起始数据位置; y代表查询多少条记录
        //根据id逆序排列
        Cursor cursor = database.rawQuery("select number,mode from blacknumber order by _id desc " +"limit ?,20", new String[]{index + ""});
        ArrayList<BlackNumberInfo> list = new ArrayList<>();
        if (cursor != null) {
            while (cursor.moveToNext()) {
                BlackNumberInfo info = new BlackNumberInfo();
                String number = cursor.getString(0);
                int mode = cursor.getInt(1);
                info.number = number;
                info.mode = mode;
                list.add(info);
            }
            cursor.close();
        }
        database.close();
        return list;
    }

    //返回条目总个数
    public int getTotalCount() {
        //select count(*) from blacknumber
        SQLiteDatabase database = mHelper.getReadableDatabase();
        Cursor cursor = database.rawQuery("select count(*) from blacknumber", null);
        int count = 0;
        if (cursor != null) {
            if (cursor.moveToNext()) {
                count = cursor.getInt(0);
            }
            cursor.close();
        }
        database.close();
        return count;
    }
}