前言
Android的SQLite对本地数据的保存起到重要的作用,也可以用来做一些网络数据的缓存。
使用SQLiteOpenHelper
使用 SQLiteOpenHelper可以很方便的创建和打开一个数据库;
- 继承SQLiteOpenHelper;
public class ItemsDBHelper extends SQLiteOpenHelper
- 在类的前面写上数据库名、表名、列名及数据库创建语句;写上类的私有属性;
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "Items.db";
public static final String TABLE_NAME = "items";
public static final String COLUMN_SMS = "sms";
public static final String COLUMN_NUMBER = "number";
public static final String CREATE_TABLE ="create table " + TABLE_NAME + " ("
+ "id integer primary key autoincrement,"
+ COLUMN_SMS +" text, "
+ COLUMN_NUMBER + " text)";
private Context mContext;
private SQLiteDatabase mDB;
- 覆写构造方法;传入上下文
context
;调用super,传入参数;使用getWritableDatabase
获取数据库对象;
public ItemsDBHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mContext = context;
mDB = this.getWritableDatabase();
}
- 覆写抽象方法;在
onCreate
中执行创建数据库的任务;
@Override
public void onCreate(SQLiteDatabase db) {
mDB.execSQL(CREATE_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
保存数据
- 先判断下数据的存在,如果存在,就不继续插入数据,从方法中返回;使用
ContentValues
类,作为数据插入的对象;
public void saveItem(ItemBean itemBean) {
if(hasItem(itemBean)) {
return;
}
ContentValues values = new ContentValues();
values.put(COLUMN_SMS, itemBean.getSms());
values.put(COLUMN_NUMBER, itemBean.getNumber());
mDB.insert(TABLE_NAME, "", values);
}
删除数据
- 使用
SQLiteDatabase
的delete
方法;table
表名,whereClause
条件,whereArgs
条件参数;
public int delete(String table, String whereClause, String[] whereArgs)
- 代码如下:
public void deleteItem(ItemBean itemBean) {
String selection = COLUMN_SMS + "=? AND " + COLUMN_NUMBER + "=?";
String[] selectionArgs = {itemBean.getSms(), itemBean.getNumber()};
mDB.delete(TABLE_NAME, selection, selectionArgs);
}
查询数据
- 使用
SQLiteDatabase
的queryquery
方法;table
表名,columns
要返回的列,selection
条件,selectionArgs
条件参数,groupBy
分组,having
包含,orderBy
排序;返回一个Cursor
对象,来封装查询好的数据;
public Cursor query(String table, String[] columns, String selection,String[] selectionArgs, String groupBy, String having, String orderBy)
- 取出所有数据;
public ArrayList<ItemBean> loadItems() {
ArrayList<ItemBean> itemBeans = new ArrayList<ItemBean>();
Cursor cursor = mDB.query(TABLE_NAME, null, null, null, null, null,null);
if(cursor != null) {
while (cursor.moveToNext()) {
ItemBean itemBean = new ItemBean();
itemBean.setSms(cursor.getString(cursor.getColumnIndex(COLUMN_SMS)));
itemBean.setNumber(cursor.getString(cursor.getColumnIndex(COLUMN_NUMBER)));
itemBeans.add(itemBean);
}
}
Log.i(Config.TAG, "ItemsDBHelper->loadItems: " + itemBeans);
return itemBeans;
}
- 按查询条件取出数据;
public ArrayList<String> getNumbers(String sms) {
ArrayList<String> sNumbers = new ArrayList<String>();
String selection = COLUMN_SMS + "=?";
String[] selectionArgs = new String[] {sms};
Cursor cursor = mDB.query(TABLE_NAME, null, selection, selectionArgs, null, null,null);
if(cursor != null) {
while (cursor.moveToNext()) {
String sNumber = cursor.getString(cursor.getColumnIndex(COLUMN_NUMBER));
sNumbers.add(sNumber);
}
}
return sNumbers;
}
- 判断数据的存在;
public boolean hasItem(ItemBean itemBean) {
String selection = COLUMN_SMS + "=? AND " + COLUMN_NUMBER + "=?";
String[] selectionArgs = new String[] {itemBean.getSms(), itemBean.getNumber()};
Cursor cursor = mDB.query(TABLE_NAME, null, selection, selectionArgs, null, null,null);
if(cursor == null || cursor.getCount() == 0) {
return false;
} else {
return true;
}
}
修改数据
- 先删除,再添加;这种方式属于偷懒的方式,亲测有效,但不知道有没有什么副作用,慎用;
public void updateItem(ItemBean oldItemBean, ItemBean newItemBean) {
deleteItem(oldItemBean);
saveItem(newItemBean);
}
- 正确方法;
public void updateItem(ItemBean oldItemBean, ItemBean newItemBean) {
ContentValues values = new ContentValues();
values.put(COLUMN_NUMBER, newItemBean.getNumber());
values.put(COLUMN_SMS, newItemBean.getSms());
String selection = COLUMN_SMS + "=? AND " + COLUMN_NUMBER + "=?";
String[] selectionArgs = {oldItemBean.getSms(), oldItemBean.getNumber()};
mDB.update(TABLE_NAME, values, selection, selectionArgs);
}
总结
- 使用
SQLiteOpenHelper
方便管理SQLite数据库; - 数据库的增、删、改、查;
- 增,要先判断一下数据有没有存在;使用
ContentValues
来封装要插入的数据; - 查,最后得出
Cursor
对象,使用moveToNext
来获取下一条数据; - 删和改,与增和查相似;