框架整理系列一(数据库)
2017-04-15 本文已影响0人
I_Gisvity
DBHelper.java
public class DBHelper extends SQLiteOpenHelper {
// 默认本数据库名称
private static final String DATABASE_NAME = "lhoa.db";
// 表集合
//public static Class<?>[] ClassList = new Class[]{FarmerTable.class, DiseaseTable.class};
public static ArrayList<Class> ClassList = new ArrayList<>();
/**
* 数据库版本升级
*/
private static final int DATABASE_VERSION = 7; //2.0增加药品库位和ID
/**
* @param context
*/
public DBHelper(Context context) {
//
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
/**
* @param context
* @param dataName
*/
public DBHelper(Context context, String dataName) {
//
super(context, dataName, null, DATABASE_VERSION);
}
/**
* @param context
* @param dataName
* @param version
*/
public DBHelper(Context context, String dataName, int version) {
//
super(context, dataName, null, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
try {
Log.i("Sqlite", "Create dataBase**********************");
if (ClassList.size() > 0) {
for (int i = 0; i < ClassList.size(); i++) {
String tableName = ClassList.get(i).getSimpleName();
Field[] declaredFields = ClassList.get(i).getDeclaredFields(); // 加局部变量
// 遍历方法集合
Log.i("OrmHelper", "=== start traversing getXX methods===="+tableName);
// 如果类里面的变量数目大于0,新建基础表
if (declaredFields.length > 0) {
db.execSQL("DROP TABLE IF EXISTS " + tableName);
db.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (_id INTEGER PRIMARY KEY AUTOINCREMENT )");
for (int m = 0; m < declaredFields.length; m++) { //针对类里每个属性增加表的各个字段
Class<?> cl = declaredFields[m].getType();
String TypeName = cl.getSimpleName();
if (TypeName.equals("int") || TypeName.equals("double") || TypeName.equals("float")) {
db.execSQL("ALTER TABLE " + tableName + " ADD '" + declaredFields[m].getName() + "' INTEGER");
} else {
db.execSQL("ALTER TABLE " + tableName + " ADD '" + declaredFields[m].getName() + "' VARCHAR");
}
}
}
}
}
} catch (Exception e) {
// TODO: handle exception
Log.i("Set_ZeroError", e.getClass().toString());
}
}
//
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.i("Sqlite", "ALTER dataBase *******************************");
if (oldVersion < DATABASE_VERSION) {
try {
onCreate(db);
// UpdateDB(db);
} catch (Exception e) {
// TODO: handle exception
Log.e("Sql_error", e.getMessage().toString());
}
}
}
/**
* 重置数据
*/
public void SetZero(String TableName) {
}
public static void DeleteDataBase(Context context) {
context.deleteDatabase(DATABASE_NAME);
}
/**
* 判断表是否存在，若不存在，则创建
*
* @param context
* @param cls
*/
public static void CreateTableWithJudge(Context context, Class<?> cls) {
if (!isHave(context, cls)) {
CreateTable(context, cls);
}
}
public static boolean isHave(Context context, Class<?> userClass) {
boolean result = false;
try {
DBHelper db = new DBHelper(context);
SQLiteDatabase database = db.getWritableDatabase();
String tableName = userClass.getSimpleName();
String sql = "select * from sqlite_sequence where name like '" + tableName + "'";
Cursor cursor = database.rawQuery(sql, null);
if (cursor.getCount() > 0) {
result = true;
} else {
result = false;
}
cursor.close();
database.close();
} catch (Exception ex) {
ex.fillInStackTrace();
}
return result;
}
/**
* 快捷建表 表名 Equip
* 例：DBHelper.CreateTable(CariAndroidOrmPronActivity.this,Equip.class);
*
* @param userClass
*/
public static void CreateTable(Context context, Class<?> userClass) {
try {
DBHelper db = new DBHelper(context);
SQLiteDatabase database = db.getWritableDatabase();
String tableName = userClass.getSimpleName();
Field[] methods = userClass.getDeclaredFields(); // 加载变量属性
// 遍历方法集合
Log.i("OrmHelper", "=== start traversing getXX methods===="+tableName);
// 如果类里面的变量数目大于0,新建基础表
if (methods.length > 0) {
database.execSQL("CREATE TABLE IF NOT EXISTS " + tableName + " (_id INTEGER PRIMARY KEY AUTOINCREMENT )");
Log.i("OrmHelper","CREATE TABLE IF NOT EXISTS " + tableName + " (_id INTEGER PRIMARY KEY AUTOINCREMENT )");
}
for (int i = 0; i < methods.length; i++) {
Class<?> cl = methods[i].getType();
String TypeName = cl.getSimpleName();
if (TypeName.equals("int")) {
Log.i("OrmHelper","ALTER TABLE " + tableName + " ADD '" + methods[i].getName() + "' int");
database.execSQL("ALTER TABLE " + tableName + " ADD '" + methods[i].getName() + "' int");
} else {
Log.i("OrmHelper","ALTER TABLE " + tableName + " ADD '" + methods[i].getName() + "' VARCHAR");
database.execSQL("ALTER TABLE " + tableName + " ADD '" + methods[i].getName() + "' VARCHAR");
}
}
database.close();
db.close();
Log.i("OrmHelper", "=== end ====");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 升级数据库
*/
public void UpdateDB(SQLiteDatabase db) {
if (ClassList.size() > 0) {
for (int i = 0; i < ClassList.size(); i++) {
String tableName = ClassList.get(i).getSimpleName();
Field[] methods = ClassList.get(i).getDeclaredFields(); // 加载变量属性,加载所有字段
CompareTableAndCreate(db, tableName, methods);
}
}
}
/**
* 对比需要生成的数据库表与设备中当前存在的数据库表的差别，并对有差别的表进行删除和重建
*/
private static void CompareTableAndCreate(SQLiteDatabase db, String table, Field[] methods) {
// if(clear)
// db.execSQL("drop table if exists "+table);
String sql = "_id INTEGER PRIMARY KEY AUTOINCREMENT";
for (int i = 0; i < methods.length; i++) {
Class<?> cl = methods[i].getType();
String TypeName = cl.getSimpleName();
if (TypeName.equals("int"))
sql = sql + "," + methods[i].getName() + " int";
else
sql = sql + "," + methods[i].getName() + " VARCHAR";
}
// 判断新数据库的字段是否与旧数据库一致，不一致则删除重建
boolean a = true;
for (int j = 0; j < methods.length; j++) {
a = checkColumnExist(db, table, methods[j].getName());
if (a == false)
break;
}
if (a == false) {
Log.i("CompareTableAndCreate", table);
db.execSQL("drop table if exists " + table);
db.execSQL("create table if not exists " + table + "(" + sql + ")");
} else
db.execSQL("create table if not exists " + table + "(" + sql + ")");
}
/**
* 判断需要生成的表中的字段与数据库中已有的表的字段是否一致
*/
static public boolean checkColumnExist(SQLiteDatabase db, String tableName, String columnName) {
boolean result = true;
Cursor cursor = null;
int index = -1;
try {
// 查询一行
cursor = db.rawQuery("SELECT * FROM " + tableName + " LIMIT 0", null);
if (cursor != null) {
index = cursor.getColumnIndex(columnName);
if (index == -1)
result = false;
} else
result = false;
} catch (Exception e) {
Log.i("checkColumnExistError", e.getMessage());
result = false;
} finally {
if (null != cursor && !cursor.isClosed()) {
cursor.close();
}
}
return result;
}
}
DataBaseUtil.java
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.channels.FileChannel;
import java.util.ArrayList;
/**
* @author zhouzhou
* @Title: SqliteHelper
* @Description:
* @Company: www.cari.com.cn
* @date 2015-9-24 下午5:03:18
*/
public class DataBaseUtil {
/**
* 反射机制，从数据库到数据表
*
* @param context
* @param cls
* @return
*/
@SuppressWarnings("unchecked")
public static ArrayList<Object> GetData(Context context, Class cls) {
return GetData(context, cls, "");
}
public static Object GetOneData(Context context, Class cls, String sqlStr){
ArrayList<Object> datas = GetData(context,cls,sqlStr);
if(datas.size()>0){
return datas.get(0);
}else {
return null;
}
}
/**
* 反射机制，从数据库到数据表
*
* @param context
* @param cls
* @return
*/
@SuppressWarnings("unchecked")
public static ArrayList<Object> GetData(Context context, Class cls, String sqlStr) {
ArrayList<Object> resultList = new ArrayList<Object>();
try {
DBHelper dbhelper = new DBHelper(context);
SQLiteDatabase db = dbhelper.getReadableDatabase(); // 上传id大于多少的....
String selectStr = cls.getDeclaredFields()[0].getName();
for (int i = 1; i < cls.getDeclaredFields().length; i++) {
selectStr = selectStr + "," + cls.getDeclaredFields()[i].getName();
}
Cursor cursor = db.rawQuery("SELECT " + selectStr + " FROM " + cls.getSimpleName() + " " + sqlStr, null);
for (cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()) {
Object obj = cls.newInstance();
for (int i = 0; i < cursor.getColumnCount(); i++) {
if (cursor.getString(i) != null) {
ReflectionHelper.doMethod("set" + toUpperCaseFirstOne(cursor.getColumnName(i)), obj, cursor.getString(i), cls.getDeclaredFields()[i].getType());
}
}
resultList.add(obj);
}
if (cursor != null) {
cursor.close();
}
db.close();
dbhelper.close();
} catch (Exception e) {
// TODO: handle exception
Log.e("SQLite ERROR", e.getMessage().toString());
}
return resultList;
}
/**
* 向数据库中插入数据ArrayList
*
* @param context
* @param cls
* @param
*/
public static void saveArrayListData(Context context, Class<?> cls, Object obj) {
String JsonStr = GsonUtil.toJson(obj);
JSONArray jsonArray = null;
try {
jsonArray = new JSONArray(JsonStr);
saveJsonArrayData(context, cls, jsonArray);
} catch (JSONException e) {
e.printStackTrace();
}
}
/**
* 向数据库中插入单个数据
*
* @param context
* @param cls
* @param
*/
public static void saveObjectData(Context context, Class<?> cls, Object obj) {
String JsonStr = GsonUtil.toJson(obj);
JSONObject jsonObject = null;
try {
jsonObject = new JSONObject(JsonStr);
} catch (JSONException e) {
e.printStackTrace();
}
saveJsonObjct(context, cls, jsonObject);
}
/**
* 插入JsonArray
*
* @param context
* @param cls
* @param jsonArray
*/
public static void saveJsonArrayData(Context context, Class<?> cls, JSONArray jsonArray) {
DBHelper db = new DBHelper(context);
SQLiteDatabase dataBase = db.getWritableDatabase();
try {
dataBase.beginTransaction(); // 手动设置开始事务
for (int i = 0; i < jsonArray.length(); i++) {
JSONObject itemdata = (JSONObject) jsonArray.get(i);
dataBase.insert(cls.getSimpleName(), null, InSertContentValues(cls, itemdata));
}
dataBase.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
Log.i("SQLite", "Success insert info into SqliteDatabase " + cls.getSimpleName());
} catch (Exception e) {
Log.e("SQLite ERROR " + cls.getSimpleName(), e.getMessage().toString());
} finally {
dataBase.endTransaction(); // 处理完成
dataBase.close();
db.close();
}
}
/**
* 插入JSONObject
*
* @param context
* @param cls
* @param jsonObject
*/
public static void saveJsonObjct(Context context, Class<?> cls, JSONObject jsonObject) {
DBHelper db = new DBHelper(context);
SQLiteDatabase dataBase = db.getWritableDatabase();
try {
dataBase.insert(cls.getSimpleName(), null, InSertContentValues(cls, jsonObject));
Log.i("SQLite", "Success insert info into SqliteDatabase " + cls.getSimpleName());
} catch (Exception e) {
Log.e("SQLite ERROR " + cls.getSimpleName(), e.getMessage().toString());
} finally {
dataBase.endTransaction(); // 处理完成
dataBase.close();
db.close();
}
}
/**
* 向ContentValues插入JSONObject
*
* @param cls
* @param jsonObject
* @return
* @throws JSONException
*/
public static ContentValues InSertContentValues(Class<?> cls, JSONObject jsonObject) throws JSONException {
ContentValues cv = new ContentValues();
JSONObject itemdata = jsonObject;
Field[] fields = cls.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
if (itemdata.has(fields[j].getName().toString())) {
cv.put(fields[j].getName().toString(), itemdata.getString(fields[j].getName().toString()));
}
}
return cv;
}
/**
* 清空指定数据表
*
* @param context
* @param cls
*/
public static void clearTable(Context context, Class<?> cls) {
DBHelper db = new DBHelper(context);
SQLiteDatabase dataBase = db.getWritableDatabase();
try {
dataBase.execSQL("delete from " + cls.getSimpleName());
} catch (Exception e) {
Log.e("SQLite ERROR " + cls.getSimpleName(), e.getMessage().toString());
} finally {
dataBase.close();
db.close();
}
}
public static void getOutDataBase() {
File f = new File("/data/data/com.lihua.oa/databases/lhoa.db"); //比如 "/data/data/com.hello/databases/test.db"
String sdcardPath = Environment.getExternalStorageDirectory().getAbsolutePath();
File o = new File(sdcardPath + "/cp.db"); //sdcard上的目标地址
if (f.exists()) {
FileChannel outF;
try {
outF = new FileOutputStream(o).getChannel();
new FileInputStream(f).getChannel().transferTo(0, f.length(), outF);
Log.e("sqlite", "数据库拷贝成功");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
//首字母转大写
public static String toUpperCaseFirstOne(String s)
{
if(Character.isUpperCase(s.charAt(0)))
return s;
else
return (new StringBuilder()).append(Character.toUpperCase(s.charAt(0))).append(s.substring(1)).toString();
}
}
ReflectionHelper.java
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
/**
* @author zhouzhou
* @Title: ReflectionHelper
* @Description:
* @Company: www.cari.com.cn
* @date 2015-9-24 下午4:01:53
*/
public class ReflectionHelper {
/**
* 快捷填充对象(标准化的对象)
*
* @param MethodName
* @param o 调用此方法的对象
* @param paras 调用的这个方法的参数参数列表
*/
public static void getMethod(String MethodName, Object o, Object[] paras) {
Class c[] = null;
if (paras != null) {// 存在
int len = paras.length;
c = new Class[len];
for (int i = 0; i < len; ++i) {
c[i] = paras[i].getClass();
}
}
try {
Method method = null;
if (c[0].getSimpleName().equals("Integer")) {
method = o.getClass().getDeclaredMethod(MethodName, int.class);
} else {
method = o.getClass().getDeclaredMethod(MethodName, c);
}
try {
method.invoke(o, paras);// 调用o对象的方法
} catch (IllegalAccessException ex) {
ex.fillInStackTrace();
} catch (IllegalArgumentException ex) {
ex.fillInStackTrace();
} catch (InvocationTargetException ex) {
ex.fillInStackTrace();
}
} catch (NoSuchMethodException ex) {
ex.fillInStackTrace();
} catch (SecurityException ex) {
ex.fillInStackTrace();
}
}
public static void getMethod(String MethodName, Object o, Object paras) {
Class c = null;
if (paras != null) {// 存在
c = paras.getClass();
}
try {
Method method = null;
if (c.getSimpleName().equals("Integer")) {
method = o.getClass().getDeclaredMethod(MethodName, int.class);
} else {
method = o.getClass().getDeclaredMethod(MethodName, c);
}
try {
method.invoke(o, paras);// 调用o对象的方法
} catch (IllegalAccessException ex) {
ex.fillInStackTrace();
} catch (IllegalArgumentException ex) {
ex.fillInStackTrace();
} catch (InvocationTargetException ex) {
ex.fillInStackTrace();
}
} catch (NoSuchMethodException ex) {
ex.fillInStackTrace();
} catch (SecurityException ex) {
ex.fillInStackTrace();
}
}
public static void doMethod(String MethodName, Object o, Object paras) {
Class c = null;
if (paras != null) {// 存在
c = paras.getClass();
}
try {
Method method = null;
if (c.getSimpleName().equals("Integer")) {
method = o.getClass().getDeclaredMethod(MethodName, int.class);
} else {
method = o.getClass().getDeclaredMethod(MethodName, c);
}
try {
method.invoke(o, paras);// 调用o对象的方法
} catch (IllegalAccessException ex) {
ex.fillInStackTrace();
} catch (IllegalArgumentException ex) {
ex.fillInStackTrace();
} catch (InvocationTargetException ex) {
ex.fillInStackTrace();
}
} catch (NoSuchMethodException ex) {
ex.fillInStackTrace();
} catch (SecurityException ex) {
ex.fillInStackTrace();
}
}
public static void doMethod(String MethodName, Object o, String paras, Class parasType) {
try {
Method method = o.getClass().getDeclaredMethod(MethodName, parasType);
try {
if (parasType.equals(int.class)) {
method.invoke(o, Integer.parseInt(paras));
} else if (parasType.equals(Double.class)) {
method.invoke(o, Double.parseDouble(paras));
} else
method.invoke(o, paras);// 调用o对象的方法
} catch (IllegalAccessException ex) {
ex.fillInStackTrace();
} catch (IllegalArgumentException ex) {
ex.fillInStackTrace();
} catch (InvocationTargetException ex) {
ex.fillInStackTrace();
}
} catch (NoSuchMethodException ex) {
ex.fillInStackTrace();
} catch (SecurityException ex) {
ex.fillInStackTrace();
}
}
}
引用
1: 使用JSONTFORMAT 将json生成对应格式的bean.class
2: 在MyApplication中初始化表。
DBHelper.ClassList.add(DiseaseTable.class);
DBHelper.ClassList.add(FarmerTable.class);
DBHelper.ClassList.add(FeedTable.class);
DBHelper.ClassList.add(FeedDisplayTable.class);
DBHelper.ClassList.add(BaseFarmer.class);
DBHelper.ClassList.add(Medicine.class); //药品
3:在接口中存储/读取表数据：
public void success(JSONArray resultArray) {
if (resultArray.length() > 0) {
DataBaseUtil.clearTable(AreaMagrActivity.this, FeedTable.class);
DataBaseUtil.saveJsonArrayData(AreaMagrActivity.this, FeedTable.class, resultArray);
cancleDialog();
}
}
