创建两个包,db包用来创建数据库,dao包对数据库的增删改查的操作。
举个对手机黑名单数据库(BlackDB)增删改查的例子:
1、在db包中创该建数据库:
<span style="font-size:14px;">public class BlackBD extends SQLiteOpenHelper {
public BlackBD(Context context) {
super(context, "black.db", null, 1);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table blacktb(_id integer primary key autoincrement,phone text,mode integer )");
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
//我这里更新版本是清除数据
db.execSQL("drop table blacktb");
this.onCreate(db);
}
}</span>
2、在dao包中创建对该数据库对应的操作类:
package com.lmk.dao;
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import com.lmk.db.BlackBD;
import com.lmk.domain.BlackTableBean;
import com.lmk.utils.BlackTable;
public class BlackDao {
private BlackBD blackDB;
public BlackDao(Context context) {
blackDB = new BlackBD(context);
}
// *******************************分页以下***************************
/**
*
* @return 得到总数据(总条数)
*/
private int getTatalRows() {
int tatalRows = 0;
SQLiteDatabase db = blackDB.getReadableDatabase();
Cursor cursor = db.rawQuery("select count(1) from "
+ BlackTable.BLACKTABLENAME, null);
while (cursor.moveToNext()) {
tatalRows = cursor.getInt(0);
}
db.close();
return tatalRows;
}
/**
*
* @param prePages
* 指定的每页显示的条数
* @return 总页数
*/
public int getTatalPages(int prePages) {
int tatalPages = 0;
int tatalRows = getTatalRows();
tatalPages = (int) Math.ceil((tatalRows * 1.0) / prePages);
return tatalPages;
}
/**
*
* @param currentPage
* 当前页码(要显示的页码)
* @param perPage
* 每页显示的条数
* @return 指定页的数据
*/
public List<BlackTableBean> getPageDatas(int currentPage, int perPage) {
List<BlackTableBean> datas = new ArrayList<BlackTableBean>();
SQLiteDatabase db = blackDB.getReadableDatabase();
/**
* 数据库sql:开始显示的第一条位置,显示的条数
*/
Cursor cursor = db.rawQuery("select " + BlackTable.PHONE + ","
+ BlackTable.MODE + " from " + BlackTable.BLACKTABLENAME
+ " limit ?,?", new String[] {
((currentPage - 1) * perPage) + "", perPage + "" });
while (cursor.moveToNext()) {
BlackTableBean bean = new BlackTableBean();
bean.setPhone(cursor.getString(0));
bean.setMode(cursor.getInt(1));
datas.add(bean);
}
db.close();
return datas;
}
// *******************************分页以上***************************
// 获取所有的datas,未分页
public List<BlackTableBean> getAll() {
List<BlackTableBean> datas = new ArrayList<BlackTableBean>();
SQLiteDatabase database = blackDB.getReadableDatabase();
Cursor cursor = database.rawQuery("select " + BlackTable.PHONE + ","
+ BlackTable.MODE + " from " + BlackTable.BLACKTABLENAME
+ " order by _id desc ", null);
while (cursor.moveToNext()) {
BlackTableBean bean = new BlackTableBean();
bean.setPhone(cursor.getString(0));
bean.setMode(cursor.getInt(1));
datas.add(bean);
}
return datas;
}
public void delete(String phone) {
// 获取数据库
SQLiteDatabase db = blackDB.getWritableDatabase();
db.delete(BlackTable.BLACKTABLENAME, BlackTable.PHONE + "=?",
new String[] { phone });
db.close();
}
public void add(BlackTableBean bean) {
add(bean.getPhone(), bean.getMode());
}
public void add(String phone, int mode) {
// 先删除重复的
delete(phone);
// 获取数据库
SQLiteDatabase db = blackDB.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(BlackTable.PHONE, phone);
values.put(BlackTable.MODE, mode);
db.insert(BlackTable.BLACKTABLENAME, null, values);
db.close();
}
}