均属于笔记，仅供个人参考，有问题欢迎指正
这里介绍的是Android数据库的原始用法，实际中可以直接使用liteOrm或者greenDao
1，建立帮助类
public class DBHelper extends SQLiteOpenHelper {
//数据库名称
private static final String DATABASE_NAME = "Foot.db";
//数据库版本号
private static final int DATABASE_VERSION = 1;
public DBHelper(Context context) {
// CursorFactory设置为null,使用默认值
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// 数据库第一次被创建时onCreate会被调用，这里注意对id的处理（在相应的Bean中）
// 只建立一张表，其他表类似
@Override
public void onCreate(SQLiteDatabase db) {
//建立食物操作步骤数据表
db.execSQL("create table FootStep(id VARCHAR PRIMARY KEY,steptext VARCHAR,stepimg VARCHAR," +
"footid VARCHAR)");
//建立食物简单介绍数据表
db.execSQL("create table FootIntroduce(id VARCHAR PRIMARY KEY,footname VARCHAR,footimg VARCHAR," +
"itemname VARCHAR,remark VARCHAR)");
//建立食物简单操作步骤数据表
db.execSQL("create table FootMenu(id VARCHAR PRIMARY KEY,footmenu VARCHAR,footid VARCHAR)");
// db.execSQL("create table Person(id VARCHAR PRIMARY KEY,name VARCHAR,sex VARCHAR," +
// "age VARCHAR,phone VARCHAR,nickname VARCHAR)");
// String sql = "create table tb_newsItem( _id integer primary key autoincrement , "
// + " title text , link text , date text , imgLink text , content text , newstype integer );";
// db.execSQL(sql);
}
// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// db.execSQL("drop table if exists Person");
// db.execSQL("drop table if exists tb_newsItem");
// onCreate(db);
}
}
2，数据库的简单操作
public class DBClient {
private DBHelper helper;
private SQLiteDatabase db;
private DBClient(Context context) {
helper = new DBHelper(context);
// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0,
// mFactory);
// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里或者application中
db = helper.getWritableDatabase();
}
public static DBClient dbClient;
public static DBClient getInstance(Context context) {
if (dbClient == null)
dbClient = new DBClient(context);
return dbClient;
}
/**
* close database
*/
public void closeDB() {
db.close();
}
/**
* FootStep数据表的相关操作
*/
// 批量添加多条数据
public void addFootSteps(ArrayList<FootStepBean> beans) {
db.beginTransaction(); //手动设置开始事务
try {
//批量处理操作
for (FootStepBean bean : beans) {
addFootStep(bean);
}
//在setTransactionSuccessful和endTransaction之间不进行任何数据库操作
db.setTransactionSuccessful(); //设置事务处理成功，不设置会自动回滚不提交。
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction(); //处理完成
}
}
// 新添加单条数据
public void addFootStep(FootStepBean bean) {
FootStepBean i = queryFootStepById(bean.getId());
if (TextUtils.isEmpty(i.getId())) {
db.execSQL(
"INSERT INTO FootStep VALUES(?,?,?,?)",
new Object[]{bean.getId(), bean.getSteptext(),
bean.getStepimg(), bean.getFootid()});
} else {
updateFootStep(bean);
}
}
// 更新数据
public void updateFootStep(FootStepBean bean) {
ContentValues cv = new ContentValues();
cv.put("id", bean.getId());
cv.put("steptext", bean.getSteptext());
cv.put("stepimg", bean.getStepimg());
cv.put("footid", bean.getFootid());
db.update("FootStep", cv, " id=? ", new String[]{bean.getId()});
}
// 根据id查询数据
public FootStepBean queryFootStepById(String id) {
FootStepBean bean = new FootStepBean();
Cursor c = queryTheCursorFootStepById(id);
if (c.moveToNext()) {
bean.setId(c.getString(c.getColumnIndex("id")));
bean.setSteptext(c.getString(c.getColumnIndex("steptext")));
bean.setStepimg(c.getString(c.getColumnIndex("stepimg")));
bean.setFootid(c.getString(c.getColumnIndex("footid")));
}
c.close();
return bean;
}
// 根据foodid查询数据
public ArrayList<FootStepBean> queryFootStepByFootid(String footid) {
ArrayList<FootStepBean> list = new ArrayList<FootStepBean>();
Cursor c = queryTheCursorFootStepByFootid(footid);
while (c.moveToNext()) {
FootStepBean bean = new FootStepBean();
bean.setId(c.getString(c.getColumnIndex("id")));
bean.setSteptext(c.getString(c.getColumnIndex("steptext")));
bean.setStepimg(c.getString(c.getColumnIndex("stepimg")));
bean.setFootid(c.getString(c.getColumnIndex("footid")));
list.add(bean);
}
c.close();
return list;
}
// 根据id查询
public Cursor queryTheCursorFootStepById(String id) {
Cursor c = db.rawQuery("SELECT * FROM FootStep where id = ?",
new String[]{id});
return c;
}
// 根据foodid查询
public Cursor queryTheCursorFootStepByFootid(String footid) {
Cursor c = db.rawQuery("SELECT * FROM FootStep where footid = ?",
new String[]{footid});
return c;
}
// 查询某一表单中含有多少行，返回查询条件
public Cursor queryTheCursorFootStepCount() {
Cursor c = db.rawQuery("SELECT count(1) FROM FootStep", null);
return c;
}
// 查询某一表单中含有多少行，返回具体int
public int queryFootStepCount() {
Cursor c = queryTheCursorFootStepCount();
if (c.moveToNext()) {
return c.getInt(0);
}
c.close();
return 0;
}
}
