SQLiteHelper

public final class SQLiteHelper {


private static final String TAG = "SQLiteHelper";


private final static ReadWriteLock rwl = new ReentrantReadWriteLock();


public static SQLiteDatabase getReadableDatabase() {
SQLiteDatabase db = null;
try {
rwl.readLock().lock();
db = SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME, null, SQLiteDatabase.OPEN_READONLY);
} catch (Exception ex) {
Logger.printStackTrace(TAG, ex);
} finally {
rwl.readLock().unlock();
}
return db;
}


public static SQLiteDatabase getWritableDatabase() {
SQLiteDatabase db = null;
try {
rwl.writeLock().lock();
db = SQLiteDatabase.openDatabase(DATABASE_PATH + DATABASE_NAME, null, SQLiteDatabase.OPEN_READWRITE);
} catch (Exception ex) {
Logger.printStackTrace(TAG, ex);
} finally {
rwl.writeLock().unlock();
}
return db;
}


// "/data/data/com.ihandy.sxtbyb/databases/"


// “/sdcard/”


private static String DATABASE_PATH = Environment.getExternalStorageDirectory().getAbsolutePath() + "/";


private static String DATABASE_NAME = "sxtbybuat.sqlite";


/**
* 初始化数据库
* 
* @param context
*/
public synchronized static void init(final Context ctx) {
// 输出路径
String outFileName = DATABASE_PATH + DATABASE_NAME;
// 检测是否已经创建
// xgx.sqlite-journal 事务失败生成的文件
//这个文件是用来当数据库操作被异常终止的时候，恢复数据库使之保持完整，而不会被损坏。所以最好不要把它手工删除掉。因为这样很可能会使数据库处于一个不一致的状态。
File sdcardDir = new File(DATABASE_PATH);
final File[] mustRemoveFiles = sdcardDir.listFiles(new FilenameFilter() {
@Override
public boolean accept(File dir, String filename) {
return filename.endsWith("-journal");
}
});


if (mustRemoveFiles != null && mustRemoveFiles.length > 0) {
for (File file : mustRemoveFiles) {
file.delete();
}
}


File databaseFile = new File(outFileName);
if (!databaseFile.exists()) {
InputStream input = null;
OutputStream output = null;


try {
// 从资源中读取数据库流
input = ctx.getAssets().open(DATABASE_NAME);


output = new FileOutputStream(outFileName);


// 拷贝到输出流
byte[] buffer = new byte[2048];
int length;
while ((length = input.read(buffer)) > 0) {
output.write(buffer, 0, length);
}
} catch (FileNotFoundException e) {
Logger.printStackTrace(TAG, e);
} catch (IOException e) {
Logger.printStackTrace(TAG, e);
} finally {
// 关闭输出流
try {
if (output != null) {
output.flush();
output.close();
}
if (input != null) {
input.close();
}
} catch (IOException e) {
}

}
}
}


/**
* 为Spinner提供数据
* 
* @param sql
* @param addCount
* @param selectionArgs 
* @return
*/
public static ArrayList<LabelValueBean> getSpinner(String sql, String... whereArgs) {
filterWhereArgs(whereArgs);
SQLiteDatabase db = null;
Cursor cursor = null;
ArrayList<LabelValueBean> dropList = null;
try {
db = getReadableDatabase();
if (db != null) {
cursor = db.rawQuery(sql, whereArgs);
if (cursor != null) {
/**
* new ArrayList<>(23);
* 根据给定的大小构造出一个ArrayList对象
* Constructs a new instance of ArrayList with the specified initial capacity.容量
*/
dropList = new ArrayList<LabelValueBean>(cursor.getCount());
while (cursor.moveToNext()) {
dropList.add(new LabelValueBean(cursor.getString(0), cursor.getString(1)));
}
}
}
} finally {
closeDb(db, cursor);
if (dropList == null) {
dropList = new ArrayList<LabelValueBean>(0);
}
}
return dropList;
}


/**
* 更新数据库
* 
* @param table
* @param values
* @param whereClause
* @param whereArgs
* @return
*/
public static boolean update(String table, ContentValues values, String whereClause, String... whereArgs) {
filterWhereArgs(whereArgs);
SQLiteDatabase db = getWritableDatabase();
int affectedRows = 0;
if (db != null) {
affectedRows = db.update(table, values, whereClause, whereArgs);
}
closeDb(db, null);
return affectedRows > 0;
}


/**
* 更新数据库
* 
* @param table
* @param values
* @return
*/
public static long insert(String table, ContentValues values) {
long result = 0L;
SQLiteDatabase db = null;;
try {
db=getWritableDatabase();
if (db != null) {
result = db.insert(table, null, values);
}
} catch (Exception e) {
Log.e("System.out", e.getMessage());
}finally{
closeDb(db, null);
}

return result;
}


