数据库操作的一般流程有以下步骤:
1.首先,写一个数据库操作帮助类Helper,这个类继承自SQLiteOpenHelper.
2.在自己的DAO层借助自己的Helper写数据库操作的一些方法
3.Activity调用DAO层的数据库操作方法进行操作
下面通过一个存储搜索记录的简单例子来了解Android中数据库操作。
一、写一个自己的数据库操作帮助类DbHelper,这个类继承自SQLiteOpenHelper,代码如下:
/**
* Created by zhpan on 2016/10/27.
*/
public class DbHelper extends SQLiteOpenHelper {
public static final String TB_NAME= "mydb.db";
private static final int VERSION = 1;
public DbHelper(Context context){
this(context,TB_NAME);
}
public DbHelper(Context context, String name) {
this(context, name, VERSION);
}
public DbHelper(Context context, String name, int version) {
this(context, name, null, version);
}
public DbHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,
int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table if not exists history(id integer primary key autoincrement,item varchar(60))");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if(newVersion>oldVersion){
// 更新数据库
}
}
}
二、在DAO层借助DbHelperf封装数据库操作的方法
在封装方法前先定义History的实体类,如下:
/**
* Created by zhpan on 2016/10/27.
*/
public class SearchHistoryBean {
private int id;
private String item; //搜索条目内容
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item;
}
}
接下来在Dao层封装数据库操作(增删改查)的方法,如下:
/**
* Created by zhpan on 2016/10/27.
*/
public class SearchHistoryDao {
DbHelper helper;
public SearchHistoryDao(Context context) {
helper = new DbHelper(context);
}
//添加历史记录
public boolean insertHistory(SearchHistoryBean history) {
SQLiteDatabase db = helper.getReadableDatabase();
try {
ContentValues values = new ContentValues();
values.put("item", history.getItem());
db.insert("history", null, values);
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
return false;
}
//更新历史记录
public boolean updateHistory(SearchHistoryBean history) {
SQLiteDatabase db = helper.getReadableDatabase();
try {
ContentValues values = new ContentValues();
values.put("item", history.getItem());
db.update("history", values, "item = ?", new String[]{history.getId() + ""});
return true;
} catch (Exception e) {
e.printStackTrace();
} finally {
db.close();
}
return false;
}
// 通过ID删除历史
public boolean delHistoryById(int id) {
SQLiteDatabase db = helper.getReadableDatabase();
try {
db.delete("history", "id=?", new String[]{id + ""});
return true;
} catch (Exception e) {
e.printStackTrace();//打印错误堆栈信息
} finally {
db.close();
}
return false;
}
// 通过item删除历史
public boolean delHistoryByItem(String item) {
SQLiteDatabase db = helper.getReadableDatabase();
try {
db.delete("history", "item=?", new String[]{item});
return true;
} catch (Exception e) {
//打印错误堆栈信息
e.printStackTrace();
} finally {
db.close();
}
return false;
}
//根据id查询历史记录
public SearchHistoryBean findById(int id) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query("history", null, "id=?", new String[]{id + ""}, null, null, null);
SearchHistoryBean history = null;
if (cursor.moveToNext()) {
history = new SearchHistoryBean();
history.setId(id);
history.setItem(cursor.getString(cursor.getColumnIndex("item")));
}
return history;
}
// 查询是否已经存在该item
public boolean isItemExists(String item) {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query("history", null, "item=?", new String[]{item}, null, null, null);
int count = cursor.getCount();
cursor.close();
if (count > 0) {
return true;
} else {
return false;
}
}
//查询所有历史记录
public List<SearchHistoryBean> findAll() {
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query("history", null, null, null, null, null, "id desc");
List<SearchHistoryBean> list = new ArrayList<>();
SearchHistoryBean history = null;
while (cursor.moveToNext()) {
history = new SearchHistoryBean();
history.setId(cursor.getInt(cursor.getColumnIndex("id")));
history.setItem(cursor.getString(cursor.getColumnIndex("item")));
list.add(history);
}
cursor.close();
return list;
}
//查询前limit条历史记录
public List<SearchHistoryBean> findLimit(int limit) {
SQLiteDatabase db = helper.getReadableDatabase();
//Cursor cursor = db.query("history", null, null, null, null, null, "id desc","limit 8");
Cursor cursor = db.rawQuery("select * from history order by id desc limit ?", new String[]{limit+""});
List<SearchHistoryBean> list = new ArrayList<>();
SearchHistoryBean history;
while (cursor.moveToNext()) {
history = new SearchHistoryBean();
history.setId(cursor.getInt(cursor.getColumnIndex("id")));
history.setItem(cursor.getString(cursor.getColumnIndex("item")));
list.add(history);
}
cursor.close();
return list;
}
// 删除所有历史记录
public boolean delAll() {
SQLiteDatabase db = helper.getReadableDatabase();
db.execSQL("delete from history");
return false;
}
}
完成以上操作后即可在Activity中通过SearchHistoryDao调用封装好的方法实现数据库增删改查的操作。