首页 > 代码库 > Sqlite的操作示例代码

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.itcast.mobilesafe11.db.BlackNumberOpenHelper;
import cn.itcast.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; } }

 

Sqlite的操作示例代码