自己封装的，用了很久了，突然今天突然想起来把之前总结的一些东西传上来，就一次性全传上来了，都有注释。
第一个工具类，DatabaseManger:
public class DatabaseManger<T> { private DBHelper dbHelper ; private static DatabaseManger instance =null; private SQLiteDatabase sqLiteDatabase; /** * * 构造方法上下文 * * @param context * @return */ private DatabaseManger(Context context) { dbHelper = new DBHelper(context); sqLiteDatabase = dbHelper.getWritableDatabase(); } /** * * 获取本类对象的实例 * @param context * @return */ public static final DatabaseManger getInstance(Context context) { if (instance == null) { if(context == null) { throw new RuntimeException("Context is null."); } instance = new DatabaseManger(context); } return instance; } /** * 关闭数据库 */ public void close() { if(sqLiteDatabase.isOpen()) { sqLiteDatabase.close(); sqLiteDatabase=null; } if(dbHelper!=null) { dbHelper.close(); dbHelper=null; } if(instance != null) { instance = null; } } /** * 执行一条sql语句 * */ public void execSql(String sql) { if(sqLiteDatabase.isOpen()) { sqLiteDatabase.execSQL(sql); } else { throw new RuntimeException("The DataBase has already closed"); } } /** * sql执行查询操作的sql语句 * selectionargs查询条件 * 返回查询的游标，可对数据进行操作，但是需要自己关闭游标 */ public Cursor queryData2Cursor(String sql,String[] selectionArgs)throws Exception { Cursor cursor = null; if(sqLiteDatabase.isOpen()) { cursor = sqLiteDatabase.rawQuery(sql,selectionArgs); }else { throw new RuntimeException("The DataBase has already closed"); } return cursor; } /** * 查询表中数据总条数 * 返回表中数据条数 * */ public int getDataCounts(String table)throws Exception { Cursor cursor = null; int counts = 0; if(sqLiteDatabase.isOpen()) { cursor = queryData2Cursor("select * from "+ table,null); if(cursor != null && cursor.moveToFirst()) { counts = cursor.getCount(); } }else { throw new RuntimeException("The DataBase has already closed"); } return counts; } /** * * 消除表中所有数据 * @param table * @throws Exception */ public void clearAllData(String table)throws Exception { if(sqLiteDatabase.isOpen()) { execSql("delete from "+ table); }else { throw new RuntimeException("The DataBase has already closed"); } } /** * * 插入数据 * @param sql 执行操作的sql语句 * @param bindArgs sql中的参数，参数的位置对于占位符的顺序 * @return 返回插入对应的额ID，返回0，则插入无效 * @throws Exception */ public long insertDataBySql(String sql,String[] bindArgs)throws Exception { long id = 0; if(sqLiteDatabase.isOpen()) { SQLiteStatement sqLiteStatement = sqLiteDatabase.compileStatement(sql); if(bindArgs != null) { int size = bindArgs.length; for (int i=0; i < size;i++) { sqLiteStatement.bindString(i+1,bindArgs[i]); } id=sqLiteStatement.executeInsert(); sqLiteStatement.close(); } }else { throw new RuntimeException("The DataBase has already closed"); } return id; } /** * * 插入数据 * @param table 表名 * @param values 数据 * @return 返回插入的ID，返回0，则插入失败 * @throws Exception */ public long insetData(String table, ContentValues values)throws Exception { long id=0; if(sqLiteDatabase.isOpen()) { id=sqLiteDatabase.insertOrThrow(table,null,values); }else { throw new RuntimeException("The DataBase has already closed"); } return id; } /** * * 批量插入数据 * @param table 表名 * @param list 数据源 * @param args 数据键名 key * @return * @throws Exception */ public long insertBatchData(String table, List<Map<String,Object>> list,String[] args)throws Exception { long insertNum =0; sqLiteDatabase.beginTransaction(); ContentValues contentValues = new ContentValues(); for(int i=0; i <list.size();i++) { for(int j=0;j<args.length;j++) { contentValues.put(args[j],list.get(i).get(args[j]).toString()); } long id = insetData(table,contentValues); if(id >0) { insertNum++; } } sqLiteDatabase.setTransactionSuccessful(); sqLiteDatabase.endTransaction(); return insertNum; } /** * * 更新数据 * @param table 表名 * @param values 需要更新的数据 * @param whereClaause 表示sql语句中条件部分的语句 * @param whereArgs 表示占位符的值 * @return * @throws Exception */ public int updateData(String table,ContentValues values,String whereClaause,String[] whereArgs)throws Exception { int rowsNum = 0; if(sqLiteDatabase.isOpen()) { rowsNum = sqLiteDatabase.update(table,values,whereClaause,whereArgs); }else { throw new RuntimeException("The DataBase has already closed"); } return rowsNum; } /** * * 删除数据 * @param sql 待执行的sql语句 * @param bindArgs sql语句中的参数，参数的顺序对应占位符的顺序 */ public void deleteDataBySql(String sql,String[] bindArgs)throws Exception { if(sqLiteDatabase.isOpen()) { SQLiteStatement statement = sqLiteDatabase.compileStatement(sql); if(bindArgs != null) { int size = bindArgs.length; for(int i= 0;i<size;i++) { statement.bindString(i+1,bindArgs[i]); } statement.execute(); statement.close(); } }else { throw new RuntimeException("The DataBase has already closed"); } } /** * * 删除数据 * @param table 表名 * @param whereClause sql中的条件语句部分 * @param whereArgs 占位符的值 * @return */ public long deleteData(String table,String whereClause,String[] whereArgs)throws Exception { long rowsNum =0; if(sqLiteDatabase.isOpen()) { rowsNum=sqLiteDatabase.delete(table,whereClause,whereArgs); }else { throw new RuntimeException("The DataBase has already closed"); } return rowsNum; } /** * * @param table 表名 * @param columns 查询需要返回的列的字段 * @param selection SQL语句中的条件语句 * @param selectionArgs 占位符的值 * @param groupBy 表示分组，可以为NULL * @param having SQL语句中的having，可以为null * @param orderBy 表示结果排序，可以为null * @return * @throws Exception */ public Cursor queryData(String table,String[] columns,String selection,String[] selectionArgs,String groupBy,String having,String orderBy)throws Exception { return queryData(table,columns,selection,selectionArgs,groupBy,having,orderBy,null); } /** * * @param table 表名 * @param columns 查询需要返回的列的字段 * @param selection SQL语句中的条件语句 * @param selectionArgs 占位符的值 * @param groupBy 表示分组，可以为NULL * @param having SQL语句中的having，可以为null * @param orderBy 表示结果排序，可以为null * @param limit 表示分页 * @return * @throws Exception */ public Cursor queryData(String table,String[] columns,String selection,String[] selectionArgs, String groupBy,String having,String orderBy,String limit)throws Exception { return queryData(false,table,columns,selection,selectionArgs,groupBy,having,orderBy,limit); } /** * @param distinct true if you want each row to be unique,false otherwise * @param table 表名 * @param columns 查询需要返回的列的字段 * @param selection SQL语句中的条件语句 * @param selectionArgs 占位符的值 * @param groupBy 表示分组，可以为NULL * @param having SQL语句中的having，可以为null * @param orderBy 表示结果排序，可以为null * @param limit 表示分页 * @return * @throws Exception */ public Cursor queryData(boolean distinct,String table,String[] columns,String selection, String[] selectionArgs,String groupBy, String having,String orderBy,String limit)throws Exception { return queryData(null,distinct,table,columns,selection,selectionArgs,groupBy,having,orderBy,limit); } /** * @param cursorFactory 游标工厂 * @param distinct true if you want each row to be unique,false otherwise * @param table 表名 * @param columns 查询需要返回的列的字段 * @param selection SQL语句中的条件语句 * @param selectionArgs 占位符的值 * @param groupBy 表示分组，可以为NULL * @param having SQL语句中的having，可以为null * @param orderBy 表示结果排序，可以为null * @param limit 表示分页 * @return * @throws Exception */ public Cursor queryData(SQLiteDatabase.CursorFactory cursorFactory,boolean distinct,String table,String[] columns,String selection, String[] selectionArgs,String groupBy, String having,String orderBy,String limit)throws Exception { Cursor cursor = null; if(sqLiteDatabase.isOpen()){ cursor = sqLiteDatabase.queryWithFactory(cursorFactory, distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit); }else{ throw new RuntimeException("The database has already closed!"); } return cursor; } /** * * @param sql 执行查询造作的SQL语句 * @param selectionArgs 查询条件 * @param object JAVABEAN对象 * @return 查询结果 */ public List<Map<String,String >> query2List(String sql,String[] selectionArgs,Object object)throws Exception { List<Map<String,String>> list = new ArrayList<>(); if(sqLiteDatabase.isOpen()) { Cursor cursor = null; cursor = queryData2Cursor(sql,selectionArgs); Field[] fields; HashMap<String,String> map; if(cursor !=null && cursor.getCount()>0) { while (cursor.moveToNext()) { map = new HashMap<>(); fields = object.getClass().getDeclaredFields(); for(int i =0; i< fields.length;i++) { /** * 1通过key，即列名，得到所在的列索引 * 2通过所在行以及所在列的索引，得到唯一确定的队友值 * 3将值与键封装到MAP集合中，此条数据读取完毕 */ map.put(fields[i].getName(),cursor.getString(cursor.getColumnIndex(fields[i].getName()))); } list.add(map); } cursor.close(); } }else { throw new RuntimeException("The database has already closed!"); } return list; } }
第二个工具类，DBHelpter
public class DBHelper extends SQLiteOpenHelper{ //***数据库名称 private static final String DATABASE_NAME = "z_android_day14.db"; //数据库版本号 private static final int DATABASE_VERSION=5; //创建表,用户信息表 public static final String TABLE_USERINFO="user_info"; //创建用户信息表，建表语句 public static final String TABLE_CITYINFO="city_info"; public static final String TABLE_PROVINCEINFO="province_info"; private static final String CREATE_USERINFO_SQL="CREATE TABLE " + TABLE_USERINFO + " (_id Integer primary key autoincrement," + " uid integer," + " nickname text," + " avatar_url text," + " username text," + " account text," + " password text);"; private static final String TABEL_WEATHERINFO = "weather_info"; private static final String CREATE_WEATHER_SQL="CREATE TABLE " + TABEL_WEATHERINFO + " (_id Integer primary key autoincrement," + " cityid integer," + " weather text," + " degree text);"; //城市信息表 private static final String CREATE_CITY_SQL="CREATE TABLE " + TABLE_CITYINFO + " (_id Integer primary key autoincrement," + " province_id text," + " city_num text," + " name text);"; //省份信息表 private static final String CREATE_PROVINCE_SQL="CREATE TABLE " + TABLE_PROVINCEINFO + " (_id Integer primary key autoincrement," + " name text," + " province_id text);"; public DBHelper (Context context) { this(context,DATABASE_NAME,null,DATABASE_VERSION); } public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) { super(context, name, factory, version); } public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) { super(context, name, factory, version, errorHandler); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_USERINFO_SQL); db.execSQL(CREATE_WEATHER_SQL); db.execSQL(CREATE_PROVINCE_SQL); db.execSQL(CREATE_CITY_SQL); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { if(newVersion > oldVersion) { db.execSQL("DROP TABLE IF EXISTS "+ TABLE_USERINFO); db.execSQL("DROP TABLE IF EXISTS "+ TABEL_WEATHERINFO); db.execSQL("DROP TABLE IF EXISTS "+ TABLE_CITYINFO); db.execSQL("DROP TABLE IF EXISTS "+ TABLE_PROVINCEINFO); onCreate(db); } } }
扫一扫
486
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
