Sqlite的操作範例程式碼

標籤：date   分頁   mode   gets   ext   led   ini   char   new   

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的操作範例程式碼