package com.sogou.qadev.service.cynthia.dao;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.log4j.Logger;
import com.sogou.qadev.cache.impl.FieldNameCache;
import com.sogou.qadev.cache.impl.TemplateCache;
import com.sogou.qadev.service.cynthia.bean.ChangeLog;
import com.sogou.qadev.service.cynthia.bean.Data;
import com.sogou.qadev.service.cynthia.bean.Field;
import com.sogou.qadev.service.cynthia.bean.Field.DataType;
import com.sogou.qadev.service.cynthia.bean.Field.Type;
import com.sogou.qadev.service.cynthia.bean.Template;
import com.sogou.qadev.service.cynthia.bean.UUID;
import com.sogou.qadev.service.cynthia.bean.impl.DataImpl;
import com.sogou.qadev.service.cynthia.factory.DataAccessFactory;
import com.sogou.qadev.service.cynthia.service.DbPoolConnection;
import com.sogou.qadev.service.cynthia.service.TableRuleManager;
import com.sogou.qadev.service.cynthia.util.CynthiaUtil;
import com.sogou.qadev.service.cynthia.util.Date;
/**
* @description:data db processor
* @author:liming
* @mail:liming@sogou-inc.com
* @date:2014-5-6 下午5:17:30
* @version:v1.0
*/
public class DataAccessSessionMySQL {
private static Logger logger = Logger.getLogger(DataAccessSessionMySQL.class.getName());
private Map<String, String> templateFieldNameCache = null;
public DataAccessSessionMySQL(UUID templateId)
{
setTemplateFieldNameCache(templateId);
}
/**
* @description:set all template field colname cache
* @date:2014-5-6 下午5:17:45
* @version:v1.0
* @param templateId
*/
public void setTemplateFieldNameCache(UUID templateId){
templateFieldNameCache = new FieldNameAccessSessionMySQL().queryTemplateFieldMap(templateId.getValue());
}
public DataAccessSessionMySQL(){
}
/**
* @description:insert data to db
* @date:2014-5-6 下午5:18:09
* @version:v1.0
* @param fieldValueMap
* @param tableName
* @param conn
* @return
* @throws IOException
* @throws SQLException
*/
public synchronized boolean insertDataToDB(Map<String, String> fieldValueMap,String tableName,Connection conn) throws IOException, SQLException{
if (tableName == null || conn ==null) {
return false;
}
boolean result = false;
PreparedStatement pStat = null;
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("insert ignore into ").append(tableName).append(" (");
Iterator<Map.Entry<String, String>> iterator = fieldValueMap.entrySet().iterator(); //去掉空列表
while (iterator.hasNext()) {
Map.Entry<String,String> entry = iterator.next();
if (entry.getKey() == null) {
System.err.println("find it!");
}
if (entry == null || entry.getKey() == null || entry.getKey().equals("")) {
iterator.remove();
}
}
if (fieldValueMap.keySet().size() == 0) {
return false;
}
for (String fieldName : fieldValueMap.keySet()) {
sqlBuffer.append(fieldName).append(",");
}
sqlBuffer.deleteCharAt(sqlBuffer.length() -1);
sqlBuffer.append(") values(");
for (String fieldName : fieldValueMap.keySet()) {
sqlBuffer.append("?,");
}
sqlBuffer.deleteCharAt(sqlBuffer.length() -1);
sqlBuffer.append(")");
String sql = sqlBuffer.toString();
try {
pStat = conn.prepareStatement(sql);
int i = 1;
for (String fieldName : fieldValueMap.keySet()) {
if (fieldValueMap.get(fieldName) == null)
pStat.setString(i++, null);
else
pStat.setString(i++, fieldValueMap.get(fieldName));
}
return pStat.executeUpdate() > 0;
} catch (Exception e) {
String content = "------------------------------------\r\n";
if (fieldValueMap.get("dataId") != null) {
content += "id:" + fieldValueMap.get("dataId") +"\r\n";
}else {
content += "id:" + fieldValueMap.get("id") +"\r\n";
}
content += "sql:" + sql +"\r\n";
content += "error reason:" + e.getMessage() +"\r\n\r\n\r\n\r\n\r\n";
logger.error(content);
e.printStackTrace();
}finally{
DbPoolConnection.getInstance().closeStatment(pStat);
}
return result;
}
/**
* @description:updata data from db
* @date:2014-5-6 下午5:18:31
* @version:v1.0
* @param fieldValueMap:data value map
* @param tableName
* @param conn
* @return
* @throws IOException
* @throws SQLException
*/
public synchronized boolean updateDataToDB(Map<String, String> fieldValueMap,String tableName,Connection conn) throws IOException, SQLException{
PreparedStatement pStat = null;
Iterator<Map.Entry<String, String>> iterator = fieldValueMap.entrySet().iterator(); //去掉空列表
while (iterator.hasNext()) {
Map.Entry<String,String> entry = iterator.next();
if (entry.getKey().equals("")) {
iterator.remove();
}
}
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("update ").append(tableName).append(" Set ");
for (String fieldName : fieldValueMap.keySet()) {
if (fieldName.equals("id") ) {
continue;
}
sqlBuffer.append(fieldName).append(" = ").append( "?").append(" ,");
}
String sql = sqlBuffer.toString();
if (sql.endsWith(",")) {
sql = sql.substring(0,sql.length()-1);
}
sql += " where id = " + fieldValueMap.get("id");
try {
pStat = conn.prepareStatement(sql);
int i = 1;
for (String fieldName : fieldValueMap.keySet()) {
if (fieldName.equals("id") )
continue;
if (fieldValueMap.get(fieldName) == null || fieldValueMap.get(fieldName).equals(""))
pStat.setNull(i++, Types.VARCHAR);
else
pStat.setString(i++, fieldValueMap.get(fieldName));
}
return pStat.executeUpdate() > 0;
} catch (Exception e) {
e.printStackTrace();
return false;
}finally{
DbPoolConnection.getInstance().closeStatment(pStat);
}
}
/**
* @description:get if data is exist
* @date:2014-5-6 下午5:18:50
* @version:v1.0
* @param dataId
* @return
*/
public boolean isExist(UUID dataId){
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try
{
List<String> queryFieldList = new ArrayList<String>();
queryFieldList.add("id");
Map<String, String> whereFieldsMap = new HashMap<String, String>();
whereFieldsMap.put("id", dataId.getValue());
String sql = DbPoolConnection.getInstance().getDataQuerySQL(TableRuleManager.getInstance().getAllDataTables(),queryFieldList, whereFieldsMap, null,null);
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
return rs.next();
}catch(Exception e){
logger.error("", e);
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(stat);
DbPoolConnection.getInstance().closeConn(conn);
}
return false;
}
/**
* @description:get if data exist
* @date:2014-5-6 下午5:19:04
* @version:v1.0
* @param dataId
* @param templateId
* @return
*/
public boolean isExist(UUID dataId,UUID templateId)
{
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try
{
String tableName = TableRuleManager.getInstance().getDataTableName(templateId);
conn = DbPoolConnection.getInstance().getReadConnection();
pstm = conn.prepareStatement("SELECT id FROM " + tableName + " WHERE id = ? and is_valid = ?");
pstm.setLong(1, Long.parseLong(dataId.getValue()));
pstm.setString(2, "1");
rs = pstm.executeQuery();
return rs.next();
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(pstm);
DbPoolConnection.getInstance().closeConn(conn);
}
return false;
}
/**
* @description:query field values by templatetype id
* @date:2014-5-6 下午5:19:14
* @version:v1.0
* @param templateTypeId
* @param queryField
* @return
*/
public String[] queryFieldByTemplateType(UUID templateTypeId , String queryField){
if (templateTypeId == null || queryField == null) {
return new String[0];
}
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
Set<String> resultSet = new HashSet<String>();
try
{
List<String> queryFieldList = new ArrayList<String>();
queryFieldList.add(queryField);
Map<String, String> whereFieldsMap = new HashMap<String, String>();
whereFieldsMap.put("templateTypeId", templateTypeId.getValue());
List<String> tableList = TableRuleManager.getInstance().getAllDataTables();
String sql = DbPoolConnection.getInstance().getDataQuerySQL(tableList,queryFieldList, whereFieldsMap,null,null);
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while (rs.next()) {
String tmp = rs.getString(queryField);
if (tmp != null && tmp.length() > 0) {
resultSet.add(tmp);
}
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeAll(rs, stat, conn);
}
return resultSet.toArray(new String[resultSet.size()]);
}
/**
* @description:query field values by template id
* @date:2014-5-6 下午5:19:33
* @version:v1.0
* @param templateId
* @param queryField
* @return
*/
public String[] queryFieldByTemplate(UUID templateId , String queryField){
if (templateId == null || queryField == null) {
return new String[0];
}
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
Set<String> resultSet = new HashSet<String>();
try
{
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("select ").append(queryField).append(" from ")
.append(TableRuleManager.getInstance().getDataTableName(templateId)).append(" where templateId = ").append(templateId.getValue()).append(" and ").append(queryField).append(" is not null ");
if (queryField!= null && (queryField.equals("assignUser") || queryField.equals("createUser")) ) {
sqlBuffer.append(" and ").append(queryField).append("!='--请选择--'");
}
sqlBuffer.append(" and is_valid=1");
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sqlBuffer.toString());
while (rs.next()) {
String tmp = rs.getString(queryField);
if (tmp != null && tmp.length() > 0) {
resultSet.add(tmp);
}
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeAll(rs, stat, conn);
}
return resultSet.toArray(new String[resultSet.size()]);
}
/**
* @description:query data from db
* @date:2014-5-6 下午5:20:07
* @version:v1.0
* @param dataId
* @return
*/
public Data queryData(UUID dataId){
if (dataId == null) {
return null;
}
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
Data data = null;
try
{
List<String> queryFieldList = new ArrayList<String>();
Map<String, String> whereFieldsMap = new HashMap<String, String>();
whereFieldsMap.put("id", dataId.getValue());
String sql = DbPoolConnection.getInstance().getDataQuerySQL(TableRuleManager.getInstance().getAllDataTables(),queryFieldList, whereFieldsMap,null,null);
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
List<Map<String, String>> colValueMapList = DbPoolConnection.getInstance().getDataMapFromRs(rs);
if (colValueMapList.size() > 0) {
UUID templateId = DataAccessFactory.getInstance().createUUID(colValueMapList.get(0).get("templateId"));
if (templateId == null) {
return data;
}
setTemplateFieldNameCache(templateId);
List<Data> allDatas = assembleDatas(colValueMapList,true);
if (allDatas.size() > 0) {
data = allDatas.get(0);
}
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeAll(rs, stat, conn);
}
return data;
}
/**
* @description:get limit sort sql
* @date:2014-5-6 下午5:20:19
* @version:v1.0
* @param tablesList
* @param queryFieldsList
* @param whereFieldsMap
* @param orderFieldsMap
* @param groupFieldMap
* @param start
* @param limit
* @return
*/
private String getDataQuerySQL(List<String> tablesList ,List<String> queryFieldsList , Map<String, String> whereFieldsMap , Map<String, String> orderFieldsMap , Map<String, String> groupFieldMap , int start,int limit){
String sql = DbPoolConnection.getInstance().getDataQuerySQL(tablesList, queryFieldsList, whereFieldsMap, orderFieldsMap, groupFieldMap);
sql += " limit " + start + "," + limit;
return sql;
}
/**
* @description:assemble datas
* @date:2014-5-6 下午5:20:32
* @version:v1.0
* @param colValueMapList
* @param needLog
* @return
* @throws ParseException
*/
public List<Data> assembleDatas(List<Map<String, String>> colValueMapList , boolean needLog) throws ParseException{
List<Data> allDatas = new ArrayList<Data>();
Map<UUID, Template> allTemplateMap = new HashMap<UUID, Template>();
Map<UUID, Map<String, String>> templateFieldNameMap = new HashMap<UUID, Map<String,String>>();
for (Map<String, String> colValueMap : colValueMapList) {
Data data = new DataImpl();
UUID dataId = DataAccessFactory.getInstance().createUUID(colValueMap.get("id"));
UUID templateId = DataAccessFactory.getInstance().createUUID(colValueMap.get("templateId"));
Map<UUID, Object> objectMapUUID = new HashMap<UUID, Object>();
Map<String, Object> objectMapName = new HashMap<String, Object>();
data.setId(dataId);
data.setTemplateId(templateId);
data.setTemplateTypeId(DataAccessFactory.getInstance().createUUID(colValueMap.get("templateTypeId")));
data.setTitle(colValueMap.get("title"));
data.setDescription(colValueMap.get("description"));
data.setCreateUser(colValueMap.get("createUser"));
data.setCreateTime(colValueMap.get("createTime") == null ? null:Timestamp.valueOf(colValueMap.get("createTime")));
data.setLastModifyTime(colValueMap.get("lastModifyTime") == null ? null : Timestamp.valueOf(colValueMap.get("lastModifyTime")));
data.setAssignUser(colValueMap.get("assignUser"));
data.setStatusId(DataAccessFactory.getInstance().createUUID(colValueMap.get("statusId")));
if (allTemplateMap.get(templateId) == null) {
Template template = TemplateCache.getInstance().get(templateId);
if (template != null) {
allTemplateMap.put(templateId, template);
}
}
Template template = allTemplateMap.get(templateId);
for (String colName : colValueMap.keySet()) {
Field validField = null;
try {
if (colName.startsWith("field") && colValueMap.get(colName) != null && colValueMap.get(colName).length() > 0) {
String fieldIdStr = "";
if (templateFieldNameCache == null ) {
if (templateFieldNameMap.get(templateId) == null) {
Map<String, String> fieldNameMap = new FieldNameAccessSessionMySQL().queryTemplateFieldMap(templateId.getValue());
templateFieldNameMap.put(templateId, fieldNameMap);
}
fieldIdStr = templateFieldNameMap.get(templateId).get(colName);
}else {
fieldIdStr = templateFieldNameCache.get(colName);
}
UUID fieldId = DataAccessFactory.getInstance().createUUID(fieldIdStr);
validField = template.getField(fieldId);
if (validField == null) {
// System.out.println("can not find field in assembleData ,fieldIdStr :" + fieldIdStr );
continue;
}
if (validField.getType().equals(Type.t_selection))
{
if (validField.getDataType().equals(DataType.dt_single))
{
UUID dataValidId = DataAccessFactory.getInstance().createUUID(colValueMap.get(colName));
objectMapUUID.put(fieldId, dataValidId);
objectMapName.put(validField.getName(), dataValidId);
}
else if (validField.getDataType().equals(DataType.dt_multiple))
{
String [] alldatas = colValueMap.get(colName).split(",");
UUID[] dataValidIds = new UUID[alldatas.length];
for (int i = 0; i < alldatas.length; i++) {
dataValidIds[i] = DataAccessFactory.getInstance().createUUID(alldatas[i]);
}
objectMapUUID.put(fieldId, dataValidIds);
objectMapName.put(validField.getName(), dataValidIds);
}
}
else if (validField.getType().equals(Type.t_reference))
{
if (validField.getDataType().equals(DataType.dt_single))
{
UUID dataValidId = DataAccessFactory.getInstance().createUUID(colValueMap.get(colName));
objectMapUUID.put(fieldId, dataValidId);
objectMapName.put(validField.getName(), dataValidId);
}
else if (validField.getDataType().equals(DataType.dt_multiple))
{
String [] alldatas = colValueMap.get(colName).split(",");
UUID[] dataValidIds = new UUID[alldatas.length];
for (int i = 0; i < alldatas.length; i++) {
dataValidIds[i] = DataAccessFactory.getInstance().createUUID(alldatas[i]);
}
objectMapUUID.put(fieldId, dataValidIds);
objectMapName.put(validField.getName(), dataValidIds);
}
}
else if (validField.getType().equals(Type.t_input))
{
if (validField.getDataType().equals(DataType.dt_integer))
{
objectMapUUID.put(fieldId, Integer.parseInt(colValueMap.get(colName)));
objectMapName.put(validField.getName(), Integer.parseInt(colValueMap.get(colName)));
}
else if (validField.getDataType().equals(DataType.dt_double))
{
objectMapUUID.put(fieldId, Double.parseDouble(colValueMap.get(colName)));
objectMapName.put(validField.getName(), Double.parseDouble(colValueMap.get(colName)));
}
else if (validField.getDataType().equals(DataType.dt_float))
{
objectMapUUID.put(fieldId, Float.parseFloat(colValueMap.get(colName)));
objectMapName.put(validField.getName(), Float.parseFloat(colValueMap.get(colName)));
}
else if (validField.getDataType().equals(DataType.dt_long))
{
objectMapUUID.put(fieldId, Long.parseLong(colValueMap.get(colName)));
objectMapName.put(validField.getName(), Long.parseLong(colValueMap.get(colName)));
}
else if (validField.getDataType().equals(DataType.dt_string) || validField.getDataType().equals(DataType.dt_text) || validField.getDataType().equals(DataType.dt_editor))
{
objectMapUUID.put(fieldId, colValueMap.get(colName));
objectMapName.put(validField.getName(), colValueMap.get(colName));
}
else if(validField.getDataType().equals(DataType.dt_timestamp))
{
Date date = Date.valueOf(colValueMap.get(colName));
objectMapUUID.put(fieldId, date);
objectMapName.put(validField.getName(), date);
}
}
else if (validField.getType().equals(Type.t_attachment))
{
String[] alldatas = colValueMap.get(colName).split(",");
UUID[] dataValidIds = new UUID[alldatas.length];
for (int i = 0; i < alldatas.length; i++) {
dataValidIds[i] = DataAccessFactory.getInstance().createUUID(alldatas[i]);
}
objectMapUUID.put(fieldId, dataValidIds);
objectMapName.put(validField.getName(), dataValidIds);
}
}
} catch (Exception e) {
System.out.println("data assemble error! dataid:" + data.getId().getValue() + " templateid:" + data.getTemplateId().getValue() + " fieldid:" + validField.getId().getValue() );
e.printStackTrace();
}
}
data.setObjectMapUUID(objectMapUUID);
data.setObjectMapName(objectMapName);
if (needLog) {
List<ChangeLog> changeLogs = new LogAccessSessionMySQL().queryAllChangeLogs(dataId,templateId,templateFieldNameCache);
data.setChangeLogs(changeLogs);
}
allDatas.add(data);
}
return allDatas;
}
/**
* @description:query data from db
* @date:2014-5-6 下午5:20:49
* @version:v1.0
* @param dataId
* @param templateId
* @return
*/
public Data queryData(UUID dataId,UUID templateId){
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
Data data = null;
try
{
String tableName = TableRuleManager.getInstance().getDataTableName(templateId);
conn = DbPoolConnection.getInstance().getReadConnection();
pstm = conn.prepareStatement("SELECT * FROM " + tableName + " WHERE id = ? and is_valid=?");
pstm.setLong(1, Long.parseLong(dataId.getValue()));
pstm.setString(2, "1");
rs = pstm.executeQuery();
List<Map<String, String>> colValueMapList = DbPoolConnection.getInstance().getDataMapFromRs(rs);
if (colValueMapList.size() > 0) {
List<Data> allDatas = assembleDatas(colValueMapList,true);
if (allDatas.size() > 0) {
data = allDatas.get(0);
}
}
}catch(Exception e){
logger.error("",e);
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(pstm);
DbPoolConnection.getInstance().closeConn(conn);
}
return data;
}
/**
* @description:query data
* @date:2014-5-6 下午5:20:58
* @version:v1.0
* @param tablesList
* @param queryFieldsList
* @param whereFieldsMap
* @param orderFieldsMap
* @param groupFieldMap
* @param needLog
* @param templateId
* @return
*/
public List<Data> queryDatas(List<String> tablesList ,List<String> queryFieldsList , Map<String, String> whereFieldsMap , Map<String, String> orderFieldsMap , Map<String, String> groupFieldMap , boolean needLog,UUID templateId){
String sql = DbPoolConnection.getInstance().getDataQuerySQL(tablesList, queryFieldsList, whereFieldsMap, orderFieldsMap, groupFieldMap);
return queryDatas(sql, needLog,templateId);
}
/**
* @description:query template id from data id
* @date:2014-5-6 下午5:21:12
* @version:v1.0
* @param id
* @return
*/
public String queryTemplateId(UUID id){
List<String> queryFieldsList = new ArrayList<String>();
queryFieldsList.add("templateId");
Map<String, String> whereMap = new HashMap<String, String>();
whereMap.put("id", id.getValue());
String sql = DbPoolConnection.getInstance().getDataQuerySQL(TableRuleManager.getInstance().getAllDataTables(),
queryFieldsList,
whereMap,null,null
);
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
if (rs.next()) {
return rs.getString("templateId");
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(stat);
DbPoolConnection.getInstance().closeConn(conn);
}
return "";
}
/**
* @description:remove data from db
* @date:2014-5-6 下午5:22:26
* @version:v1.0
* @param ids
* @return
*/
public synchronized boolean remove(UUID[] ids)
{
Connection conn = null;
Statement stat = null;
String sql = "";
String tableName = "";
try
{
conn = DbPoolConnection.getInstance().getConnection();
stat = conn.createStatement();
for (UUID uuid : ids) {
UUID templateId = DataAccessFactory.getInstance().createUUID(queryTemplateId(uuid));
tableName = TableRuleManager.getInstance().getDataTableName(templateId);
sql = "update " + tableName + " set is_valid=0 where id = " + uuid.getValue();
stat.execute(sql);
tableName = TableRuleManager.getInstance().getDataLogTableName(templateId);
sql = "update " + tableName + " set is_valid=0 where dataId = " + uuid.getValue();
stat.execute(sql);
}
return true;
}catch(Exception e){
e.printStackTrace();
return false;
}finally
{
DbPoolConnection.getInstance().closeStatment(stat);
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:add data to db
* @date:2014-5-6 下午5:22:37
* @version:v1.0
* @param data
* @return
*/
public synchronized boolean add(Data data) {
Map<String, String> dataSaveDBMap = new LinkedHashMap<String, String>();
getDataValueMap(data, dataSaveDBMap);
Connection conn = null;
try {
conn = DbPoolConnection.getInstance().getConnection();
conn.setAutoCommit(false);
String templateId = dataSaveDBMap.get("templateId");
String tableName = TableRuleManager.getInstance().getDataTableName(DataAccessFactory.getInstance().createUUID(templateId));
boolean isSuccess = insertDataToDB(dataSaveDBMap, tableName, conn);
if (!isSuccess) {
logger.error("数据无法存储： dataid=" + data.getId().getValue());
throw new Exception("无法存储");
}
//存日志
dataSaveDBMap.put("dataId", dataSaveDBMap.get("id"));
dataSaveDBMap.remove("id");
dataSaveDBMap.put("logcreateUser", data.getObject("logCreateUser") == null?"":data.getObject("logCreateUser").toString());
dataSaveDBMap.put("logActionIndex", String.valueOf(data.getChangeLogs() == null ? 1 : data.getChangeLogs().length));
dataSaveDBMap.put("logcreateTime", dataSaveDBMap.get("lastModifyTime"));
dataSaveDBMap.put("logActionId", data.getObject("logActionId") == null?"":data.getObject("logActionId").toString());
dataSaveDBMap.put("logActionComment", data.getObject("logActionComment") == null?"":data.getObject("logActionComment").toString());
tableName = TableRuleManager.getInstance().getDataLogTableName(DataAccessFactory.getInstance().createUUID(templateId));
isSuccess = insertDataToDB(dataSaveDBMap, tableName, conn);
if (!isSuccess) {
logger.error("日志无法存储： dataid=" + data.getId().getValue());
throw new Exception("无法存储");
}
conn.commit();
conn.setAutoCommit(true);
logger.info("add a data!");
return true;
} catch (Exception e) {
try {
if (!conn.isClosed()) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
logger.error(e.getMessage());
return false;
}finally{
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:modify data from db
* @date:2014-5-6 下午5:22:48
* @version:v1.0
* @param data
* @return
*/
public synchronized boolean modify(Data data) {
Map<String, String> dataSaveDBMap = new LinkedHashMap<String, String>();
try {
getDataValueMap(data, dataSaveDBMap);
} catch (Exception e) {
e.printStackTrace();
return false;
}
Connection conn = null;
try {
conn = DbPoolConnection.getInstance().getConnection();
conn.setAutoCommit(false);
String templateId = dataSaveDBMap.get("templateId");
String tableName = TableRuleManager.getInstance().getDataTableName(DataAccessFactory.getInstance().createUUID(templateId));
boolean isSuccess = updateDataToDB(dataSaveDBMap, tableName, conn);
if (!isSuccess) {
logger.error("数据无法更数： dataid=" + data.getId().getValue());
throw new Exception("无法存储");
}
//存日志
dataSaveDBMap.put("dataId", dataSaveDBMap.get("id"));
dataSaveDBMap.remove("id");
dataSaveDBMap.put("logcreateUser", data.getObject("logCreateUser") == null?"":data.getObject("logCreateUser").toString());
dataSaveDBMap.put("logcreateTime", dataSaveDBMap.get("lastModifyTime"));
dataSaveDBMap.put("logActionIndex", String.valueOf(data.getChangeLogs() == null ? 1 : data.getChangeLogs().length));
dataSaveDBMap.put("logActionId", data.getObject("logActionId") == null? "48" :data.getObject("logActionId").toString()); //48为编辑
dataSaveDBMap.put("logActionComment", data.getObject("logActionComment") == null?"":data.getObject("logActionComment").toString());
tableName = TableRuleManager.getInstance().getDataLogTableName(DataAccessFactory.getInstance().createUUID(templateId));
isSuccess = insertDataToDB(dataSaveDBMap, tableName, conn);
if (!isSuccess) {
logger.error("日志无法存储： dataid=" + data.getId().getValue());
throw new Exception("无法存储");
}
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (Exception e) {
try {
if (!conn.isClosed()) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
logger.error("", e);
return false;
}finally{
DbPoolConnection.getInstance().closeConn(conn);
}
}
/**
* @description:get data values map
* @date:2014-5-6 下午5:22:58
* @version:v1.0
* @param data
* @param dataSaveDBMap
* @return
*/
public Map<String,String> getDataValueMap(Data data ,Map<String, String> dataSaveDBMap){
Template template = TemplateCache.getInstance().get(data.getTemplateId());
if(template == null){
return null;
}
dataSaveDBMap.put("id", data.getId().getValue());
dataSaveDBMap.put("templateId", template.getId().getValue());
dataSaveDBMap.put("createUser", data.getCreateUsername());
dataSaveDBMap.put("templateTypeId", template.getTemplateTypeId().getValue());
dataSaveDBMap.put("title", data.getTitle());
dataSaveDBMap.put("description", data.getDescription());
dataSaveDBMap.put("createTime", data.getCreateTime().toString());
dataSaveDBMap.put("lastModifyTime", data.getLastModifyTime().toString());
dataSaveDBMap.put("assignUser", data.getAssignUsername());
dataSaveDBMap.put("statusId", data.getStatusId().getValue());
Set<Field> allFields = template.getFields();
for(Field field : allFields){
if (field == null) {
continue;
}
String fieldValue = "";
Type type = field.getType();
DataType dataType = field.getDataType();
if (type == Type.t_selection) {
if (dataType == DataType.dt_single) {
fieldValue = data.getSingleSelection(field.getId()) == null ? "" : data.getSingleSelection(field.getId()).getValue();
}else {
if (data.getMultiSelection(field.getId()) != null) {
for (UUID uuid : data.getMultiSelection(field.getId())) {
if (uuid == null) {
continue;
}
fieldValue += "".equals(fieldValue) ? uuid.getValue() : "," + uuid.getValue();
}
}
}
}else if (type == Type.t_reference) {
if (dataType == DataType.dt_single) {
fieldValue = data.getSingleReference(field.getId()) == null ? "" : data.getSingleReference(field.getId()).getValue();
}else {
if (data.getMultiReference(field.getId()) != null) {
for (UUID uuid : data.getMultiReference(field.getId())) {
if (uuid == null) {
continue;
}
fieldValue += "".equals(fieldValue) ? uuid.getValue() : "," + uuid.getValue();
}
}
}
}else if (type == Type.t_attachment) {
if (data.getAttachments(field.getId()) != null) {
for (UUID uuid : data.getAttachments(field.getId())) {
if (uuid == null) {
continue;
}
fieldValue += "".equals(fieldValue) ? uuid.getValue() : "," + uuid.getValue();
}
}
}else if (type == Type.t_input) {
if (dataType.equals(DataType.dt_integer))
{
fieldValue = data.getInteger(field.getId()) == null ? null : data.getInteger(field.getId()).toString();
}else if (dataType.equals(DataType.dt_double))
{
fieldValue = data.getDouble(field.getId()) == null ? null : data.getDouble(field.getId()).toString();
}else if (dataType.equals(DataType.dt_float))
{
fieldValue = data.getFloat(field.getId()) == null ? null : data.getFloat(field.getId()).toString();
}else if (dataType.equals(DataType.dt_long))
{
fieldValue = data.getLong(field.getId()) == null ? null : data.getLong(field.getId()).toString();
}else if (dataType.equals(DataType.dt_string) || dataType.equals(DataType.dt_text) || dataType.equals(DataType.dt_editor))
{
fieldValue = data.getString(field.getId()) == null ? null : data.getString(field.getId()).toString();
}else if(dataType.equals(DataType.dt_timestamp))
{
fieldValue = data.getDate(field.getId()) == null ? null : data.getDate(field.getId()).toTimestamp().toString();
}
}
String fieldColName = FieldNameCache.getInstance().getFieldName(field.getId(),template.getId());
if (fieldColName != null && fieldColName.length() > 0) {
dataSaveDBMap.put(fieldColName, fieldValue);
}
}
return dataSaveDBMap;
}
/**
* @description:remove field datas when field removed
* @date:2014-5-6 下午5:23:14
* @version:v1.0
* @param templateId
* @param fieldId
*/
public void removeFieldData(UUID templateId, UUID fieldId) {
if (templateId == null || fieldId == null) {
throw new RuntimeException("templateID or fieldId is null!");
}
String dataTableName = TableRuleManager.getInstance().getDataTableName(templateId);
String logTableName = TableRuleManager.getInstance().getDataLogTableName(templateId);
String fieldColName = FieldNameCache.getInstance().getFieldName(fieldId ,templateId);
if (fieldColName == null) {
return;
}
String sql = "update " + dataTableName + " set " + fieldColName + "=? where templateId=?";
Connection conn = null;
PreparedStatement pstm = null;
try {
conn = DbPoolConnection.getInstance().getConnection();
conn.setAutoCommit(false);
pstm = conn.prepareStatement(sql);
pstm.setString(1, null);
pstm.setString(2, templateId.getValue());
if (!(pstm.executeUpdate() >0))
throw new Exception("数据库更新错误");
sql = "update " + logTableName + " set " + fieldColName + "=? where templateId=?";
pstm = conn.prepareStatement(sql);
pstm.setString(1, null);
pstm.setString(2, templateId.getValue());
int affectRow = pstm.executeUpdate();
conn.commit();
if (!(affectRow >0))
throw new Exception("数据库更新错误");
} catch (Exception e) {
logger.error("", e);
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
DbPoolConnection.getInstance().closeAll(pstm, conn);
}
}
/**
* @description:query is_valid=1
* @date:2014-5-6 下午5:23:52
* @version:v1.0
* @param sql
* @return
*/
private String getVaildDataSql(String sql){
if (sql == null) {
return "";
}
StringBuffer sqlBuffer = new StringBuffer();
String orderByStr = "";
if (sql.indexOf("order by") != -1) {
orderByStr = sql.substring(sql.indexOf("order by"));
sql = sql.substring(0,sql.indexOf("order by"));
}
String[] allSql = sql.split("union");
for (String sqlStr : allSql) {
sqlBuffer.append(sqlBuffer.length() > 0 ? " union " : "");
sqlBuffer.append(sqlStr).append(sqlStr.indexOf("where") != -1 ? " and " : " where ").append(" is_valid = 1 ");
}
sqlBuffer.append(" ").append(orderByStr);
return sqlBuffer.toString();
}
/**
* @description:query data by sql
* @date:2014-5-6 下午5:24:13
* @version:v1.0
* @param sql
* @param needLog
* @param templateId
* @return
*/
public List<Data> queryDatas(String sql, boolean needLog , UUID templateId) {
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
List<Data> allDatas = new ArrayList<Data>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
if (templateId != null) {
setTemplateFieldNameCache (templateId);
}
List<Map<String, String>> colValueMapList = DbPoolConnection.getInstance().getDataMapFromRs(rs);
allDatas = assembleDatas(colValueMapList , needLog);
}catch(Exception e){
logger.error("", e);
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(stat);
DbPoolConnection.getInstance().closeConn(conn);
}
return allDatas;
}
/**
* @description:query data ids from sql
* @date:2014-5-6 下午5:24:29
* @version:v1.0
* @param sql
* @return
*/
public String[] queryDataIdArray(String sql) {
sql = CynthiaUtil.cancelGroupOrder(sql);
String[] sqlArray = sql.split("union"); //每个表单独处理，避免union组合将所有表都锁定
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
List<String> allDataIdList = new ArrayList<String>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
for (String sqlStr:sqlArray) {
sqlStr += (sqlStr.indexOf("where") != -1 ? " and " : " where ") + " is_valid=1";
stat = conn.createStatement();
rs = stat.executeQuery(sqlStr);
while (rs.next()) {
allDataIdList.add(rs.getString("id"));
}
}
}catch(Exception e){
e.printStackTrace();
}finally{
DbPoolConnection.getInstance().closeAll(rs, stat, conn);
}
return allDataIdList.toArray(new String[allDataIdList.size()]);
}
/**
* @description:query id and last modify time from sql
* @date:2014-5-6 下午5:24:45
* @version:v1.0
* @param sql
* @return
*/
public Map<String, String> queryDataIdAndLastModifyTime(String sql) {
sql = CynthiaUtil.cancelGroupOrder(sql);
String[] sqlArray = sql.split("union"); //每个表单独处理，避免union组合将所有表都锁定
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
Map<String, String> idAndModifyTimeMap = new HashMap<String, String>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
for (String sqlStr:sqlArray) {
sqlStr += (sqlStr.indexOf("where") != -1 ? " and " : " where ") + " is_valid=1";
stat = conn.createStatement();
rs = stat.executeQuery(sqlStr);
while (rs.next()) {
idAndModifyTimeMap.put(rs.getString("id"), rs.getString("lastModifyTime"));
}
}
}catch(Exception e){
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeAll(rs, stat, conn);
}
return idAndModifyTimeMap;
}
/**
* @description:query data id and template id from sql
* @date:2014-5-6 下午5:24:58
* @version:v1.0
* @param sql
* @return
*/
public Map<String, String> queryDataIdAndTemplate(String sql) {
sql = CynthiaUtil.cancelGroupOrder(sql);
String[] sqlArray = sql.split("union"); //每个表单独处理，避免union组合将所有表都锁定
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
Map<String, String> idAndTemplateMap = new HashMap<String, String>();
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
for (String sqlStr:sqlArray) {
sqlStr += (sqlStr.indexOf("where") != -1 ? " and " : " where ") + " is_valid=1";
stat = conn.createStatement();
rs = stat.executeQuery(sqlStr);
while (rs.next()) {
idAndTemplateMap.put(rs.getString("id"), rs.getString("templateId"));
}
}
}catch(Exception e){
e.printStackTrace();
}finally
{
DbPoolConnection.getInstance().closeAll(rs, stat, conn);
}
return idAndTemplateMap;
}
/**
* @description:query data id and field by template
* @date:2014-5-6 下午5:25:13
* @version:v1.0
* @param templateId
* @param queryField
* @return
*/
public Map<String, String> queryIdAndFieldOfTemplate(String templateId, String queryField){
Map<String, String> fieldResultMap = new HashMap<String, String>();
if (templateId == null ) {
return fieldResultMap;
}
String tableName = TableRuleManager.getInstance().getDataTableName(DataAccessFactory.getInstance().createUUID(templateId));
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("select id ,").append(queryField).append(" from ").append(tableName).append(" where templateId=? and is_valid=?");
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
pstm = conn.prepareStatement(sqlBuffer.toString());
pstm.setString(1, templateId);
pstm.setString(2, "1");
rs = pstm.executeQuery();
while (rs.next()) {
fieldResultMap.put(rs.getString("id"), rs.getString(queryField));
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeAll(rs, pstm, conn);
}
return fieldResultMap;
}
/**
* @description:query field by data ids and template
* @date:2014-5-6 下午5:25:33
* @version:v1.0
* @param dataIdArray
* @param queryField
* @param templateId
* @return
*/
public String[] queryFieldByIds(String[] dataIdArray , String queryField , UUID templateId) {
if (dataIdArray == null || dataIdArray.length == 0) {
return new String[0];
}
Set<String> fieldResultSet = new HashSet<String>();
List<String> tableList = new ArrayList<String>();
if (templateId != null) {
tableList.add(TableRuleManager.getInstance().getDataTableName(templateId));
}else {
tableList.addAll(TableRuleManager.getInstance().getAllDataTables());
}
StringBuffer idBuffer = new StringBuffer();
idBuffer.append(" ( ");
for (String dataId : dataIdArray) {
idBuffer.append(dataId).append(",");
}
idBuffer.deleteCharAt(idBuffer.length() -1 );
idBuffer.append(")");
StringBuffer sqlBuffer = new StringBuffer();
for (String tableName : tableList) {
if (sqlBuffer.length() > 0) {
sqlBuffer.append(" union ");
}
sqlBuffer.append(" select ").append(queryField).append(" from ").append(tableName).append(" where id in ").append(idBuffer).append(" and is_valid=1");
}
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sqlBuffer.toString());
while (rs.next()) {
fieldResultSet.add(rs.getString(queryField));
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(stat);
DbPoolConnection.getInstance().closeConn(conn);
}
return fieldResultSet.toArray(new String[fieldResultSet.size()]);
}
/**
* @description:query field values from data ids
* @date:2014-5-6 下午5:25:51
* @version:v1.0
* @param dataIdArray
* @param queryField
* @param templateId
* @return
*/
public String[] queryFieldByIds(UUID[] dataIdArray , String queryField , UUID templateId) {
if (dataIdArray == null || dataIdArray.length == 0) {
return new String[0];
}
String[] dataIdStrArray = new String[dataIdArray.length];
for (int i = 0; i < dataIdArray.length; i++) {
dataIdStrArray[i] = dataIdArray[i].getValue();
}
return queryFieldByIds(dataIdStrArray, queryField, templateId);
}
/**
* @description:query id and field from dataIds
* @date:2014-5-6 下午5:26:33
* @version:v1.0
* @param dataIdArray
* @param fieldName
* @return
*/
public Map<String,String> queryIdAndFieldByIds(UUID[] dataIdArray , String fieldName) {
if (dataIdArray == null || dataIdArray.length == 0) {
return new HashMap<String, String>();
}
Map<String , String > fieldResultMap = new HashMap<String, String>();
List<String> tableList = TableRuleManager.getInstance().getAllDataTables();
StringBuffer idBuffer = new StringBuffer();
idBuffer.append(" ( ");
for (UUID dataId : dataIdArray) {
idBuffer.append(dataId.getValue()).append(",");
}
idBuffer.deleteCharAt(idBuffer.length() -1 );
idBuffer.append(")");
StringBuffer sqlBuffer = new StringBuffer();
for (String tableName : tableList) {
if (sqlBuffer.length() > 0) {
sqlBuffer.append(" union ");
}
sqlBuffer.append(" select id , ").append(fieldName).append(" from ").append(tableName).append(" where id in ").append(idBuffer).append(" and is_valid=1");
}
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try
{
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sqlBuffer.toString());
while(rs.next()) {
fieldResultMap.put(rs.getString("id"), rs.getString(fieldName));
}
}catch(Exception e){
logger.error("",e);
}finally
{
DbPoolConnection.getInstance().closeResultSet(rs);
DbPoolConnection.getInstance().closeStatment(stat);
DbPoolConnection.getInstance().closeConn(conn);
}
return fieldResultMap;
}
/**
* @description:query data exist like from db
* @date:2014-5-6 下午5:27:39
* @version:v1.0
* @param dataValue
* @param templateId
* @param fieldColName
* @return
*/
public boolean isDataExist(String dataValue, UUID templateId,String fieldColName) {
boolean isExist = false;
if (templateId == null) {
return isExist;
}
String tableName = TableRuleManager.getInstance().getDataTableName(templateId);
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
conn = DbPoolConnection.getInstance().getReadConnection();
pstm = conn.prepareStatement("select id from " + tableName + " where " + fieldColName + " like ? and is_valid=?" );
pstm.setString(1, "%" + dataValue + "%");
pstm.setString(2, "1");
rs = pstm.executeQuery();
isExist = rs.next();
} catch (Exception e) {
logger.error("",e);
}finally{
DbPoolConnection.getInstance().closeAll(rs, pstm, conn);
return isExist;
}
}
/**
* @description:query field values from sql
* @date:2014-5-6 下午5:27:59
* @version:v1.0
* @param sql
* @return
*/
public String[] queryFieldBySql(String sql) {
Set<String> dataSet = new HashSet<String>();
Connection conn = null;
Statement stat = null;
ResultSet rs = null;
try {
conn = DbPoolConnection.getInstance().getReadConnection();
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while(rs.next()){
dataSet.add(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
}finally{
DbPoolConnection.getInstance().closeAll(rs,stat, conn);
}
return dataSet.toArray(new String[0]);
}
/**
* @description:set tempate data valid
* @date:2014-8-7 下午5:11:02
* @version:v1.0
* @param templateId
* @param isValid
* @return
*/
public boolean setValidDataOfTemplate(UUID templateId, boolean isValid) {
if(templateId == null){
return false;
}
String tableName = TableRuleManager.getInstance().getDataTableName(templateId);
String logTableName = TableRuleManager.getInstance().getDataLogTableName(templateId);
Connection conn = null;
PreparedStatement pstm = null;
try {
conn = DbPoolConnection.getInstance().getConnection();
conn.setAutoCommit(false);
//更新data表
pstm = conn.prepareStatement("update " + tableName + " set is_valid = ? where templateId = ?");
pstm.setBoolean(1, isValid);
pstm.setString(2, templateId.getValue());
pstm.executeUpdate();
//更新log表
pstm = conn.prepareStatement("update " + logTableName + " set is_valid = ? where templateId = ?");
pstm.setBoolean(1, isValid);
pstm.setString(2, templateId.getValue());
pstm.executeUpdate();
conn.commit();
conn.setAutoCommit(true);
return true;
} catch (Exception e) {
try {
if (!conn.isClosed()) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
return false;
} finally{
DbPoolConnection.getInstance().closeAll(pstm, conn);
}
}
}