packagecom.asc.db;importandroid.content.ContentValues;importandroid.content.Context;importandroid.database.Cursor;importandroid.database.sqlite.SQLiteDatabase;importandroid.database.sqlite.SQLiteOpenHelper;/*** 数据库公共类,提供基本数据库操作*/
public classDBManager {//默认数据库
private static final String DB_NAME = "asc.db";//数据库版本
private static final int DB_VERSION = 1;//执行open()打开数据库时,保存返回的数据库对象
private SQLiteDatabase mSQLiteDatabase = null;//由SQLiteOpenHelper继承过来
private DatabaseHelper mDatabaseHelper = null;//本地Context对象
private Context mContext = null;private static DBManager dbConn= null;//查询游标对象
privateCursor cursor;/*** SQLiteOpenHelper内部类*/
private static class DatabaseHelper extendsSQLiteOpenHelper {
DatabaseHelper(Context context) {//当调用getWritableDatabase()或 getReadableDatabase()方法时,创建一个数据库
super(context, DB_NAME, null, DB_VERSION);
}
@Overridepublic voidonCreate(SQLiteDatabase db) {
db.execSQL("CREATE TABLE ad_record(id PRIMARY KEY NOT NULL, adUrl TEXT, apMac TEXT, createDate DATETIME);");
}
@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, intnewVersion) {
db.execSQL("DROP TABLE IF EXISTS ad_record");
onCreate(db);
}
}/*** 构造函数
*
*@parammContext*/
privateDBManager(Context mContext) {super();this.mContext =mContext;
}public staticDBManager getInstance(Context mContext){if (null ==dbConn) {
dbConn= newDBManager(mContext);
}returndbConn;
}/*** 打开数据库*/
public voidopen() {
mDatabaseHelper= newDatabaseHelper(mContext);
mSQLiteDatabase=mDatabaseHelper.getWritableDatabase();
}/*** 关闭数据库*/
public voidclose() {if (null !=mDatabaseHelper) {
mDatabaseHelper.close();
}if (null !=cursor) {
cursor.close();
}
}/*** 插入数据
*@paramtableName 表名
*@paramnullColumn null
*@paramcontentValues 名值对
*@return新插入数据的ID,错误返回-1
*@throwsException*/
public longinsert(String tableName, String nullColumn,
ContentValues contentValues)throwsException {try{returnmSQLiteDatabase.insert(tableName, nullColumn, contentValues);
}catch(Exception e) {throwe;
}
}/*** 通过主键ID删除数据
*@paramtableName 表名
*@paramkey 主键名
*@paramid 主键值
*@return受影响的记录数
*@throwsException*/
public long delete(String tableName, String key, int id) throwsException {try{return mSQLiteDatabase.delete(tableName, key + " = " + id, null);
}catch(Exception e) {throwe;
}
}/*** 查找表的所有数据
*@paramtableName 表名
*@paramcolumns 如果返回所有列,则填null
*@return*@throwsException*/
public Cursor findAll(String tableName, String [] columns) throwsException{try{
cursor= mSQLiteDatabase.query(tableName, columns, null, null, null, null, null);
cursor.moveToFirst();returncursor;
}catch(Exception e) {throwe;
}
}/*** 根据主键查找数据
*@paramtableName 表名
*@paramkey 主键名
*@paramid 主键值
*@paramcolumns 如果返回所有列,则填null
*@returnCursor游标
*@throwsException*/
public Cursor findById(String tableName, String key, int id, String [] columns) throwsException {try{return mSQLiteDatabase.query(tableName, columns, key + " = " + id, null, null, null, null);
}catch(Exception e) {throwe;
}
}/*** 根据条件查询数据
*@paramtableName 表名
*@paramnames 查询条件
*@paramvalues 查询条件值
*@paramcolumns 如果返回所有列,则填null
*@paramorderColumn 排序的列
*@paramlimit 限制返回数
*@returnCursor游标
*@throwsException*/
public Cursor find(String tableName, String [] names, String [] values, String [] columns, String orderColumn, String limit) throwsException{try{
StringBuffer selection= newStringBuffer();for (int i = 0; i < names.length; i++) {
selection.append(names[i]);
selection.append(" = ?");if (i != names.length - 1) {
selection.append(",");
}
}
cursor= mSQLiteDatabase.query(true, tableName, columns, selection.toString(), values, null, null, orderColumn, limit);
cursor.moveToFirst();returncursor;
}catch(Exception e) {throwe;
}
}/***
*@paramtableName 表名
*@paramnames 查询条件
*@paramvalues 查询条件值
*@paramargs 更新列-值对
*@returntrue或false
*@throwsException*/
public boolean udpate(String tableName, String [] names, String [] values, ContentValues args) throwsException{try{
StringBuffer selection= newStringBuffer();for (int i = 0; i < names.length; i++) {
selection.append(names[i]);
selection.append(" = ?");if (i != names.length - 1) {
selection.append(",");
}
}return mSQLiteDatabase.update(tableName, args, selection.toString(), values) > 0;
}catch(Exception e) {throwe;
}
}/*** 执行sql语句,包括创建表、删除、插入
*
*@paramsql*/
public voidexecuteSql(String sql) {
mSQLiteDatabase.execSQL(sql);
}
}