安卓数据库基类

//创建数据库
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;
	}

}