概述
通过SqliteDataBase的一些方法来自已写一些公用类可以实现对Sqlite数据库的常规操作,
但是Android为我们提供了更好用的SQLiteOpenHelper 为什么不用呢?自已写一个公用类继承自这个helper,然后把自己对数据库的一引起操作封装进去,美美哒。
不啰嗦了,更多说明直接写在注释里了
封装继承 SQLiteOpenHelper
public class DbHelper extends SQLiteOpenHelper {
/**
* 在继承SQLiteOpenHelper辅助类时,必须构造该函数
*
* @param context 上下文对象
* @param name 数据库名称
* @param factory
* @param version 当前数据库版本,值必须是整数且递增的状态
*/
public DbHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
super(context, name, factory, version);
}
public DbHelper(Context context){
this(context, PubUtil.getDb(), null, 1);
}
/**
* 第一次创建数据库的时候会回调onCreate方法,
* 当使用getReadableDatabase()方法获取数据库实例时,如果数据库不存在,也会调用这个方法
* 主要作用:创建数据表
*
* @param sqLiteDatabase
*/
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
String strSql = "CREATE TABLE tb_mc(mc_id INTEGER PRIMARY KEY, mc_year TEXT, mc_month TEXT, mc_day TEXT, mc_volume INTEGER, mc_rem TEXT)";
sqLiteDatabase.execSQL(strSql);
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
}
里面可以加一些自定义的方法
public Boolean isHave(String strSql) {
SQLiteDatabase db = this.getReadableDatabase();
try {
Cursor cursor = db.rawQuery(strSql, null);
if (cursor != null && cursor.getCount() > 0) {
cursor.close();
db.close();
return true;
}
} catch (Exception e) {
log(e.getMessage());
if (db != null) db.close();
}
return false;
}
Activity 中实例化封装类
AppFolderDbHelper dbhelper = new AppFolderDbHelper(this); //继承自SQLiteOpenHelper的自定义dbhelper
SQLiteDatabase db; //实例化一个SQLiteDatabase以操作数据库表数据
db = dbhelper.getWritableDatabase(); //创建或打开可读写的数据库,执行增删改操作
db = dbhelper.getReadableDatabase(); //创建或打开可读的数据库,执行查操作
查询数据封装入你的bean数据模板类中
ArrayList<AccountInfoModule> lstAccount = new ArrayList<>();
private void getAccountInfo() {
db = dbHelper.getReadableDatabase();
String sql = "SELECT * FROM tb_account WHERE account_type='" + accountType + "' order by account_address";
try {
Cursor cursor = db.rawQuery(sql, null);
if (cursor != null) {
if (cursor.getCount() >= 0) {
while (cursor.moveToNext()) {
int id = cursor.getInt(cursor.getColumnIndex("account_id"));
String username = cursor.getString(cursor.getColumnIndex("account_username"));
String address = cursor.getString(cursor.getColumnIndex("account_address"));
String type = cursor.getString(cursor.getColumnIndex("account_type"));
lstAccount.add(new AccountInfoModule(id, username, address, type));
}
}
cursor.close();
}
} catch (Exception e) {
showDialogError(e.getMessage());
}
if (db != null) db.close();
}
删除指定id数据
db = dbHelper.getWritableDatabase();
try {
db.delete("tb_account", "account_id=?", new String[]{String.valueOf(accountInfo.getAccountId())});
}catch (Exception e){
showDialogError(e.getMessage());
}
if(db != null) db.close();
保存数据到数据库
通过 insert() / update() 方法进行新增修改
private void saveAccountInfo(){
db = dbHelper.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("account_type", mView.editAccountType.getText().toString());
cv.put("account_address", mView.editAccountAddress.getText());
cv.put("account_username", mView.editAccountUsername.getText());
cv.put("account_password", mView.editAccountPassword.getText());
cv.put("account_tel", mView.editAccountTel.getText());
cv.put("account_email", mView.editAccountEmail.getText());
cv.put("account_birthday", mView.editAccountBirthday.getText());
cv.put("account_question", mView.editAccountQuestion.getText());
cv.put("account_answer", mView.editAccountAnswer.getText());
try {
if(accountId == -1) db.insert("tb_account", null, cv);
else db.update("tb_account", cv, "account_id=?", new String[]{String.valueOf(accountId)});
}catch (Exception e){
showDialogError(e.getMessage());
}
if(db != null) db.close();
}