SQLite概述

class MyDBOpenHelper extends SQLiteOpenHelper { /** * * @param context 应用程序上下文 * @param name 数据库的名字 * @param factory 查询数据库的游标工厂 一般情况下 用sdk默认的 * @param version 数据库的版本 版本号必须不小1 * */ public MyDBOpenHelper(Context context) { super(context, "itheima.db", null, 5); } // 在mydbOpenHelper 在数据库第一次被创建的时候 会执行onCreate(); @Override public void onCreate(SQLiteDatabase db) { System.out.println("我被调用了 oncreate"); db.execSQL("CREATE TABLE person (personid integer primary key autoincrement, name varchar(20))"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { System.out.println("on update "); db.execSQL("ALTER TABLE person ADD phone VARCHAR(12) NULL "); } }

 

MyDBOpenHelper helper = new MyDBOpenHelper(this);

SQLiteDatabase ddatabase = helper.getReadableDatabase();

获取数据库后进行CRUD

 

常用DAO代码参考：

/**
 * 数据库常用Dao
 */
public class BlackNumberDao {
       private BlackNumberDBOpenHelper helper;
       public BlackNumberDao(Context context) {
              helper = new BlackNumberDBOpenHelper(context);
       }
 
       /**
        * 查找一条黑名单号码
        */
       public boolean find(String number) {
              boolean result = false;
              SQLiteDatabase db = helper.getReadableDatabase();
              if (db.isOpen()) {
                     Cursor cursor = db.rawQuery(
                                   "select * from blacknumber where number =?",
                                   new String[] { number });
                     if (cursor.moveToFirst()) {
                            result = true;
                     }
                     cursor.close();
                     db.close();
              }
              return result;
       }
      
       /**
        * 查找一条黑名单号码的拦截模式
        */
       public int findNumberMode(String number) {
              int result = -1;
              SQLiteDatabase db = helper.getReadableDatabase();
              if (db.isOpen()) {
                     Cursor cursor = db.rawQuery(
                                   "select mode from blacknumber where number =?",
                                   new String[] { number });
                     if (cursor.moveToFirst()) {
                            result = cursor.getInt(0);
                     }
                     cursor.close();
                     db.close();
              }
              return result;
       }
      
       /**
        * 添加一条黑名单号码
        */
       public boolean add(String number, String mode) {
              if (find(number))// 防止添加重复的数据
                     return false;
 
              SQLiteDatabase db = helper.getWritableDatabase();
              if (db.isOpen()) {
                     db.execSQL("insert into blacknumber (number,mode) values (?,?)",
                                   new Object[] { number, mode });
                     db.close();
              }
             
              return find(number);
       }
 
       /**
        * 删除一条黑名单号码
        */
       public void delete(String number) {
 
              SQLiteDatabase db = helper.getWritableDatabase();
              if (db.isOpen()) {
                     db.execSQL("delete from blacknumber where number=?",
                                   new Object[] { number });
 
                     db.close();
              }
 
       }
 
       /**
        * 更改黑名单号码
        *
        * @param oldnumber 旧的的电话号码
        * @param newnumber 新的号码 可以留空
        * @param mode 新的模式
        */
       public void update(String oldnumber, String newnumber, String mode) {
             
              SQLiteDatabase db = helper.getWritableDatabase();
              if (db.isOpen()) {
                     if (TextUtils.isEmpty(newnumber)) {
                            newnumber = oldnumber;
                     }
                     db.execSQL(
                                   "update blacknumber set number=?, mode=? where number=?",
                                   new Object[] { newnumber, mode, oldnumber });
                     db.close();
              }
       }
 
       /**
        * 查找全部的黑名单号码
        * @return
        */
       public List<BlackNumber> findAll() {
              List<BlackNumber> numbers = new ArrayList<BlackNumber>();
              SQLiteDatabase db = helper.getReadableDatabase();
              if (db.isOpen()) {
                     Cursor cursor = db.rawQuery("select number,mode from blacknumber",
                                   null);
                     while (cursor.moveToNext()) {
                            BlackNumber blackNumber = new BlackNumber();
                            blackNumber.setNumber(cursor.getString(0));
                            blackNumber.setMode(cursor.getInt(1));
                            numbers.add(blackNumber);
                            blackNumber = null;
                     }
                     cursor.close();
                     db.close();
              }
              return numbers;
       }
       /**
        * 分页的加载数据
        * @param startindex
        * @param maxresult
        * @return
        */
       public List<BlackNumber> findAllByPage(int startindex ,int maxresult) {
              List<BlackNumber> numbers = new ArrayList<BlackNumber>();
              SQLiteDatabase db = helper.getReadableDatabase();
              if (db.isOpen()) {
                     Cursor cursor = db.rawQuery("select number,mode from blacknumber limit ?,? ",
                                   new String[]{startindex+"",maxresult+""});
                     while (cursor.moveToNext()) {
                            BlackNumber blackNumber = new BlackNumber();
                            blackNumber.setNumber(cursor.getString(0));
                            blackNumber.setMode(cursor.getInt(1));
                            numbers.add(blackNumber);
                            blackNumber = null;
                     }
                     cursor.close();
                     db.close();
              }
              return numbers;
       }
}
 

 

SQLite概述

标签：