package com.iplatform.gather.util;
|
|
import com.walker.connector.Address;
|
import com.walker.connector.support.DatabaseConnector;
|
import com.walker.infrastructure.utils.StringUtils;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
|
import java.util.List;
|
import java.util.Map;
|
|
public class StoreUtils {
|
|
/**
|
* 从目的表中查询出来要更新的数据，如果不存在说明没有可更新数据，都需要插入
|
* @param keys
|
* @param destTableName
|
* @param connector
|
* @param dataList
|
* @return
|
*/
|
public static List<Map<String, Object>> loadExistDataList(String[] keys
|
, String destTableName, DatabaseConnector connector, List<Object> dataList){
|
StringBuilder sql = new StringBuilder();
|
|
final int size = dataList.size();
|
|
if(keys.length == 1){
|
sql.append("select ");
|
sql.append(keys[0]); // 只查询主键字段，其他字段没用
|
sql.append(" from ");
|
sql.append(destTableName);
|
sql.append(" where ");
|
sql.append(keys[0]);
|
sql.append(" in(");
|
|
for(int i=0; i<size; i++){
|
if(i > 0){
|
sql.append(StringUtils.DEFAULT_SPLIT_SEPARATOR);
|
}
|
sql.append("?");
|
}
|
sql.append(")");
|
|
//拼接参数
|
Object[] params = new Object[size];
|
for(int j=0; j<size; j++){
|
params[j] = ((Map<Object, Object>)dataList.get(j)).get(keys[0]);
|
}
|
|
return connector.queryForList(sql.toString(), params);
|
|
} else if(keys.length > 1){
|
throw new UnsupportedOperationException("当前还未支持根据多主键，查询已经存在数据");
|
}
|
return null;
|
}
|
|
/**
|
* 返回更新数据SQL
|
* @param destTableName 更新表名
|
* @param key 主键名称
|
* @param data 提供的一条记录
|
* @return
|
*/
|
public static String getUpdateSQLByOneKey(String destTableName, String key, Map<String, Object> data){
|
StringBuilder sql = new StringBuilder();
|
sql.append("update ");
|
sql.append(destTableName);
|
sql.append(" set ");
|
|
int i=0;
|
for(String k : data.keySet()){
|
if(i > 0){
|
sql.append(StringUtils.DEFAULT_SPLIT_SEPARATOR);
|
}
|
sql.append(k);
|
sql.append("=?");
|
i++;
|
}
|
sql.append(" where ");
|
sql.append(key);
|
sql.append("=?");
|
return sql.toString();
|
}
|
|
/**
|
* 返回一个更新SQL的参数
|
* @param data
|
* @param idValue
|
* @return
|
*/
|
public static Object[] getUpdateParams(Map<String, Object> data, String idValue){
|
int size = data.keySet().size() + 1; // 最后要加上主键值
|
Object[] params = new Object[size];
|
int i = 0;
|
for(String k : data.keySet()){
|
params[i] = data.get(k);
|
i++;
|
}
|
params[i] = idValue;
|
return params;
|
}
|
|
public static String getInsertSQL(Address address, List<String> fields, String destTableName
|
, Map<Integer, String> cachedInsertSQL){
|
int hashCode = address.hashCode() + destTableName.hashCode();
|
String insertSql = cachedInsertSQL.get(hashCode);
|
if(insertSql == null){
|
// 分析字段，拼接insert语句
|
StringBuilder sbSQL = new StringBuilder();
|
sbSQL.append("insert into ").append(destTableName).append("(");
|
int i = 0;
|
for(String field : fields){
|
if(i > 0){
|
sbSQL.append(StringUtils.DEFAULT_SPLIT_SEPARATOR);
|
}
|
sbSQL.append(field);
|
i++;
|
}
|
sbSQL.append(") values(");
|
|
int j = 0;
|
for(String field : fields){
|
if(j > 0){
|
sbSQL.append(StringUtils.DEFAULT_SPLIT_SEPARATOR);
|
}
|
sbSQL.append(StringUtils.SEPARATOR_COLON).append(field);
|
j++;
|
}
|
sbSQL.append(")");
|
insertSql = sbSQL.toString();
|
cachedInsertSQL.put(hashCode, insertSql);
|
}
|
return insertSql;
|
}
|
|
public static final MapSqlParameterSource createMapParameter(List<String> fields, Map<String, Object> map){
|
MapSqlParameterSource sps = new MapSqlParameterSource();
|
// logger.debug("+++++++++++ " + json);
|
for(String field : fields){
|
sps.addValue(field, map.get(field));
|
// logger.debug("添加参数 '" + field + "', value = " + json.get(field));
|
}
|
return sps;
|
}
|
}
|
