首页 > 代码库 > 经常用得到的安卓数据库基类
经常用得到的安卓数据库基类
Java代码
- //创建数据库
- public class DBCreate {
- public static void CreateDatabase(SQLiteDatabase db) {
- db.beginTransaction();
- try {
- create_NetTaskBuffer(db);
- insert_SQL(db);
- db.setTransactionSuccessful();
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- db.endTransaction();
- }
- }
- // 缓存
- private static void create_NetTaskBuffer(SQLiteDatabase db) {
- db.execSQL("DROP TABLE IF EXISTS NetTaskBuffer");
- StringBuilder sql = new StringBuilder();
- sql.append("CREATE TABLE IF NOT EXISTS NetTaskBuffer (");
- sql.append(" id INTEGER PRIMARY KEY AUTOINCREMENT,"); // 这一列不能修改
- sql.append(" label TEXT COLLATE NOCASE,"); //
- sql.append(" param TEXT COLLATE NOCASE,"); //
- sql.append(" result TEXT COLLATE NOCASE,"); //
- sql.append(" remark TEXT COLLATE NOCASE,");
- sql.append(" time LONG)"); //
- db.execSQL(sql.toString());
- }
- // 插入语句,初始化数据库使用
- private static void insert_SQL(SQLiteDatabase db) {
- }
- }
- //----------------------数据库操作基类--------------------------------------//
- /**
- * 数据库基本操作类,数据库的创建,更新的操作都在这里进行
- *
- * @author yizhe
- * @date 2014-9-11
- */
- public abstract class DBHelper extends SQLiteOpenHelper {
- static String name = "hk.db"; // 数据库名称
- static CursorFactory cursorFactory = null;
- static int version = 1000;// 初始版本:1000
- Context context;
- protected ContentValues contentValues; // cursor对象转行中介,给子类使用
- protected String tableName;
- protected DBHelper(Context context, String tableName) {
- super(context, name, cursorFactory, version);
- this.context = context;
- this.tableName = tableName;
- }
- /**
- * 软件第一次安装的时候会调用,覆盖安装不会调用
- */
- public void onCreate(SQLiteDatabase db) {
- // 所有表的创建过程都在这里进行
- DBCreate.createDatabase(db);
- }
- /**
- * 覆盖安装,当版本号version发生变化的时候,这个方法才会被调用,而且只执行一次
- */
- public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
- onCreate(db);
- // if (oldVersion < 109) {
- // onCreate(db);
- // } else {
- // }
- }
- /**
- * 每次成功打开数据库后首先被执行
- */
- public void onOpen(SQLiteDatabase db) {
- super.onOpen(db); // 每次成功打开数据库后首先被执行
- }
- public void finalize() {
- close();
- }
- // --------------------------sql方法---------------------------------//
- /**
- * 执行sql,没有返回
- */
- public void execSQL(String sql) {
- System.out.println("==execSQL==" + sql);
- SQLiteDatabase db = getWritableDatabase();
- db.execSQL(sql);
- db.close();
- }
- /**
- * 批量执行sql,内部启用事务
- *
- * @param list
- * sql列表
- * @return 是否执行成功
- */
- public boolean execSQLBatch(ArrayList<String> list) {
- SQLiteDatabase db = getWritableDatabase();
- db.beginTransaction();
- try {
- for (String sql : list) {
- db.execSQL(sql);
- }
- db.setTransactionSuccessful();
- } catch (Exception e) {
- e.printStackTrace();
- return false;
- } finally {
- db.endTransaction();
- db.close();
- }
- return true;
- }
- /**
- * 根据id删除记录
- *
- * @param id
- * 表中必须有"id"字段
- * @return the number of rows affected if a whereClause is passed in, 0
- * otherwise. To remove all rows and get a count pass "1" as the
- * whereClause.
- */
- public int delete(int id) {
- SQLiteDatabase db = getWritableDatabase();
- int result = db.delete(tableName, "id=?", new String[] { id + "" });
- db.close();
- return result;
- }
- /**
- * 删除: 只需要写where 条件(不带where) 和 参数
- *
- * @param whereStr
- * 例如: "id=?"
- * @param arr
- * 例如: new String[] { "123" }
- * @return 受影响的行
- */
- public int delete(String whereStr, String[] arr) {
- SQLiteDatabase db = getWritableDatabase();
- int result = db.delete(tableName, whereStr, arr);
- db.close();
- return result;
- }
- /**
- * 清空表
- */
- public void clearTable() {
- SQLiteDatabase db = getWritableDatabase();
- db.execSQL("delete from " + tableName);
- db.close();
- }
- /**
- * 通用查询,多用于事务中
- */
- public static Cursor Query(SQLiteDatabase db, String sql) {
- System.out.println("==Query==" + sql);
- return db.rawQuery(sql, new String[] {});
- }
- /**
- * 通用执行sql,,多用于事务中
- *
- */
- public static void execSQL(SQLiteDatabase db, String sql) {
- System.out.println("==execSQL==" + sql);
- db.execSQL(sql);
- }
- }
- //-------------------------下面是一个具体实现的DEMO-------------------------------//
- /**
- * dao类,实现基本的数据库操作<br/>
- * 需要保存到数据库中的对象的属性不能使用基本类型,建议只使用Integer 和 String<br/>
- * 做为通用对象,从demo创建只要修改tableName即可
- *
- * @author yizhe
- * @date 2012-5-18
- */
- public class NetTaskBufferDao extends DBHelper {
- int expiryDays = 10; // 数据缓存有效期
- public NetTaskBufferDao(Context context) {
- super(context, "NetTaskBuffer");
- }
- // pojo的整数都需要使用Long类型 或者Integer类型 建议使用Long
- public void iniContentValues(NetTaskBuffer pojo) {
- contentValues = new ContentValues();
- contentValues.put("id", pojo.id);
- contentValues.put("label", pojo.label);
- contentValues.put("param", pojo.param);
- contentValues.put("result", pojo.result);
- contentValues.put("remark", pojo.remark);
- contentValues.put("time", pojo.time);
- }
- public NetTaskBuffer setBaseItem(Cursor cursor) {
- NetTaskBuffer pojo = new NetTaskBuffer();
- pojo.id = cursor.getInt(cursor.getColumnIndex("id"));
- pojo.label = cursor.getString(cursor.getColumnIndex("label"));
- pojo.param = cursor.getString(cursor.getColumnIndex("param"));
- pojo.result = cursor.getString(cursor.getColumnIndex("result"));
- pojo.remark = cursor.getString(cursor.getColumnIndex("remark"));
- pojo.time = cursor.getLong(cursor.getColumnIndex("time"));
- return pojo;
- }
- // --------------------自定义方法--------------------------------//
- public String getBuffer(String label, String param) {
- String sql = "select * from " + tableName
- + " where label=? and param=? ";
- NetTaskBuffer obj = getOneAsSQL(sql, new String[] { label, param });
- if (null == obj) {
- return null;
- }
- Date time = new Date(obj.time);
- Calendar c = Calendar.getInstance();
- c.add(Calendar.DAY_OF_MONTH, -expiryDays);
- if (time.compareTo(c.getTime()) < 0) {
- delete(obj.id);
- return null;
- }
- return obj.result;
- }
- public String getBuffer(String label, String param, String remark) {
- String sql = "select * from " + tableName
- + " where label=? and param=? and remark=?";
- NetTaskBuffer obj = getOneAsSQL(sql, new String[] { label, param,
- remark });
- if (null == obj) {
- return null;
- }
- Date time = new Date(obj.time);
- Calendar c = Calendar.getInstance();
- c.add(Calendar.DAY_OF_MONTH, -expiryDays);
- if (time.compareTo(c.getTime()) < 0) {
- delete(obj.id);
- return null;
- }
- return obj.result;
- }
- public void deleteBuffer(String label) {
- String whereSql = " label=?";
- delete(whereSql, new String[] { label });
- }
- public void deleteBuffer(String label, String param) {
- String whereSql = " label=? and param=?";
- delete(whereSql, new String[] { label, param });
- }
- public void deleteBuffer(String label, String param, String remark) {
- String whereSql = " label=? and param=? and remark=?";
- delete(whereSql, new String[] { label, param, remark });
- }
- // --------------------基础方法---------------------------------//
- /**
- * 根据sql获取list
- */
- public ArrayList<NetTaskBuffer> getListAsSQL(String sql) {
- SQLiteDatabase db = getReadableDatabase();
- Cursor cursor = db.rawQuery(sql, new String[] {});
- ArrayList<NetTaskBuffer> itemList = new ArrayList<NetTaskBuffer>();
- for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
- itemList.add(setBaseItem(cursor));
- }
- cursor.close();
- db.close();
- return itemList;
- }
- /**
- * 根据ID获取一条记录
- */
- public NetTaskBuffer getById(int id) {
- String sql = "select * from " + tableName + " where id=" + id;
- return getOneAsSQL(sql, null);
- }
- /**
- * 返回结果中的提一条记录
- */
- public NetTaskBuffer getOneAsSQL(String sql, String[] arr) {
- SQLiteDatabase db = getReadableDatabase();
- Cursor cursor = db.rawQuery(sql, arr);
- try {
- if (null != cursor && cursor.getCount() > 0) {
- cursor.moveToFirst();
- return setBaseItem(cursor);
- }
- } finally {
- cursor.close();
- db.close();
- }
- return null;
- }
- /**
- * 保存对象到数据库,自动判断插入还是更像
- *
- * @return 返回更新的记录数,或者 插入数据的id,如果返回值<=0表示失败
- */
- public long save(NetTaskBuffer pojo) {
- if (null == pojo.time || 0 == pojo.time) {
- pojo.time = new Date().getTime();
- }
- Long idOrEffectRows = 0l;
- if (null == pojo.id || pojo.id < 1) {
- idOrEffectRows = insert(pojo);
- } else {
- idOrEffectRows = (long) update(pojo);
- }
- return idOrEffectRows;
- }
- /**
- * 添加数据,自动插入id
- *
- * @return the row ID of the newly inserted row, or -1 if an error occurred
- */
- public long insert(NetTaskBuffer pojo) {
- SQLiteDatabase db = getWritableDatabase();// 获取可写SQLiteDatabase对象
- iniContentValues(pojo); // ContentValues类似map,存入的是键值对
- long result = db.insert(tableName, null, contentValues);
- if (result != -1) {
- pojo.id = (int) result;
- }
- db.close();
- return result;
- }
- /**
- * 根据ID更新记录的,跟插入的很像
- *
- * @return the number of rows affected
- *
- */
- public int update(NetTaskBuffer pojo) {
- SQLiteDatabase db = getWritableDatabase();
- iniContentValues(pojo); // 初始化键值对
- int result = db.update(tableName, contentValues, "id=?",
- new String[] { pojo.id + "" });
- db.close();
- return result;
- }
- }
安卓标准数据库构建.zip (9.2 KB)
经常用得到的安卓数据库基类
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。