經常用得到的安卓資料庫基類，安卓資料庫基類

經常用得到的安卓資料庫基類，安卓資料庫基類

//建立資料庫  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;      }    }