/**
 * 數(shù)據(jù)庫表操作類
 *
 * @author: NPF
 * @date: 2018/2/26.
 */
public class DBTableOperate {
private final String TAG = "nan";
private static DBTableOperate mInstance;

private DBTableOperate() {
}

/**
 * 單例模式獲取實(shí)例
 */
public static DBTableOperate getInstance() {
    if (null == mInstance) {
        synchronized (DBTableOperate.class) {
            if (null == mInstance) {
                mInstance = new DBTableOperate();
            }
        }
    }
    return mInstance;
}

/**
 * 表中添加列
 * ALTER TABLE 表名 ADD COLUMN 列名 數(shù)據(jù)類型
 *
 * @param db
 * @param tableName    表名
 * @param columnName   列名
 * @param columnType   列類型
 * @param defaultField 列默認(rèn)值
 */
public synchronized void columnAdd(SQLiteDatabase db, String tableName,
                                   String columnName, String columnType,
                                   Object defaultField) {
    try {
        if (null != db) {
            //查詢第一條記錄
            Cursor c = db.rawQuery(String.format(Locale.CHINESE, "SELECT * FROM %s LIMIT 1 ", tableName), null);
            boolean flag = true;
            if (c != null) {
                //遍歷檢索是否已經(jīng)存在該列
                for (int i = 0; i < c.getColumnCount(); i++) {
                    if (columnName.equalsIgnoreCase(c.getColumnName(i))) {
                        flag = false;
                        break;
                    }
                }
                if (flag) {//插入列
                    db.execSQL(String.format(Locale.CHINESE, "ALTER TABLE %s ADD COLUMN %s %s default %s", tableName, columnName, columnType, defaultField));
                }
                c.close();
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        Log.e(TAG, String.format(Locale.CHINESE, "表：%s 中 %s 列添加失敗==%s", tableName, columnName, e.getMessage()));
    }
}


/**
 * 更新表名稱
 * ALTER TABLE 舊表名 RENAME TO 新表名
 *
 * @param db
 * @param oldTableName
 * @param newTableName
 */
public synchronized void tableRename(SQLiteDatabase db, String oldTableName, String newTableName) {
    try {
        db.execSQL(String.format(Locale.CHINESE, "ALTER TABLE %s RENAME TO %s", oldTableName, newTableName));
    } catch (Exception e) {
        e.printStackTrace();
        Log.e(TAG, String.format(Locale.CHINESE, "表：%s 更新名稱為 %s 失敗==%s", oldTableName, newTableName, e.getMessage())
        );
    }
}

/**
 * 刪除表
 * DROP TABLE 表名;
 *
 * @param db
 * @param tableName
 */
public synchronized void deleteTable(SQLiteDatabase db, String tableName) {
    try {
        db.execSQL(String.format(Locale.CHINESE, "DROP TABLE %s", tableName));
    } catch (Exception e) {
        e.printStackTrace();
        Log.e(TAG, String.format(Locale.CHINESE, "表：%s 刪除失敗==%s", tableName, e.getMessage())
        );
    }
}


/**
 * 查詢表結(jié)構(gòu)
 * PRAGMA TABLE_INFO (表名)
 *
 * @param db
 * @param tableName
 */
public synchronized void tablePragma(SQLiteDatabase db, String tableName) {
    try {
        db.execSQL(String.format(Locale.CHINESE, "PRAGMA TABLE_INFO (%s)", tableName));
    } catch (Exception e) {
        e.printStackTrace();
        Log.e(TAG, String.format(Locale.CHINESE, "表：%s 結(jié)構(gòu)查詢失敗==%s", tableName, e.getMessage())
        );
    }
}

/**
 * 刪除表中指定列(結(jié)合ORMLite使用)
 *
 * @param db
 * @param connectionSource
 * @param tableName        表名
 * @param classz           表映射實(shí)體
 * @param retain           需要保留的列字段
 */
public synchronized void columnDelete(SQLiteDatabase db, ConnectionSource connectionSource, String tableName, Class classz, String... retain) {
    try {
        String tableNameSuffix = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss").format(new Date(System.currentTimeMillis()));
        String tempTableName = String.format(Locale.CHINESE, "%s_%s", tableName, tableNameSuffix);
        tableRename(db, tableName, tempTableName);
        TableUtils.createTableIfNotExists(connectionSource, classz);
        StringBuffer buffer = new StringBuffer();
        for (String filed : retain) {
            buffer.append(filed + ",");
        }
        String fileds = buffer.substring(0, buffer.length() - 1);
        db.execSQL(String.format(Locale.CHINESE, "INSERT INTO %s (%s) SELECT %s FROM %s", tableName, fileds, fileds, tempTableName));
        deleteTable(db, tempTableName);
    } catch (Exception e) {
        e.printStackTrace();
        Log.e("nan", "刪除表中指定列失敗====" + e.getMessage());
    }
}}