/**
* 插入或更新
* 
* @param table
* @param values
* @param whereClause
* @param whereArgs
* @return
*/
public static boolean insertOrUpdate(String table, ContentValues values, String whereClause, String... whereArgs) {
filterWhereArgs(whereArgs);
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getReadableDatabase();
if (db != null) {
cursor = db.query(table, null, whereClause, whereArgs, null, null, null);
if (cursor != null) {
int count = cursor.getCount();
if (count > 0) {
return update(table, values, whereClause, whereArgs);
} else {
return insert(table, values) > 0L;
}
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
closeDb(db, cursor);
}
return false;
}


/**
* 删除数据库
* 
* @param table
* @param whereClause
* @param whereArgs
* @return
*/
public static boolean delete(String table, String whereClause, String... whereArgs) {
boolean result = true;
SQLiteDatabase db = null;
try {
db = getWritableDatabase();
if (db != null) {
filterWhereArgs(whereArgs);
result = db.delete(table, whereClause, whereArgs) > 0;
}
} catch (SQLException e) {
result = false;
Logger.printStackTrace(TAG, e);
} catch (Exception e) {
result = false;
Logger.printStackTrace(TAG, e);
} finally {
if (db != null) {
db.close();
}
}
return result;
}


/**
* 查询
* 
* @param sql
* @param whereArgs
* @return
*/
public static String exeScalar(String sql, String... whereArgs) {
String uniqueResult = "";
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getReadableDatabase();
if (db != null) {
filterWhereArgs(whereArgs);
cursor = db.rawQuery(sql, whereArgs);
if (cursor != null && cursor.moveToNext()) {
uniqueResult = cursor.getString(0);
}
}
} finally {
closeDb(db, cursor);
}
return StringUtils.trimToEmpty(uniqueResult);
}


/**
* 获取一行记录前N个字段值
* 
* @param columnCount
* @param sql
* @param whereArgs
* @return
*/
public static String[] exeScalar(int columnCount, String sql, String... whereArgs) {
String[] result = new String[columnCount];
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getWritableDatabase();
if (db != null) {
filterWhereArgs(whereArgs);
cursor = db.rawQuery(sql, whereArgs);
if (cursor != null && cursor.moveToNext()) {
int dbCC = cursor.getColumnCount();
if (columnCount > dbCC) {
columnCount = dbCC;
}
int index = 0;
while (columnCount > index) {
result[index] = cursor.getString(index);
index++;
}
}
}
} finally {
closeDb(db, cursor);
}
return result;
}


/**
* 过来whereArgs中为null的数据项
* 
* @param whereArgs
*/
private static void filterWhereArgs(String... whereArgs) {
if (whereArgs != null && whereArgs.length > 0) {
for (int i = 0, j = whereArgs.length; i < j; i++) {
if (whereArgs[i] == null) {
whereArgs[i] = "";
}
}
}
}


/**
* 释放数据库资源
**/
public static void closeDb(SQLiteDatabase db, Cursor cursor) {
if (cursor != null) {
cursor.close();
}
if (db != null) {
db.close();
}
}


/**
* 获得最后一次更新资源的时间
*/
public static String getLastUpdateResourceDateTime() {
String lastUpdateResourceTime = "";
SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getReadableDatabase();
if (db != null) {
final String sql = "select * from data_base_version";
cursor = db.rawQuery(sql, null);
final int colIndex  = cursor.getColumnIndex("resource_last_update_time");
if (cursor != null && cursor.moveToFirst() && colIndex > -1) {
lastUpdateResourceTime = cursor.getString(colIndex);
}
}
} catch (Exception e) {
Logger.printStackTrace(TAG, e);
} finally {
closeDb(db, cursor);
}
return lastUpdateResourceTime;
}

/***获得所有的资源列表*/
public static List<String> findAllResourceNamesList() {
List<String> allImgNameList = null;
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try{
if(db!=null) {
allImgNameList = new ArrayList<String>(120);
cursor = db.rawQuery("select resource_name from resource",null);
if(cursor!=null) {
while(cursor.moveToNext()) {
allImgNameList.add(cursor.getString(0));
}
}
}
}finally {
closeDb(db, cursor);
}

if(allImgNameList==null) {
allImgNameList= new ArrayList<String>(0);
}
return  allImgNameList;
}


/**
* 更新最后一次 资源的更新时间
*/
public static void updateLastUpdateResourceDateTime(String serverDateTime) {
SQLiteDatabase db = null;
// 开启事务
try {
db = getWritableDatabase();
if (db != null) {
// 更新客户端数据库版本号
db.execSQL("update data_base_version set resource_last_update_time = ?",
new String[] { serverDateTime });
}
} catch (Exception e) {
Logger.printStackTrace(TAG, e);
} finally {
if (db != null) {
db.close();
}
}
}

