#数据库形式服务入口逻辑
@Service
public class SqlHandlerStrategyImpl implements SqlHandlerStrategy {
Logger logger = LoggerFactory.getLogger(SqlHandlerStrategyImpl.class);
@Autowired
private DBInfoService dbInfoService;
@Autowired
private ServiceObjectService serviceObjectService;
@Autowired
private ServiceObjectParamService serviceObjectParamService;
@Override
public int handleServiceDB(Integer wfId,
Integer companyId,
Integer userId,
ServiceLink serviceLink,
Map msgMap,
ServiceDb serviceDb,
ServiceObject inObj,
List<ServiceObjectParam> paramList) {
if (null == wfId
|| null == companyId
|| null == userId
|| null == serviceLink
|| null == msgMap
|| null == serviceDb
|| null == inObj
|| null == paramList) {
logger.info("SqlHandlerStrategyImpl.handleServiceDB 参数未满足执行要求，wfId=" + wfId);
return 0;
}
logger.info("SqlHandlerStrategyImpl.handleServiceDB 进行源请求处理，" +
"wfId=" + wfId + JSON.toJSONString(serviceDb));
Integer dbId = serviceDb.getDbId();
Byte sqlType = serviceDb.getSqlType();
DBInfo dbInfo = dbInfoService.selectDBInfoById(dbId);
if (null == dbInfo) {
logger.info("SqlHandlerStrategyImpl.dbInfo数据库信息不能为空，wfId="
+ wfId + ",serviceLink=" + JSON.toJSONString(serviceLink));
}
if (null == sqlType) {
logger.info("SqlHandlerStrategyImpl.dbInfo数据库信息脚本类型信息不能为空，" +
"wfId=：" + wfId + JSON.toJSONString(dbInfo));
return 0;
}
Byte dbType = dbInfo.getDbType();
if (null == dbType) {
logger.info("SqlHandlerStrategyImpl.dbInfo.dbType数据库类型不能为空，" +
"wfId=" + wfId + JSON.toJSONString(dbInfo));
return 0;
}
String content = serviceDb.getContent();
if (null == content) {
logger.info("SqlHandlerStrategyImpl.dbInfo数据库信息脚本信息不能为空，" +
"wfId=" + wfId + JSON.toJSONString(dbInfo));
return 0;
}
//sql 参数处理策略
SqlParamBinder sqlParamBinding = new SqlParamBinder();
logger.info("SqlHandlerStrategyImpl 进行脚本处理！|wfId=：" + wfId +
";|content=：" + content + "|inObj=:" + JSON.toJSONString(inObj));
String sql = sqlParamBinding.parseSqlParamBinding(wfId,
companyId,
userId,
msgMap,
content,
dbType,
JSON.toJSONString(paramList));
if (StringUtils.isNull(sql)) {
logger.info("SqlHandlerStrategyImpl 进行脚本处理失败，请检查请求参数是否和sql的参数相符合！" +
"|wfId=：" + wfId + ";|sql=：" + content + "|请求参数=:" + JSON.toJSONString(msgMap));
return 0;
}
//获得
Integer outObjId = serviceDb.getOutObjId();
if (null == outObjId) {
logger.info("SqlHandlerStrategyImpl.输出对象信息不能为空，wfId="
+ wfId + ",serviceLink=" + JSON.toJSONString(serviceLink));
return 0;
}
ServiceObject outServiceObject = serviceObjectService.selectServiceObjectById(outObjId);
if (null == outServiceObject) {
logger.info("SqlHandlerStrategyImpl.输出对象信息不能为空，wfId="
+ wfId + ",serviceLink=" + JSON.toJSONString(serviceLink));
return 0;
}
List<ServiceObjectParam> outServiceObjectParamList = serviceObjectParamService.selectServiceObjectParamListByObjId(outObjId);
if(CollectionUtils.isEmpty(outServiceObjectParamList)){
logger.info("SqlHandlerStrategyImpl.输出对象信息的字段参数不能为空，wfId="
+ wfId + "|serviceLink=" + JSON.toJSONString(serviceLink)+"|outServiceObject =:"+JSON.toJSONString(outServiceObject));
return 0;
}
DataBase poolDataBase = new DataBase();
pressDataBase(poolDataBase, dbInfo);
Connection conn = null;
Statement st = null;
ResultSet rs = null;
DBAdapter adapter = new DBAdapter(poolDataBase);
if (null == adapter) {
logger.info("SqlHandlerStrategyImpl.dbInfo数据库适配器获得失败，" +
"wfId=" + wfId + JSON.toJSONString(dbInfo));
return 0;
}
DruidPool pool = adapter.getPool();
if (null == pool) {
logger.info("SqlHandlerStrategyImpl.dbInfo数据库连接池获得失败，" +
"wfId=" + wfId + JSON.toJSONString(dbInfo));
return 0;
}
try {
conn = pool.getConnection();
if (null == conn) {
logger.info("SqlHandlerStrategyImpl 获得数据库连接失败！没有获得数据库连接！");
return 0;
}
st = conn.createStatement();
/*脚本处理执行策略*/
List<Map> resultMap = new ArrayList<>();
if (sqlType.equals(SqlTypeEnum.SELECT.getIndex())) {
rs = st.executeQuery(sql);
ResultSetParamBinder resultSetParamBinder = new ResultSetParamBinder();
resultSetParamBinder.bindSqlResultSetParams(
wfId,
companyId,
userId,
rs,
resultMap,
JSON.toJSONString(outServiceObjectParamList)
);
} else if (sqlType.equals(SqlTypeEnum.DELETE.getIndex())
|| sqlType.equals(SqlTypeEnum.UPDATE.getIndex())) {
//TODO
} else if (sqlType.equals(SqlTypeEnum.INSERT.getIndex())) {
//TODO
} else {
logger.info("SqlHandlerStrategyImpl sql 脚本类型超出系统执行范围！");
return 0;
}
logger.info("SqlHandlerStrategyImpl 获得结果 resultMap"+JSON.toJSONString(resultMap));
//TODO 将数据推送到服务执行队列
} catch (Exception e) {
String msg = e.getMessage();
logger.info("SqlHandlerStrategyImpl 获得数据库连接失败！错误信息："
+ msg + "wfId=" + wfId + JSON.toJSONString(dbInfo));
return 0;
} finally {
try {
} catch (Exception e) {
pool.closeConnection();
String msg = e.getMessage();
logger.info("SqlHandlerStrategyImpl 获得数据库关闭失败！错误信息："
+ msg + "wfId=" + wfId + JSON.toJSONString(dbInfo));
}
}
return 1;
}
private void pressDataBase(DataBase poolDataBase, DBInfo dbInfo) {
poolDataBase.setDbAccount(dbInfo.getDbAccount());
poolDataBase.setDbIp(dbInfo.getDbIp());
poolDataBase.setDbName(dbInfo.getDbName());
poolDataBase.setDbPort(dbInfo.getDbPort());
String pwd = Encipher.DecodePasswd(dbInfo.getDbPwd());
poolDataBase.setDbPwd(pwd);
poolDataBase.setDbType(dbInfo.getDbType());
}
public class SqlParamBinder {
Logger logger = LoggerFactory.getLogger(SqlParamBinder.class);
/**
* 脚本解析类
*
* @param wfId
* @param companyId
* @param userId
* @param msgMap
* @param content
* @param dbType
* @return
*/
public String parseSqlParamBinding(Integer wfId,
Integer companyId,
Integer userId,
Map msgMap,
String content,
Byte dbType,
String paramList) {
String sql = null;
if (StringUtils.isEmpty(content)) {
logger.info("SqlParamBinding content不能为空！");
return sql;
}
if (StringUtils.isEmpty(paramList)) {
logger.info("SqlParamBinding content不能为空！");
return sql;
}
if (StringUtils.isEmpty(msgMap)) {
logger.info("SqlParamBinding msgMap不能为空！");
return sql;
}
if (StringUtils.isEmpty(dbType)) {
logger.info("SqlParamBinding mdbType不能为空！");
return sql;
}
List<ServiceObjectParam> params = JSON.parseArray(paramList, ServiceObjectParam.class);
//数据库策略
if (dbType.equals(DBEnum.MYSQL.getCode())) {
logger.info(">>>>>SqlParamBinding MYSQL sql press>>>>>>>>");
sql = parseMysqlSql(wfId,
companyId,
userId,
msgMap,
content,
dbType,
params);
} else if (dbType.equals(DBEnum.ORACLE.getCode())) {
logger.info(">>>>>SqlParamBinding ORACLE sql press>>>>>>>>");
sql = parseOracleSql(wfId,
companyId,
userId,
msgMap,
content,
dbType,
params);
} else if (dbType.equals(DBEnum.SQLSERVER.getCode())) {
logger.info(">>>>>SqlParamBinding SQLSERVER sql press>>>>>>>>");
sql = parseSqlserverSql(wfId,
companyId,
userId,
msgMap,
content,
dbType,
params);
} else {
logger.info("SqlParamBinding 系统支持的数据库类型MYSQL ORACLE SQLSERVER ，脚本已超出处理的数据库范围。");
return sql;
}
return sql;
}
/*parseSqlserverSql*/
private String parseSqlserverSql(Integer wfId,
Integer companyId,
Integer userId,
Map msgMap,
String content,
Byte dbType,
List<ServiceObjectParam> params) {
String sql = null;
//content 参数采用#{}封装参数
//TODO
return sql;
}
/*parseOracleSql*/
private String parseOracleSql(Integer wfId,
Integer companyId,
Integer userId,
Map msgMap,
String content,
Byte dbType,
List<ServiceObjectParam> params) {
//TODO
String sql = null;
return sql;
}
/*parseMysqlSql*/
private String parseMysqlSql(Integer wfId,
Integer companyId,
Integer userId,
Map msgMap,
String content,
Byte dbType,
List<ServiceObjectParam> params) {
String sql = null;
if (StringUtils.isEmpty(content)) {
logger.info("SqlParamBinding parseMysqlSql content不能为空！wfId=：" + wfId);
return sql;
}
if (CollectionUtils.isEmpty(params)) {
logger.info("SqlParamBinding parseMysqlSql params 不能为空！wfId=：" + wfId);
return sql;
}
if (StringUtils.isEmpty(msgMap)) {
logger.info("SqlParamBinding parseMysqlSql msgMap不能为空！wfId=：" + wfId);
return sql;
}
if (StringUtils.isEmpty(dbType)) {
logger.info("SqlParamBinding parseMysqlSql dbType 不能为空！wfId=：" + wfId);
return sql;
}
List<String> paramTagList = BindingParser.findMatch(content);
if (CollectionUtils.isEmpty(paramTagList)) {
logger.info("SqlParamBinding parseMysqlSql 没有待替换的参数，返回源脚本内容！wfId=：" + wfId + "|content=：" + content);
return content;
}
/*处理换行符*/
String sqlAfterDeleteEnterTag = BindingParser.deleteEnterTag(content);
logger.info("SqlParamBinding parseMysqlSql.sqlAfterDeleteEnterTag sqlAfterDeleteEnterTag=:" + sqlAfterDeleteEnterTag);
/*删除参数=之间空格*/
String sqlAfterDeleteSpace = BindingParser.deleteSpace(sqlAfterDeleteEnterTag);
logger.info("SqlParamBinding parseMysqlSql.deleteSpace sqlAfterDeleteSpace=:" + sqlAfterDeleteSpace);
/*进行参数替换
*
* 1.所有参数必须完全替换，如果没有替换完全，进行异常抛出
*
* 2.如果有 null 的数据，进行 is null 替换
*
* //TODO 3. 待补充
* */
List<String> list4Match = BindingParser.findMatch(sqlAfterDeleteSpace);
if (CollectionUtils.isEmpty(list4Match)) {
logger.info("SqlParamBinding parseMysqlSql.findMatch list4Match 不能为空！" + "|wfId=：" + wfId);
return sql;
}
List<String> paramList = new ArrayList<>();
for (String paramTag : list4Match) {
paramTag = paramTag.replace("#{", "");
paramTag = paramTag.replace("}", "");
paramList.add(paramTag);
}
Set<String> paramsKeySet = msgMap.keySet();
List<String> paramListIsNotContain = new ArrayList<>();
for (String param : paramList) {
if (!paramsKeySet.contains(param)) {
paramListIsNotContain.add(param);
}
}
if (!CollectionUtils.isEmpty(paramListIsNotContain)) {
logger.info("SqlParamBinding parseMysqlSql.sql中的参数没有传入paramListIsNotContain=:"
+ JSON.toJSONString(paramListIsNotContain) + "|wfId=：" + wfId);
return sql;
}
Map<String, ServiceObjectParam> soParamsMap = new HashMap<>();
for (ServiceObjectParam param : params) {
String paramCode = param.getParamCode();
if (!soParamsMap.containsKey(paramCode)) {
soParamsMap.put(paramCode, param);
}
}
Set<String> soparamsKeySet = soParamsMap.keySet();
List<String> paramsIsNotContainInSoParams = new ArrayList<>();
for (String param : paramList) {
if (!paramsKeySet.contains(param)) {
paramsIsNotContainInSoParams.add(param);
}
}
if (!CollectionUtils.isEmpty(paramsIsNotContainInSoParams)) {
logger.info("SqlParamBinding parseMysqlSql.sql中的参数有参数与绑定对象字段不相符，paramsIsNotContainInSoParams=:"
+ JSON.toJSONString(paramsIsNotContainInSoParams) + "|wfId=：" + wfId);
return sql;
}
//进行数值替换
StringBuffer sb = new StringBuffer(sqlAfterDeleteSpace);
for (String paramTag : list4Match) {
String bridge = null;
bridge = sb.toString();
String originParam = paramTag;
paramTag = paramTag.replace("#{", "");
paramTag = paramTag.replace("}", "");
if (!paramsKeySet.contains(paramTag)) {
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
} else {
/*根据数据据类型拼接字符串
* 1.数值直接替换
*
* 2.boolean 直接替换
*
* 3.字符串加'' 单引号
*
* 4.日期加'' 单引号
*
* 5.没有获得数据，,直接替换为null
*
* */
ServiceObjectParam param = soParamsMap.get(paramTag);
if (null == param) {
logger.info("SqlParamBinding parseMysqlSql.sql中的参数有参数与绑定对象字段不相符,绑定对象没有参数paramTag=:" + paramTag + "|wfId=：" + wfId);
continue;
}
Object ovalue = msgMap.get(paramTag);
if (null == ovalue) {
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
} else {
Byte paramType = param.getParamType();
if (null == paramType) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode() + "|wfId=：" + wfId);
continue;
}
if (paramType.equals(DataTypeEnum.INT.getIndex())) {
try {
Integer value = Integer.parseInt(ovalue.toString());
bridge = bridge.replace(originParam, value.toString());
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "INT数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else if (paramType.equals(DataTypeEnum.FLOAT.getIndex())) {
try {
Float value = Float.parseFloat(ovalue.toString());
bridge = bridge.replace(originParam, value.toString());
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "FLOAT数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else if (paramType.equals(DataTypeEnum.DOUBLE.getIndex())) {
try {
Double value = Double.parseDouble(ovalue.toString());
bridge = bridge.replace(originParam, value.toString());
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "DOUBLE数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else if (paramType.equals(DataTypeEnum.BOOLEAN.getIndex())) {
try {
String value = ovalue.toString();
bridge = bridge.replace(originParam, value.toString());
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "BOOLEAN数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else if (paramType.equals(DataTypeEnum.STRING.getIndex())) {
try {
String value = ovalue.toString();
bridge = bridge.replace(originParam, "'".concat(value.toString()).concat("'"));
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "STRING数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else if (paramType.equals(DataTypeEnum.LONG.getIndex())) {
try {
Long value = Long.parseLong(ovalue.toString());
bridge = bridge.replace(originParam, value.toString());
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "LONG数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else if (paramType.equals(DataTypeEnum.DATE.getIndex())) {
try {
String value = ovalue.toString();
bridge = bridge.replace(originParam, "'".concat(value.toString()).concat("'"));
sb = new StringBuffer(bridge);
} catch (Exception e) {
logger.info("SqlParamBinding parseMysqlSql.绑定对象的字段paramCode=:" + param.getParamCode()
+ "DATE数据转换异常|wfId=：" + wfId + "|exception:" + e.getMessage());
bridge = bridge.replace(originParam, "null");
sb = new StringBuffer(bridge);
}
} else {
//TODO press other datatype
}
}
}
}
String sqlAfterReplace = sb.toString();
//将 =null 转换成 is null
sql = sqlAfterReplace.replace("=null", " is null ");
//sql处理完毕
logger.info("SqlParamBinding parseMysqlSql sql已经绑定成功！sql=:"+sql+" |wfId=：" + wfId);
return sql;
}
#参数绑定器
public class BindingParser {
/*#{******} 字符串匹配*/
private final static String PATTERN = "#(.+?)(?<=})";
/**/
private final static String PATTERN_SPACE = "=(.+?)(?<=})";
/*=null 字符串匹配*/
private final static String PATTERN_NULL = "(=)?null";
/*查询tag*/
public static List<String> findMatch(String txt) {
List<String> matchList = new ArrayList<>();
if (StringUtils.isEmpty(txt)) {
return null;
}
Pattern p = Pattern.compile(PATTERN);
Matcher m = p.matcher(txt);
while (m.find()) {
String mat = m.group();
matchList.add(mat);
}
System.out.println("findMatch:" + JSON.toJSONString(matchList));
return matchList;
}
/*查询带空格tag*/
public static List<String> findMatchSpace(String txt) {
List<String> matchList = new ArrayList<>();
if (StringUtils.isEmpty(txt)) {
return null;
}
Pattern p = Pattern.compile(PATTERN_SPACE);
Matcher m = p.matcher(txt);
while (m.find()) {
String mat = m.group();
matchList.add(mat);
}
System.out.println("findMatchSpace:" + JSON.toJSONString(matchList));
return matchList;
}
/*查询null */
public static List<String> findMatchNull(String txt) {
List<String> matchList = new ArrayList<>();
if (StringUtils.isEmpty(txt)) {
return null;
}
Pattern p = Pattern.compile(PATTERN_NULL);
Matcher m = p.matcher(txt);
while (m.find()) {
String mat = m.group();
matchList.add(mat);
}
System.out.println("findMatchNull:" + JSON.toJSONString(matchList));
return matchList;
}
/*去除空格*/
public static String deleteSpace(String txt) {
if (StringUtils.isEmpty(txt)) {
return null;
}
System.out.println("deleteSpace 开始处理>>>>>>txt=:" + txt);
List<String> list = BindingParser.findMatchSpace(txt);
StringBuffer sb = new StringBuffer(txt);
for (String paramsTag : list) {
String bridge = null;
bridge = sb.toString();
String target = paramsTag.replace(" ", "");
bridge = bridge.replace(paramsTag, target);
sb = new StringBuffer(bridge);
}
String sql = sb.toString();
System.out.println("deleteSpace 结束处理>>>>>>txt=:" + sql);
return sql;
}
/*处理换行符*/
public static String deleteEnterTag(String txt) {
if (StringUtils.isEmpty(txt)) {
return null;
}
System.out.println("deleteEnterTag 开始处理>>>>>>txt=:" + txt);
List<String> list = BindingParser.findMatchSpace(txt);
String sql = txt.replaceAll("\r|\n", " ");
System.out.println("deleteEnterTag 结束处理>>>>>>txt=:" + sql);
return sql;
}
public static void main(String[] args) {
String sql = "select * from test_device_info where device_id = #{ deviceId} and wen_du= #{ wenDu} =#{} #{} = null #{}";
BindingParser.findMatch(sql);
BindingParser.findMatchSpace(sql);
BindingParser.findMatchNull(sql);
BindingParser.deleteSpace(sql);
System.out.println("main:");
}
}
#结果解析封装
pulic class ResultSetParamBinder {
Logger logger = LoggerFactory.getLogger(ResultSetParamBinder.class);
/**
* @param wfId 工作流单元id
* @param companyId 公司id
* @param userId 用户id
* @param rs ResultSet 查询结果集
* @param resultMap 结果集封装的数据结构
* @param paramList 输出对象参数集合
*/
public void bindSqlResultSetParams(Integer wfId,
Integer companyId,
Integer userId,
ResultSet rs,
List<Map> resultMap,
String paramList) {
if (null == wfId
|| null == companyId
|| null == userId
|| null == rs
|| null == paramList
) {
String error = "bindResultSetParams 参数不满足程序运行需求，以下不能为空 " +
"wfId=:"
+ wfId
+ "|companyId=:"
+ companyId + "|userId=:"
+ userId + "|rs=:"
+ rs + "|paramList=:"
+ paramList;
logger.info(error);
return;
}
try {
List<ServiceObjectParam> outServiceObjectParamList = JSON.parseArray(paramList, ServiceObjectParam.class);
rs.last();
int rowCount = rs.getRow();
rs.beforeFirst();
if (rowCount < 1) {
String msg = "bindResultSetParams ，没有查询到信息。" +
"|wfId=:"
+ wfId
+ "|companyId=:"
+ companyId + "|userId=:"
+ userId + "|rs=:"
+ rs + "|paramList=:"
+ paramList;
logger.info(msg);
return;
}
while (rs.next()) {
Map<String,Object> subMap = new HashMap<>();
for (ServiceObjectParam param : outServiceObjectParamList) {
String paramCode = param.getParamCode();
String paramName = param.getParamName();
Integer paramId = param.getParamId();
if (StringUtils.isNull(paramCode)) {
String msg = "bindResultSetParams ，输出对象字段编码缺失" +
"|wfId=:"
+ wfId + "|companyId=:"
+ companyId + "|userId=:"
+ userId + "|rs=:"
+ rs + "|paramList=:"
+ paramList + "|paramId=:"
+ paramId;
logger.info(msg);
continue;
}
Object object = rs.getObject(paramCode);
subMap.put(paramCode,object);
}
resultMap.add(subMap);
}
} catch (Exception e) {
String error = "bindResultSetParams 处理结果集发生异常，exception=: " + e.getMessage() +
"|wfId=:"
+ wfId
+ "|companyId=:"
+ companyId + "|userId=:"
+ userId + "|rs=:"
+ rs + "|paramList=:"
+ paramList;
logger.info(error);
return;
}
}
//TODO delete
//TODO update
//TODO insert
}
#线程池
public class DruidPool {
Logger logger = LoggerFactory.getLogger(DruidPool.class);
private static DruidPool single = null;
public static DruidDataSource DRUID_DATA_SOURCE = new DruidDataSource();
public DruidPool() {
}
/**
* 获取连接实例
*
* @return
*/
public static DruidPool getInstance() {
if (single == null) {
synchronized (DruidPool.class) {
if (single == null) {
single = new DruidPool();
}
}
}
return single;
}
/*初始化数据库连接资源*/
public DruidPool(DataBase db) {
Properties prop = new Properties();
if (DBEnum.MYSQL.getCode().equals(db.getDbType())) {
prop.setProperty("driver", "com.mysql.jdbc.Driver");
prop.setProperty("url", "jdbc:mysql://" + db.getDbIp() + ":" + db.getDbPort() + "/" + db.getDbName());
prop.setProperty("connectionProperties", "useUnicode=true;characterEncoding=UTF8");
} else if (DBEnum.ORACLE.getCode().equals(db.getDbType())) {
prop.setProperty("driver", "oracle.jdbc.driver.OracleDriver");
prop.setProperty("url", "jdbc:oracle:thin:@" + db.getDbIp() + ":" + db.getDbPort() + ":" + db
.getDbName());
} else if (DBEnum.SQLSERVER.getCode().equals(db.getDbType())) {
prop.setProperty("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver");
prop.setProperty("url", "jdbc:sqlserver://" + db.getDbIp() + ":" + db.getDbPort() + ";DatabaseName=" + db.getDbName());
} else {
logger.info("连接池目前支持Mysql、Oracle、Sqlserver三种数据库类型！");
throw new RuntimeException("连接池目前支持Mysql、Oracle、Sqlserver三种数据库类型！");
}
prop.setProperty("username", db.getDbAccount());
String passwd = db.getDbPwd();
prop.setProperty("password", passwd);
prop.setProperty("initialSize", "3");
prop.setProperty("maxActive", "6");
prop.setProperty("minIdle", "1");
prop.setProperty("maxWait", "60000");
prop.setProperty("filters", "stat");
prop.setProperty("timeBetweenEvictionRunsMillis", "35000");
prop.setProperty("minEvictableIdleTimeMillis", "30000");
prop.setProperty("testWhileIdle", "true");
prop.setProperty("testOnBorrow", "false");
prop.setProperty("testOnReturn", "false");
prop.setProperty("poolPreparedStatements", "false");
prop.setProperty("maxPoolPreparedStatementPerConnectionSize", "200");
prop.setProperty("removeAbandoned", "true");
try {
DRUID_DATA_SOURCE = (DruidDataSource) DruidDataSourceFactory
.createDataSource(prop);
if (null != DRUID_DATA_SOURCE) {
DRUID_DATA_SOURCE.setBreakAfterAcquireFailure(true);
}
} catch (Exception e) {
e.printStackTrace();
logger.info("初始化创建连接池失败！");
}
}
/*;获得连接*/
public Connection getConnection() throws SQLException {
return DRUID_DATA_SOURCE.getConnection();
}
/*关闭连接*/
public void closeConnection() {
DRUID_DATA_SOURCE.close();
}
}
#代理器
public class DBAdapter {
Logger logger = LoggerFactory.getLogger(DBAdapter.class);
private DruidPool pool = null;
public DBAdapter(DataBase db) {
pool = new DruidPool(db);
}
public DruidPool getPool() {
return pool;
}
public void setPool(DruidPool pool) {
this.pool = pool;
}
}
#java bean
public class ServiceObjectParam {
private Integer paramId;
private Integer companyId;
private Integer objId;
private String paramName;
private String paramCode;
private Byte paramType;
private Integer listObjId;
private Integer paramLength;
private Byte isNull;
private Byte isRuleKey;
private Integer ruleId;
private Date updateTime;
private Date createTime;
private Integer createUserId;
public Integer getParamId() {
return paramId;
}
public void setParamId(Integer paramId) {
this.paramId = paramId;
}
public Integer getCompanyId() {
return companyId;
}
public void setCompanyId(Integer companyId) {
this.companyId = companyId;
}
public Integer getObjId() {
return objId;
}
public void setObjId(Integer objId) {
this.objId = objId;
}
public String getParamName() {
return paramName;
}
public void setParamName(String paramName) {
this.paramName = paramName == null ? null : paramName.trim();
}
public String getParamCode() {
return paramCode;
}
public void setParamCode(String paramCode) {
this.paramCode = paramCode == null ? null : paramCode.trim();
}
public Byte getParamType() {
return paramType;
}
public void setParamType(Byte paramType) {
this.paramType = paramType;
}
public Integer getListObjId() {
return listObjId;
}
public void setListObjId(Integer listObjId) {
this.listObjId = listObjId;
}
public Integer getParamLength() {
return paramLength;
}
public void setParamLength(Integer paramLength) {
this.paramLength = paramLength;
}
public Byte getIsNull() {
return isNull;
}
public void setIsNull(Byte isNull) {
this.isNull = isNull;
}
public Byte getIsRuleKey() {
return isRuleKey;
}
public void setIsRuleKey(Byte isRuleKey) {
this.isRuleKey = isRuleKey;
}
public Integer getRuleId() {
return ruleId;
}
public void setRuleId(Integer ruleId) {
this.ruleId = ruleId;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Integer getCreateUserId() {
return createUserId;
}
public void setCreateUserId(Integer createUserId) {
this.createUserId = createUserId;
}
}
/**
* 数据库类型枚举类
*/
public enum DBEnum {
/*mysql*/
MYSQL(new Byte("0")),
/*oracle*/
ORACLE(new Byte("1")),
/*sqlserver*/
SQLSERVER(new Byte("2"));
private Byte code;
DBEnum(Byte code) {
this.code = code;
}
public Byte getCode() {
return code;
}
}
public class DataBase {
private String dbAccount;
private String dbPwd;
private String dbIp;
private String dbPort;
private Byte dbType;
private String dbName;
public String getDbAccount() {
return dbAccount;
}
public void setDbAccount(String dbAccount) {
this.dbAccount = dbAccount;
}
public String getDbPwd() {
return dbPwd;
}
public void setDbPwd(String dbPwd) {
this.dbPwd = dbPwd;
}
public String getDbIp() {
return dbIp;
}
public void setDbIp(String dbIp) {
this.dbIp = dbIp;
}
public String getDbPort() {
return dbPort;
}
public void setDbPort(String dbPort) {
this.dbPort = dbPort;
}
public Byte getDbType() {
return dbType;
}
public void setDbType(Byte dbType) {
this.dbType = dbType;
}
public String getDbName() {
return dbName;
}
public void setDbName(String dbName) {
this.dbName = dbName;
}
}
/**
* 数据类型枚举类
*/
public enum DataTypeEnum {
/* 字段数据类型
0:INT,
1:FLOAT
2:DOUBLE
3:BOOLEAN
4:STRING
5:LONG
6:LIST
7.DATE
*/
/*整数*/
INT("整数", new Byte("0")),
/*浮点*/
FLOAT("浮点", new Byte("1")),
/*双精度*/
DOUBLE("双精度", new Byte("2")),
/*布尔*/
BOOLEAN("布尔", new Byte("3")),
/*字符串*/
STRING("字符串", new Byte("4")),
/*长整型*/
LONG("长整型", new Byte("5")),
/*集合*/
LIST("集合", new Byte("6")),
/*日期*/
DATE("日期", new Byte("7"));
/*名称*/
private String name;
/*索引*/
private Byte index;
private DataTypeEnum(String name, Byte index) {
this.name = name;
this.index = index;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Byte getIndex() {
return index;
}
public void setIndex(Byte index) {
this.index = index;
}
}
/**
* 服务类型枚举类
* ylyin
*/
public enum ServiceTypeEnum {
/*DB服务*/
DBTYPE("DB服务", new Byte("1")),
/*restful服务*/
RRSTFUL("restful服务", new Byte("0"));
/*名称*/
private String name;
/*索引*/
private Byte index;
private ServiceTypeEnum(String name, Byte index) {
this.name = name;
this.index = index;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Byte getIndex() {
return index;
}
public void setIndex(Byte index) {
this.index = index;
}
@Override
public String toString() {
return this.index + "_" + this.name;
}
}
浙公网安备 33010602011771号