Android Sqlite数据库操作
Android中的数据存储方式有:SharedPreferences 首选项,文件,网络,sqllite;
很多时候开发APP时需要缓存数据,其中以SQL为最常用了. 在此记录一下sql的各种方法.
创建本地数据库与表时,需要写一个数据库帮助类继承SQLiteOpenHelper类.
public class DbHelper extends SQLiteOpenHelper
重写方法onCreate(),onUpgrade();
/** * 首次创建数据库时调用 第一次调用后不再调用 * * */ @Override public void onCreate(SQLiteDatabase sdb) {} /** 此方法用于数据更新表结构 */ @Override public void onUpgrade(SQLiteDatabase sdb, int oldVersion, int newVersion) { Log.i(TAG, "更新表结构..."); }
贴一个使用实例:
/** * @category 入库记录 * * */ public class DbPutStorage { private static final String TAG = "DbPutStorage"; private DbHelper helper; private SQLiteDatabase db; private static final String TABLE_NAME = "putStorage"; private static DbPutStorage dbPutStorage; public static DbPutStorage getInstance(Context mContext) { if (null == dbPutStorage) { dbPutStorage = new DbPutStorage(mContext); } return dbPutStorage; } public DbPutStorage(Context mContext) { helper = new DbHelper(mContext); // 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, // mFactory); // 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里 db = helper.getWritableDatabase(); } /** * 添加一条数据 * * @param status:上传状态(0:未上传,1:已上传),logoNo:订单标识 * */ public synchronized void addSingle(String logoNo,String _id, String name, String description, String productCode, String order, String price, String originalPrice, double quantiy,int status) { db = helper.getWritableDatabase(); Log.i(TAG, "添加单条数据到临时表"); ContentValues cv = new ContentValues(); cv.put("logoNo", logoNo); cv.put("pro_id", _id); cv.put("name", name); cv.put("description", description); cv.put("productCode", productCode); cv.put("orderNo", order); cv.put("price", price); cv.put("originalPrice", originalPrice); cv.put("quantiy", quantiy); cv.put("status", status); db.insert(TABLE_NAME, null, cv); cv.clear(); } /** * 查询所有数据 * * */ public Cursor selectAll() { db = helper.getWritableDatabase(); Cursor c = db.query(TABLE_NAME, null, null, null, null, null, null); return c; } /** * 修改某条数据状态 * * */ public void updateStatus(String pro_id){ db = helper.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put("status", 1); String[] args = {String.valueOf(pro_id)}; db.update(TABLE_NAME, cv, "pro_id=?",args); } /** * 通过rawQuery实现参数查询 * * @category 根据商品code查询详情 * */ public Cursor queryByCode(String search_context) { db = helper.getWritableDatabase(); String sql = "select * from " + TABLE_NAME + " where productCode=?"; String[] selectionArgs = new String[] { search_context }; Cursor cursor = db.rawQuery(sql, selectionArgs); Log.i(TAG, "根据商品code(" + search_context + ")查询" + cursor.getCount()); return cursor; } /** * 通过rawQuery实现参数查询 * * @category 根据时间date查询详情 * */ public Cursor queryByDate(String search_context) { db = helper.getWritableDatabase(); String sql = "select * from " + TABLE_NAME + " where date=?"; String[] selectionArgs = new String[] { search_context }; Cursor cursor = db.rawQuery(sql, selectionArgs); Log.i(TAG, "根据时间date(" + search_context + ")查询" + cursor.getCount()); return cursor; } /** * 通过rawQuery实现模糊参数查询 * * @category 根据商品名称查询 * */ public Cursor queryName(String search_context) { db = helper.getWritableDatabase(); String sql = "select * from " + TABLE_NAME + " where name like ?"; String[] selectionArgs = new String[] { "%" + search_context + "%" }; Cursor cursor = db.rawQuery(sql, selectionArgs); // if (cursor.moveToFirst()) { // return cursor; // } Log.i(TAG, "根据商品名称查询" + cursor.getCount()); return cursor; } /** * 通过rawQuery实现模糊参数查询 * * @category 根据商品标签查询 * */ public Cursor queryTagId(String search_context) { db = helper.getWritableDatabase(); String sql = "select * from " + TABLE_NAME + " where productTag like ?"; String[] selectionArgs = new String[] { "%" + search_context + "%" }; Cursor cursor = db.rawQuery(sql, selectionArgs); Log.i(TAG, "根据商品标签查询" + cursor.getCount()); return cursor; } /** * 通过rawQuery实现模糊参数查询 * * @category 根据商品名称或者商品code模糊查询 * */ public Cursor query(String search_context) { db = helper.getWritableDatabase(); String sql = "select * from " + TABLE_NAME + " where name like ? or productCode like ?"; String[] selectionArgs = new String[] { "%" + search_context + "%", "%" + search_context + "%" }; Cursor cursor = db.rawQuery(sql, selectionArgs); Log.i(TAG, "模糊查询" + cursor.getCount()); return cursor; } /** * 判断该条记录是否已存在 * * */ public boolean judgeCode(String code) { db = helper.getWritableDatabase(); Cursor c = db.rawQuery("select * from " + TABLE_NAME + " where productCode=?", new String[] { code }); if (c.moveToFirst()) { int i = c.getCount(); if (i > 0) { return true; } } return false; } /** * 删除一条指定数据 * * */ public void deleteCodeSingle(String productCode) { db = helper.getWritableDatabase(); String whereClause = "productCode=?";// 删除的条件 String[] whereArgs = { productCode };// 删除的条件参数 Log.i(TAG, "删除一条指定数据"); db.delete(TABLE_NAME, whereClause, whereArgs);// 执行删除 } /** * 删除状态为已上传的数据 * * */ public void deleteStatus(String status){ db = helper.getWritableDatabase(); String whereClause = "status=?";// 删除的条件 String[] whereArgs = { status };// 删除的条件参数 Log.i(TAG, "删除一条已上传的数据"); db.delete(TABLE_NAME, whereClause, whereArgs);// 执行删除 } /** * 删除全部数据 * * */ public void deleteTableData() { db = helper.getWritableDatabase(); String sql = "delete from " + TABLE_NAME;// 删除操作的SQL语句 Log.i(TAG, "删除sql所有数据..."); db.execSQL(sql);// 执行删除操作 } /** * close database 关闭数据库 */ public void closeDB() { db = helper.getWritableDatabase(); db.close(); } }