/***获得所有的资源Map<文件名,文件的大小>*/
public static Map<String,Long> findAllResourceNamesSizePairMap() {
Map<String,Long> allImgNameMap = null;
SQLiteDatabase db = getReadableDatabase();
Cursor cursor = null;
try{
if(db!=null) {
allImgNameMap = new HashMap<String,Long>(130);
cursor = db.rawQuery("select resource_name,res_size from resource",null);
if(cursor!=null) {
while(cursor.moveToNext()) {
allImgNameMap.put(cursor.getString(0),cursor.getLong(1));
}
}
}
}finally {
closeDb(db, cursor);
}

if(allImgNameMap==null) {
allImgNameMap= new HashMap<String,Long>(0);
}
return  allImgNameMap;
}

/***获得已经成功下载了的   “需要修改的资源”
* @return
*/
public static Map<Integer,List<String>> findSuccessDownloadedModifyResource() {
//Map<修改的资源的版本号,已经成功下载了 此次需要修改的图片>
Map<Integer,List<String>>  downloadedResourceMap = new HashMap<Integer,List<String>>();

SQLiteDatabase db = null;
Cursor cursor = null;
try {
db = getReadableDatabase();
if(db!=null) {
cursor = db.rawQuery("select version,resource_name from resource_modify_version", null);
if(cursor!=null) {

while(cursor.moveToNext()) {
final int modifyResourceVersion = cursor.getInt(0);
final String resourceName = cursor.getString(1);

List<String> resourceList = downloadedResourceMap.get(modifyResourceVersion);
if(resourceList==null) {
resourceList = new ArrayList<String>();
downloadedResourceMap.put(modifyResourceVersion, resourceList);
}
resourceList.add(resourceName);
}
}
}
  }catch (Exception ex) {
Logger.printStackTrace(TAG,ex);
}finally {
closeDb(db,cursor);
}
return downloadedResourceMap;
}
 
/** 添加    已经成功下载了的“修改资源”**/
public static void addSuccessDownloadedModifyResource(final ModifiedResource modifyResource){
if(modifyResource!=null && modifyResource.modifyVersions!=null && !StringUtils.isEmpty(modifyResource.resourceName)) {
SQLiteDatabase db = null; 
try{
db = getWritableDatabase();
if(db!=null) {
db.beginTransaction();
final String sql = "insert into resource_modify_version(version,resource_name) values (?,?)";

for(Integer version: modifyResource.modifyVersions) {
db.execSQL(sql,new Object[]{version,modifyResource.resourceName});
}
db.setTransactionSuccessful();
}
}catch(Exception ex) {
Logger.printStackTrace(TAG,ex);
}finally {
if(db!=null) { 
db.endTransaction();
closeDb(db,null);
}
}
}
}

/**
* 获取当前客户端数据库版本
**/
public static int getCurrentDatabaseVersion() {
int version = 1;
SQLiteDatabase db = null;
Cursor cursor = null;
try {
String sql = "select * from data_base_version";
db = getReadableDatabase();
if (db != null) {
cursor = db.rawQuery(sql, null);
int culIndex = cursor.getColumnIndex("version_num");
if (cursor != null && cursor.moveToFirst()) {
version = cursor.getInt(culIndex);
}
}
} catch (Exception e) {
Logger.printStackTrace(TAG, e);
} finally {
SQLiteHelper.closeDb(db, cursor);
}
return version;
}


/*
* 更新数据库版本
*/
public static boolean updateDatabaseVersion(DatabaseVersion dv) {
SQLiteDatabase db = null;
// 执行更新语句
if (StringUtils.isEmpty(dv.getSql())) {
return false;
}


boolean result = true;
// 开启事务
try {
db = getWritableDatabase();
if (db != null) {
db.beginTransaction();
String[] sqls = dv.getSql().split(";");
for (String execSql : sqls) { 
execSql = execSql.replace("\n", " ").trim();
if (execSql.equals("")) {
continue;
}
db.execSQL(execSql);
}
// 更新客户端数据库版本号
db.execSQL("update data_base_version set version_num = ?", new Object[] {dv.getVersionNum() });
db.setTransactionSuccessful();
}
} catch (Exception e) {
Logger.printStackTrace(TAG, e);
} finally {
if (db != null) {
db.endTransaction();
db.close();
}
}
return result;
}
// Begin REQLOP-359 add 神行太保系统登陆版本控制需求  严程 2012-07-11
/**
* 删除数据库
*/
public static void deleteDatabaseName() {
String databaseName = DATABASE_PATH + DATABASE_NAME;
File sdcardDir = new File(DATABASE_PATH);
final File[] mustRemoveFiles = sdcardDir.listFiles(new FilenameFilter() {
@Override
public boolean accept(File dir, String filename) {
return filename.endsWith("-journal");
}
});
if (mustRemoveFiles != null && mustRemoveFiles.length > 0) {
for (File file : mustRemoveFiles) {
file.delete();
}
}
File databaseFile = new File(databaseName);
if (databaseFile.exists()) {
databaseFile.delete();
}
}
// End REQLOP-359
}