/**
* 取得数据库的表信息
*
* @return
*/
public ArrayList<DBMasterEntity> getTables() {
ArrayList<DBMasterEntity> tadbMasterArrayList = new ArrayList<DBMasterEntity>();
String sql = "select * from sqlite_master where type='table' order by name";
CHLogger.i(SQLiteDB.this, sql);
if (testSQLiteDatabase()) {
if (sql != null && !sql.equalsIgnoreCase("")) {
this.queryStr = sql;
free();
queryCursor =
mSQLiteDatabase.rawQuery(
"select * from sqlite_master where type='table' order by name", null);
if (queryCursor != null) {
while (queryCursor.moveToNext()) {
if (queryCursor != null && queryCursor.getColumnCount() > 0) {
DBMasterEntity tadbMasterEntity = new DBMasterEntity();
tadbMasterEntity.setType(queryCursor.getString(0));
tadbMasterEntity.setName(queryCursor.getString(1));
tadbMasterEntity.setTbl_name(queryCursor.getString(2));
tadbMasterEntity.setRootpage(queryCursor.getInt(3));
tadbMasterEntity.setSql(queryCursor.getString(4));
tadbMasterArrayList.add(tadbMasterEntity);
}
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
}
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
}
return tadbMasterArrayList;
}
/**
* 判断是否存在某个表,为true则存在，否则不存在
*
* @param tableName 需要判断的表名
* @return true则存在，否则不存在
*/
public boolean hasTable(String tableName) {
if (tableName != null && !tableName.equalsIgnoreCase("")) {
if (testSQLiteDatabase()) {
tableName = tableName.trim();
String sql =
"select count(*) as c from Sqlite_master where type ='table' and name ='"
+ tableName
+ "' ";
if (sql != null && !sql.equalsIgnoreCase("")) {
this.queryStr = sql;
}
free();
queryCursor = mSQLiteDatabase.rawQuery(sql, null);
if (queryCursor.moveToNext()) {
int count = queryCursor.getInt(0);
if (count > 0) {
return true;
}
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
}
} else {
CHLogger.e(SQLiteDB.this, "判断数据表名不能为空！");
}
return false;
}
/**
* 查询记录
*
* @param cursorFactory
* @param distinct 限制重复，如过为true则限制,false则不用管
* @param table 表名
* @param columns 需要查询的列
* @param selection 格式化的作为 SQL WHERE子句(不含WHERE本身)。 传递null返回给定表的所有行。
* @param selectionArgs
* @param groupBy groupBy语句
* @param having having语句
* @param orderBy orderBy语句
* @param limit limit语句
* @return
*/
public ArrayList<CHHashMap<String>> queryWithFactory(
CursorFactory cursorFactory,
boolean distinct,
String table,
String[] columns,
String selection,
String[] selectionArgs,
String groupBy,
String having,
String orderBy,
String limit) {
if (testSQLiteDatabase()) {
free();
this.queryCursor =
mSQLiteDatabase.queryWithFactory(
cursorFactory,
distinct,
table,
columns,
selection,
selectionArgs,
groupBy,
having,
orderBy,
limit);
if (queryCursor != null) {
return getQueryCursorData();
} else {
CHLogger.e(SQLiteDB.this, "查询" + table + "错误");
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
}
return null;
}
/**
* 数据库错误信息 并显示当前的SQL语句
*
* @return
*/
public String error() {
if (this.queryStr != null && !queryStr.equalsIgnoreCase("")) {
error = error + "\n [ SQL语句 ] : " + queryStr;
}
CHLogger.e(SQLiteDB.this, error);
return error;
}
/**
* 插入记录
*
* @param table 需要插入到的表
* @param nullColumnHack 不允许为空的行
* @param values 插入的值
* @return
*/
public boolean insertOrThrow(String table, String nullColumnHack, ContentValues values) {
if (testSQLiteDatabase()) {
return mSQLiteDatabase.insertOrThrow(table, nullColumnHack, values) > 0;
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
return false;
}
}
/**
* 更新记录
*
* @param table 表名字
* @param values
* @param whereClause
* @param whereArgs
* @return 返回true执行成功，否则执行失败
*/
public boolean update(
String table, ContentValues values, String whereClause, String[] whereArgs) {
if (testSQLiteDatabase()) {
return mSQLiteDatabase.update(table, values, whereClause, whereArgs) > 0;
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
return false;
}
}
/**
* 删除记录
*
* @param table 被删除的表名
* @param whereClause 设置的WHERE子句时，删除指定的数据 ,如果null会删除所有的行。
* @param whereArgs
* @return 返回true执行成功，否则执行失败
*/
public boolean delete(String table, String whereClause, String[] whereArgs) {
if (testSQLiteDatabase()) {
return mSQLiteDatabase.delete(table, whereClause, whereArgs) > 0;
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
return false;
}
}
/**
* 获得所有的查询数据集中的数据
*
* @return
*/
public MapArrayList<String> getQueryCursorData() {
MapArrayList<String> arrayList = null;
if (queryCursor != null) {
try {
arrayList = new MapArrayList<String>();
queryCursor.moveToFirst();
while (queryCursor.moveToNext()) {
arrayList.add(DBUtils.getRowData(queryCursor));
}
} catch (Exception e) {
e.printStackTrace();
CHLogger.e(SQLiteDB.this, "当前数据集获取失败！");
}
} else {
CHLogger.e(SQLiteDB.this, "当前数据集不存在！");
}
return arrayList;
}
/**
* 执行查询，主要是SELECT, SHOW 等指令 返回数据集
*
* @param sql sql语句
* @param selectionArgs
* @return
*/
public ArrayList<CHHashMap<String>> query(String sql, String[] selectionArgs) {
CHLogger.i(SQLiteDB.this, sql);
if (testSQLiteDatabase()) {
if (sql != null && !sql.equalsIgnoreCase("")) {
this.queryStr = sql;
}
free();
this.queryCursor = mSQLiteDatabase.rawQuery(sql, selectionArgs);
if (queryCursor != null) {
return getQueryCursorData();
} else {
CHLogger.e(SQLiteDB.this, "执行" + sql + "错误");
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
}
return null;
}
/**
* 执行查询，主要是SELECT, SHOW 等指令 返回数据集
*
* @param clazz
* @param distinct 限制重复，如过为true则限制,false则不用管
* @param where where语句
* @param groupBy groupBy语句
* @param having having语句
* @param orderBy orderBy语句
* @param limit limit语句
* @return
*/
@SuppressWarnings("unchecked")
public <T> List<T> query(
Class<?> clazz,
boolean distinct,
String where,
String groupBy,
String having,
String orderBy,
String limit) {
if (testSQLiteDatabase()) {
List<T> list = null;
SqlBuilder getSqlBuilder =
SqlBuilderFactory.getInstance().getSqlBuilder(SqlBuilderFactory.SELECT);
getSqlBuilder.setClazz(clazz);
getSqlBuilder.setCondition(distinct, where, groupBy, having, orderBy, limit);
try {
String sqlString = getSqlBuilder.getSqlStatement();
CHLogger.i(SQLiteDB.this, "执行" + sqlString);
free();
this.queryCursor = mSQLiteDatabase.rawQuery(sqlString, null);
list = (List<T>) DBUtils.getListEntity(clazz, this.queryCursor);
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
CHLogger.e(SQLiteDB.this, e.getMessage());
e.printStackTrace();
} catch (CHDBException e) {
// TODO Auto-generated catch block
CHLogger.e(SQLiteDB.this, e.getMessage());
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
CHLogger.e(SQLiteDB.this, e.getMessage());
e.printStackTrace();
}
return list;
} else {
return null;
}
}
/**
* 删除表
*
* @param tableName
* @return 为true创建成功，为false创建失败
*/
public boolean dropTable(String tableName) {
boolean isSuccess = false;
if (tableName != null && !tableName.equalsIgnoreCase("")) {
if (testSQLiteDatabase()) {
try {
String sqlString = "DROP TABLE " + tableName;
execute(sqlString, null);
isSuccess = true;
} catch (Exception e) {
// TODO Auto-generated catch block
isSuccess = false;
e.printStackTrace();
CHLogger.e(SQLiteDB.this, e.getMessage());
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
return false;
}
} else {
CHLogger.e(SQLiteDB.this, "删除数据表名不能为空！");
}
return isSuccess;
}
/**
* 创建表
*
* @param clazz
* @return 为true创建成功，为false创建失败
*/
public boolean creatTable(Class<?> clazz) {
boolean isSuccess = false;
if (testSQLiteDatabase()) {
try {
String sqlString = DBUtils.creatTableSql(clazz);
execute(sqlString, null);
isSuccess = true;
} catch (CHDBException e) {
// TODO Auto-generated catch block
isSuccess = false;
e.printStackTrace();
CHLogger.e(SQLiteDB.this, e.getMessage());
} catch (CHDBNotOpenException e) {
// TODO Auto-generated catch block
isSuccess = false;
e.printStackTrace();
CHLogger.e(SQLiteDB.this, e.getMessage());
}
} else {
CHLogger.e(SQLiteDB.this, "数据库未打开！");
return false;
}
return isSuccess;
}
/**
* INSERT, UPDATE 以及DELETE
*
* @param sql 语句
* @param bindArgs
* @throws CHDBNotOpenException
*/
public void execute(String sql, String[] bindArgs) throws CHDBNotOpenException {
CHLogger.i(SQLiteDB.this, "准备执行SQL[" + sql + "]语句");
if (testSQLiteDatabase()) {
if (sql != null && !sql.equalsIgnoreCase("")) {
this.queryStr = sql;
if (bindArgs != null) {
mSQLiteDatabase.execSQL(sql, bindArgs);
} else {
mSQLiteDatabase.execSQL(sql);
}
}
} else {
throw new CHDBNotOpenException("数据库未打开！");
}
}