自己封装的，用了很久了，突然今天突然想起来把之前总结的一些东西传上来，就一次性全传上来了，都有注释。
第一个工具类，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);
}
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
