package com.wgcloud.util.jdbc;
|
|
import cn.hutool.core.collection.CollectionUtil;
|
import com.wgcloud.config.CommonConfig;
|
import com.wgcloud.entity.DbInfo;
|
import com.wgcloud.entity.DbTable;
|
import com.wgcloud.service.DbInfoService;
|
import com.wgcloud.service.LogInfoService;
|
import com.wgcloud.util.DateUtil;
|
import com.wgcloud.util.FormatUtil;
|
import com.wgcloud.util.ThreadPoolUtil;
|
import com.wgcloud.util.msg.WarnMailUtil;
|
import com.wgcloud.util.msg.WarnPools;
|
import com.wgcloud.util.staticvar.StaticKeys;
|
import org.apache.commons.lang3.StringUtils;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.jdbc.datasource.DriverManagerDataSource;
|
import org.springframework.stereotype.Component;
|
|
import javax.annotation.Resource;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* @version v3.3
|
* @ClassName:ConnectionUtil.java
|
* @author: http://www.wgstart.com
|
* @date: 2021年1月16日
|
* @Description: ConnectionUtil.java
|
* @Copyright: 2019-2021 wgcloud. All rights reserved.
|
*/
|
@Component
|
public class ConnectionUtil {
|
private static final Logger logger = LoggerFactory.getLogger(ConnectionUtil.class);
|
@Resource
|
private LogInfoService logInfoService;
|
@Resource
|
private DbInfoService dbInfoService;
|
@Autowired
|
CommonConfig commonConfig;
|
|
public JdbcTemplate getJdbcTemplate(DbInfo dbInfo) throws Exception {
|
JdbcTemplate jdbcTemplate = null;
|
String driver = "";
|
String url = dbInfo.getDbUrl();
|
if (RDSConnection.KEY_MYSQL.equals(dbInfo.getDbType())) {
|
driver = RDSConnection.DRIVER_MYSQL;
|
} else if (RDSConnection.KEY_MARIADB.equals(dbInfo.getDbType())) {
|
driver = RDSConnection.DRIVER_MARIADB;
|
} else if (RDSConnection.KEY_POSTGRESQL.equals(dbInfo.getDbType())) {
|
driver = RDSConnection.DRIVER_POSTGRESQL;
|
} else if (RDSConnection.KEY_SQLSERVER.equals(dbInfo.getDbType())) {
|
driver = RDSConnection.DRIVER_SQLSERVER;
|
} else if (RDSConnection.KEY_DB2.equals(dbInfo.getDbType())) {
|
driver = RDSConnection.DRIVER_DB2;
|
} else {
|
driver = RDSConnection.DRIVER_ORACLE;
|
}
|
try {
|
//创建连接池
|
DriverManagerDataSource dataSource = new DriverManagerDataSource();
|
dataSource.setDriverClassName(driver);
|
dataSource.setUrl(url);
|
dataSource.setUsername(dbInfo.getUserName());
|
dataSource.setPassword(dbInfo.getPasswd());
|
jdbcTemplate = new JdbcTemplate(dataSource);
|
jdbcTemplate.setQueryTimeout(120);//超时120s
|
if (RDSConnection.KEY_MYSQL.equals(dbInfo.getDbType())) {
|
jdbcTemplate.queryForRowSet(RDSConnection.MYSQL_VERSION);
|
} else if (RDSConnection.KEY_MARIADB.equals(dbInfo.getDbType())) {
|
jdbcTemplate.queryForRowSet(RDSConnection.MYSQL_VERSION);
|
} else if (RDSConnection.KEY_POSTGRESQL.equals(dbInfo.getDbType())) {
|
jdbcTemplate.queryForRowSet(RDSConnection.MYSQL_VERSION);
|
} else if (RDSConnection.KEY_SQLSERVER.equals(dbInfo.getDbType())) {
|
jdbcTemplate.queryForRowSet(RDSConnection.SQLSERVER_VERSION);
|
} else if (RDSConnection.KEY_DB2.equals(dbInfo.getDbType())) {
|
jdbcTemplate.queryForRowSet(RDSConnection.DB2_VERSION);
|
} else {
|
jdbcTemplate.queryForRowSet(RDSConnection.ORACLE_VERSION);
|
}
|
dbInfo.setDbState(StaticKeys.ON_STATE);
|
if (null != WarnPools.MEM_WARN_MAP && null != WarnPools.MEM_WARN_MAP.get(dbInfo.getId())) {
|
Runnable runnable = () -> {
|
WarnMailUtil.sendDbDown(dbInfo, false);
|
};
|
ThreadPoolUtil.executor.execute(runnable);
|
}
|
//只有数据源在线时候，才更新时间，连接失败就不更新时间
|
dbInfo.setCreateTime(DateUtil.getNowTime());
|
dbInfoService.updateById(dbInfo);
|
return jdbcTemplate;
|
} catch (Exception e) {
|
jdbcTemplate = null;
|
logger.error("连接数据库错误", e);
|
dbInfo.setDbState(StaticKeys.DOWN_STATE);
|
dbInfoService.updateById(dbInfo);
|
Runnable runnable = () -> {
|
WarnMailUtil.sendDbDown(dbInfo, true);
|
};
|
ThreadPoolUtil.executor.execute(runnable);
|
}
|
return null;
|
}
|
|
public long queryTableCount(DbInfo dbInfo, JdbcTemplate jdbcTemplate, DbTable dbTable) {
|
try {
|
dbTable.setState(StaticKeys.ON_STATE);
|
String sqlinkey = FormatUtil.haveSqlDanger(dbTable.getWhereVal(), commonConfig.getSqlInKeys());
|
if (!StringUtils.isEmpty(sqlinkey)) {
|
String errinfo = "统计SQL语句含有sql敏感字符" + sqlinkey;
|
logger.error("统计数据表错误：" + errinfo);
|
logInfoService.save(errinfo + "：" + dbInfo.getAliasName(), "数据库别名：" + dbInfo.getAliasName() + "，数据表别名：" + dbTable.getRemark()
|
+ "，错误信息：" + errinfo, StaticKeys.LOG_XTCZ);
|
return 0;
|
}
|
|
if (null == jdbcTemplate) {
|
dbTable.setState(StaticKeys.DOWN_STATE);
|
return 0;
|
}
|
List<Map<String, Object>> list = jdbcTemplate.queryForList(dbTable.getWhereVal());
|
if (CollectionUtil.isEmpty(list)) {
|
return 0;
|
} else {
|
//取第一行
|
Map<String, Object> mapResult = list.get(0);
|
String resultStr = "0";
|
if (StringUtils.isEmpty(dbTable.getTableName())) {
|
//如果没有设置取值列名，默认取值
|
for (String key : mapResult.keySet()) {
|
resultStr = mapResult.get(key).toString();
|
}
|
} else {
|
resultStr = mapResult.get(dbTable.getTableName()).toString();
|
}
|
if (resultStr.indexOf(".") > -1) {
|
return Double.valueOf(resultStr).longValue();
|
} else {
|
return Long.valueOf(resultStr);
|
}
|
}
|
} catch (Exception e) {
|
dbTable.setState(StaticKeys.DOWN_STATE);
|
logger.error("统计数据表错误：", e);
|
logInfoService.save("统计数据表sql执行错误：" + dbInfo.getAliasName(), "数据库别名：" + dbInfo.getAliasName() + "，数据表别名：" + dbTable.getRemark() +
|
"，错误信息：" + e.toString(), StaticKeys.LOG_XTCZ);
|
return 0;
|
}
|
}
|
|
}
|
