package com.basic.security.manager;
|
|
import android.database.Cursor;
|
import android.database.SQLException;
|
import android.text.TextUtils;
|
import android.view.View;
|
|
import com.basic.security.manager.erlang.ErBaseManger;
|
import com.basic.security.manager.es.EsBaseManager;
|
import com.basic.security.model.ModelAdapter;
|
import com.basic.security.model.Person;
|
import com.basic.security.utils.Base64Util;
|
import com.basic.security.utils.Constants;
|
import com.basic.security.utils.FaceId;
|
|
import java.util.ArrayList;
|
import java.util.Arrays;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.UUID;
|
|
/**
|
* sllite数据库操作的公共基础类
|
*/
|
|
public class BaseManager extends ErBaseManger {
|
|
public static Map<String, List<String>> tableColumnNames = new HashMap<>();
|
|
public static List<String> syncTableNames = Constants.syncToErlang ? Arrays.asList() : new ArrayList<>();
|
|
public static void save(ModelAdapter modelAdapter) {
|
try {
|
if (modelAdapter != null) {
|
GetInsertOrUpdateSql getInsertOrUpdateSql = new GetInsertOrUpdateSql(modelAdapter).invoke();
|
String table = getInsertOrUpdateSql.getTable();
|
String insertSql = getInsertOrUpdateSql.getInsertSql();
|
|
if (syncTableNames.contains(table)) {
|
if (table.equals("person")) {
|
/**
|
* all_device为 1 的同步到集群
|
*/
|
if ("1".equals(modelAdapter.getString("all_device"))) {
|
ErBaseManger.executeSqlInCluster(insertSql);
|
}
|
} else {
|
ErBaseManger.executeSqlInCluster(insertSql);
|
}
|
}
|
// if (modelAdapter.getBlob("camera_image_feature") != null) {
|
// FaceId.instance.addFeatureToDb(modelAdapter.getId(), modelAdapter.getBlob("camera_image_feature"));
|
// }
|
if (Constants.printSql) {
|
System.out.println("insertsql=" + insertSql);
|
}
|
DatabaseManager.execSQL(insertSql);
|
if (table.equals("visit")) {
|
EsBaseManager esBaseManager = new EsBaseManager();
|
esBaseManager.queryAndSend(modelAdapter.getString("visit_time"));
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
public static void delete(ModelAdapter modelAdapter) { // 假删除
|
if (1 == 1) {
|
deletePhysically(modelAdapter);
|
} else {
|
try {
|
if (modelAdapter != null) {
|
String table = modelAdapter.getString("table");
|
String id = modelAdapter.getString("id");
|
String del_flag = "1";
|
if (syncTableNames.contains(table)) {
|
id = Base64Util.encodeToString(table, "id", id);
|
del_flag = Base64Util.encodeToString(table, "del_flag", del_flag);
|
}
|
String sql = "update " + table + " set del_flag='" + del_flag + "' where id='" + id + "'";
|
if (Constants.printSql) {
|
System.out.println("deletesql=" + sql);
|
}
|
if (syncTableNames.contains(table)) {
|
ErBaseManger.executeSqlInCluster(sql);
|
}
|
DatabaseManager.execSQL(sql);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
public static void deletePhysically(ModelAdapter modelAdapter) { // 真删除
|
try {
|
if (modelAdapter != null) {
|
String table = modelAdapter.getString("table");
|
String id = modelAdapter.getString("id");
|
String sql = "delete from " + table + " where id='" + id + "'";
|
|
if (Constants.printSql) {
|
System.out.println("deletesql=" + sql);
|
}
|
if (syncTableNames.contains(table)) {
|
ErBaseManger.executeSqlInCluster(sql);
|
}
|
try {
|
DatabaseManager.execSQL(sql);
|
} catch (SQLException e) {
|
System.out.println("sql=" + sql);
|
e.printStackTrace();
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
|
public static ModelAdapter cursorToModelAdapter(Cursor cursor, String table) {
|
ModelAdapter modelAdapter = new ModelAdapter();
|
String[] columnNames = cursor.getColumnNames();
|
for (String columnName : columnNames) {
|
modelAdapter.setString(columnName, cursor.getString(cursor.getColumnIndex(columnName)));
|
}
|
// if (syncTableNames.contains(table)) {
|
for (Map.Entry<String, Object> entry : modelAdapter.model.entrySet()) {
|
String key = entry.getKey();
|
String value = (String) entry.getValue();
|
if (!"camera_image_feature".equals(key)) {
|
if (!TextUtils.isEmpty(value)) {
|
try {
|
modelAdapter.setString(key, Base64Util.decodeToString(table, key, value));
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
}
|
// }
|
// modelAdapter.setString("table", table);
|
return modelAdapter;
|
}
|
|
public static ModelAdapter findById(String table, String id) {
|
Cursor cursor = null;
|
try {
|
if (id == null) {
|
return null;
|
}
|
cursor = DatabaseManager.getDatabase().rawQuery("select * from " + table + " where id = '" + id + "'", null);
|
if (cursor.moveToFirst()) {
|
return cursorToModelAdapter(cursor, table);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if (cursor != null)
|
cursor.close();
|
}
|
return null;
|
}
|
|
/**
|
* 根据时间查询某条访问记录
|
*
|
* @param table
|
* @param time
|
* @return
|
*/
|
public static ModelAdapter findByTime(String table, String time) {
|
Cursor cursor = null;
|
try {
|
cursor = DatabaseManager.getDatabase().rawQuery("select * from " + table + " where visit_time = '" + time + "'", null);
|
if (cursor.moveToFirst()) {
|
return cursorToModelAdapter(cursor, table);
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if (cursor != null)
|
cursor.close();
|
}
|
return null;
|
}
|
|
public static ModelAdapter findOne(String sql) {
|
Cursor cursor = null;
|
try {
|
if (Constants.printSql) {
|
System.out.println("findOne sql=" + sql);
|
}
|
cursor = DatabaseManager.getDatabase().rawQuery(sql, null);
|
if (cursor.moveToFirst()) {
|
return cursorToModelAdapter(cursor, getTableName(sql));
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if (cursor != null) {
|
cursor.close();
|
}
|
}
|
return null;
|
}
|
|
private static String getTableName(String sql) {
|
try {
|
return sql.toLowerCase().substring(sql.indexOf("from") + "from".length()).trim().split(" ")[0].trim();
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return "";
|
}
|
|
public static List<ModelAdapter> findList(String sql) {
|
List<ModelAdapter> modelAdapterList = new ArrayList<>();
|
Cursor cursor = null;
|
try {
|
if (Constants.printSql) {
|
System.out.println("sql=" + sql);
|
}
|
cursor = DatabaseManager.getDatabase().rawQuery(sql, null);
|
if (cursor.moveToFirst()) {
|
while (!cursor.isAfterLast()) {
|
modelAdapterList.add(cursorToModelAdapter(cursor, getTableName(sql)));
|
cursor.moveToNext();
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
if (cursor != null) {
|
cursor.close();
|
}
|
}
|
return modelAdapterList;
|
}
|
|
public static void deleteList(List<ModelAdapter> modelAdapterList) {
|
for (ModelAdapter modelAdapter : modelAdapterList) {
|
FaceId.instance.removeFeatureFromDb(modelAdapter.getString(Person.id));
|
deletePhysically(modelAdapter);
|
}
|
}
|
|
public static Map<String, String> parseSql(String sql) {
|
Map<String, String> tableKeyValueMap = new HashMap<>();
|
try {
|
String sql1 = sql;
|
String[] split = sql1.split("\\(`");
|
String[] values = split[1].split("VALUES");
|
String keyList = values[0].trim().substring(0, values[0].length() - 3);
|
String valueList = values[1].trim().substring(2, values[1].length() - 3);
|
String[] keyString = keyList.split("`,`");
|
String[] valueString = valueList.split("','");
|
if (keyString.length == valueString.length) {
|
for (int i = 0; i < keyString.length; i++) {
|
tableKeyValueMap.put(keyString[i], valueString[i]);
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
return tableKeyValueMap;
|
|
}
|
|
public static View.OnClickListener buildClickListener() {
|
return new View.OnClickListener() {
|
public void onClick(View v) {
|
|
}
|
};
|
}
|
|
public static class GetInsertOrUpdateSql {
|
private ModelAdapter modelAdapter;
|
private String table;
|
private String insertSql;
|
|
public GetInsertOrUpdateSql(ModelAdapter modelAdapter) {
|
this.modelAdapter = modelAdapter;
|
}
|
|
public String getTable() {
|
return table;
|
}
|
|
public String getInsertSql() {
|
return insertSql;
|
}
|
|
public GetInsertOrUpdateSql invoke() {
|
Map<String, Object> sqliteModel = modelAdapter.model;
|
table = (String) sqliteModel.get("table");
|
insertSql = "INSERT OR REPLACE INTO " + table + " ";
|
List<String> paramNameList = new ArrayList<>();
|
List<String> paramValueList = new ArrayList<>();
|
String id = (String) sqliteModel.get("id");
|
if (id == null) {
|
id = UUID.randomUUID().toString();
|
sqliteModel.put("id", id);
|
}
|
List<String> columnsNameList = tableColumnNames.get(table);
|
boolean hasDelFlag = false;
|
if (columnsNameList != null) {
|
for (Map.Entry<String, Object> entry : sqliteModel.entrySet()) {
|
String key = entry.getKey();
|
Object value = entry.getValue();
|
if (columnsNameList.contains(key)) {
|
if (key.contains("del_flag") && !TextUtils.isEmpty((String) value)) {
|
hasDelFlag = true;
|
}
|
paramNameList.add("`" + key + "`");
|
if (value == null) {
|
paramValueList.add("''");
|
} else {
|
if (value instanceof byte[]) {
|
paramValueList.add(ModelAdapter.getAttachmentPath(id, key, table));
|
} else {
|
StringBuffer sb = new StringBuffer();
|
sb.append("'");
|
sb.append(value);
|
sb.append("'");
|
// paramValueList.add("'" + (String) value + "'");
|
paramValueList.add(sb.toString());
|
}
|
}
|
}
|
}
|
if (!hasDelFlag) {
|
if (columnsNameList.contains("del_flag")) {
|
paramNameList.add("`del_flag`");
|
paramValueList.add("'0'");
|
}
|
}
|
if (syncTableNames.contains(table)) {
|
for (int i = 0; i < paramNameList.size(); i++) {
|
String key = paramNameList.get(i);
|
String value = paramValueList.get(i);
|
String strValue = value;
|
if (value != null && !TextUtils.isEmpty(value) && !"''".equals(strValue)) {
|
if (!"`camera_image_feature`".equals(key)) {
|
try {
|
if (strValue.startsWith("'")) {
|
strValue = strValue.substring(1, strValue.length());
|
}
|
if (strValue.endsWith("'")) {
|
strValue = strValue.substring(0, strValue.length() - 1);
|
}
|
strValue = Base64Util.encodeToString(table, key, strValue);
|
paramValueList.set(i, "'" + strValue + "'");
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
} else {
|
// System.out.println(id+"保存personFeature feature="+strValue);
|
}
|
}
|
}
|
}
|
insertSql += "(" + TextUtils.join(",", paramNameList) + ") VALUES (" +
|
TextUtils.join(",", paramValueList) + ")";
|
|
}
|
return this;
|
}
|
}
|
|
}
|
