需求
在不同表不同 ip 数据库,需要同步表内容,可以查询但是没法主从时,可以做定时同步,写了一个工具类,读取配置好的表 用来同步不同数据库内容
最好写一个页面 点点点完事,本人懒没写
SynDBAction.java
package com.cter.action;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import com.cter.util.TempDBUtils;
import org.springframework.stereotype.Component;
import java.sql.SQLException;
import java.util.*;
@Component("SynDBAction")//为了加入 Spring 然后定时器 调用,定时同步,如果不需要就去掉
public class SynDBAction {
public static void main(String[] args) throws Exception {
comparison();
}
public static List<Map<String, Object>> getSyncConfig() {
TempDBUtils tempDBUtils = TempDBUtils.getNoteBookConnection("nm_shared_info");
String sql = "select * from sync_config ";
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
List<Map<String, Object>> results = new ArrayList<>();
List<Object> params = new ArrayList<Object>();
try {
results = tempDBUtils.executeQuery(sql, params);
for (int i = 0; i < results.size(); i++) {
Map<String, Object> resultMap = results.get(i);
System.out.println(JSONUtil.toJsonStr(resultMap));
}
return results;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
tempDBUtils.closeDB();
}
}
/**
* 对比 trunk_info
*/
public static void comparison() throws Exception {
List<Map<String, Object>> syncConfigList = getSyncConfig();
/**
可点击key和value值进行编辑
{
"table_name_id":"city_1",###这次同步的表格主键名称,为了区分一个表可能同步几次,所以没有做联合主键
"update_table_schema":"table_db_name",###将要更新的db名称
"from_table_schema":"table_db_name",###来源数据库的db名称
"update_field":"field1,field2,field3,field4,field5,field6",###将要更新table的名称
"from_field":"field1,field2,field3,field4,field5,field6",###来源table的名称
"update_db_config":"db_name###db_password###jdbc:mysql://11.11.11.11:3306/table_db_name?characterEncoding=utf-8",###将要更新数据库的链接配置
"from_db_config":"db_name###db_password###jdbc:mysql://22.22.22.22:3306/table_db_name?characterEncoding=utf-8",###来源数据库的链接配置
"update_table_name":"table_name",###将要更新数据的table名称
"from_table_name":"table_name"###来源数据的table名称
}
**/
for (Map<String, Object> syncConfigMap : syncConfigList) {
String table_name_id = syncConfigMap.get("table_name_id").toString();
String from_table_schema = syncConfigMap.get("from_table_schema").toString();
String update_table_schema = syncConfigMap.get("update_table_schema").toString();
String from_table_name = syncConfigMap.get("from_table_name").toString();
String update_table_name = syncConfigMap.get("update_table_name").toString();
String from_field = syncConfigMap.get("from_field").toString();
String update_field = syncConfigMap.get("update_field").toString();
String from_db_config = syncConfigMap.get("from_db_config").toString();
String update_db_config = syncConfigMap.get("update_db_config").toString();
String[] fromFieldAll = from_field.split(",");
String[] updateFieldAll = update_field.split(",");
String fromSynSql = "select " + from_field + " from " + from_table_name;
String updateSynSql = "select " + update_field + " from " + update_table_name;
TempDBUtils fromDB = new TempDBUtils(from_db_config.split("###")[0], from_db_config.split("###")[1], from_db_config.split("###")[2]);
TempDBUtils updateDB = new TempDBUtils(update_db_config.split("###")[0], update_db_config.split("###")[1], update_db_config.split("###")[2]);
String fromPRI = getPRI(fromDB, from_table_schema, from_table_name);
String updatePRI = getPRI(updateDB, from_table_schema, from_table_name);
List<Map<String, Object>> fromRest = fromDB.executeQuery(fromSynSql, null);
List<Map<String, Object>> updateRest = updateDB.executeQuery(updateSynSql, null);
HashMap<String, Map<String, Object>> formMap = new HashMap<>();
HashMap<String, Map<String, Object>> updateMap = new HashMap<>();
for (Map<String, Object> map : fromRest) {
formMap.put(map.get(fromPRI).toString(), map);
}
for (Map<String, Object> map : updateRest) {
updateMap.put(map.get(updatePRI).toString(), map);
}
//增加的
//减少的
//改变的
//没有改变的
List<Map<String, Object>> addList = new LinkedList<>();
List<Map<String, Object>> lessenList = new LinkedList<>();//减少的
List<Map<String, Object>> changeList = new LinkedList<>();
List<Map<String, Object>> unchangedList = new LinkedList<>();//没有改变的
for (int j = 0; j < fromRest.size(); j++) {//遍历 来源数据库 对比 更新数据库,如果没有就添加,统计重复和不重复
Map<String, Object> fromTemp = fromRest.get(j);
String fromPriId = fromTemp.get(fromPRI).toString();//主键
if (ObjectUtil.isNull(updateMap.get(fromPriId))) {
addList.add(fromTemp);
} else {
Map<String, Object> updateTemp = updateMap.get(fromPriId);
boolean changeSign = false;//改变标志 false 没有改变,true 改变了
for (int i = 0; i < fromFieldAll.length; i++) {
String fromField = fromFieldAll[i];
String updateField = fromFieldAll[i];
if (!StrUtil.isEmpty(fromField) && !StrUtil.isEmpty(updateField) && !StrUtil.isEmpty(fromTemp.get(fromField).toString()) && !updateTemp.get(updateField).toString().equals(fromTemp.get(fromField).toString())) {
changeSign = true;
}
}
if (changeSign == true) {//有改变
changeList.add(fromTemp);
} else {
unchangedList.add(updateTemp);
}
}
}
for (Map<String, Object> updateTemp : updateRest) {//减少的
String priId = updateTemp.get(fromPRI).toString();
if (ObjectUtil.isNull(formMap.get(priId))) {
lessenList.add(updateTemp);
}
}
System.out.println(addList);
System.out.println(lessenList);
System.out.println(changeList);
System.out.println(unchangedList);
List<Object> params = new ArrayList<Object>();
for (int i = 0; i < addList.size(); i++) {//增加的
Map<String, Object> tempMap1 = addList.get(i);
params = new ArrayList<>();
String inseterSql = "INSERT INTO "+update_table_name+"(" + update_field + ") VALUES ";
inseterSql=stitchingQuestion(inseterSql,update_field);
for (String field : updateFieldAll) {
if (ObjectUtil.isNull(tempMap1.get(field)) || StrUtil.isEmpty(tempMap1.get(field).toString())) {
params.add(null);
} else {
params.add(tempMap1.get(field));
}
}
System.out.println(inseterSql);
int sum = updateDB.executeUpdate(inseterSql, params);
}
for (int i = 0; i < lessenList.size(); i++) {//删除
Map<String, Object> tempMap1 = lessenList.get(i);
params = new ArrayList<>();
String sql = "delete from " + update_table_name + " where " + updatePRI + "= ?";
params.add(tempMap1.get(updatePRI));
int sum = updateDB.executeUpdate(sql, params);
}
for (int i = 0; i < changeList.size(); i++) {//相同的
Map<String, Object> tempMap1 = changeList.get(i);
params = new ArrayList<>();
String sql = "UPDATE " + update_table_name + " SET ";
sql = updateSqlAppendOn(sql, fromFieldAll, updateFieldAll, tempMap1, params);
sql += " where " + updatePRI + "= '" + tempMap1.get(updatePRI) + "'";
System.out.println(sql);
int sum = updateDB.executeUpdate(sql, params);
}
}
}
/**
* 组装sql中的 ? 语句
* @param sql
* @param fieldStr
*/
public static String stitchingQuestion(String sql,String fieldStr){
sql+="( ";
String[] tempSplit = fieldStr.split(",");
for (int i1 = 0; i1 < tempSplit.length; i1++) {
sql+="?,";
}
sql=sql.substring(0,sql.length()-1)+")";
return sql;
}
/**
* 根据 数据库和表名 获取表的主键
*
* @return
*/
public static String getPRI(TempDBUtils tempDBUtils, String table_schema, String table_name) {
String sql = "SELECT COLUMN_NAME FROM information_schema.columns WHERE COLUMN_KEY='PRI' ";
List<Object> params = new ArrayList<>();
try {
if (!StrUtil.isEmpty(table_schema)) {
sql += " and table_schema=?";
params.add(table_schema);
}
if (!StrUtil.isEmpty(table_name)) {
sql += " and table_name=?";
params.add(table_name);
}
List<Map<String, Object>> result = tempDBUtils.executeQuery(sql, params);
if (null != result && result.size() != 0) {
return result.get(0).get("COLUMN_NAME").toString();
}
} catch (SQLException e) {
e.printStackTrace();
return "";
}
return "";
}
/**
* 拼装更新 sql On
*
* @param sql
* @param fromFieldArr 来源的字段
* @param updateFieldArr 更新的字段
* @param paramMap 参数map
* @return
*/
public static String updateSqlAppendOn(String sql, String[] fromFieldArr, String[] updateFieldArr, Map<String, Object> paramMap, List<Object> paramList) {
int sqlLength = sql.length();
if (updateFieldArr.length > 0) {
for (int i = 0; i < updateFieldArr.length; i++) {
String fromParm = fromFieldArr[i];
String updateParam = updateFieldArr[i];
Object paramObj = paramMap.get(fromParm);
if (!StrUtil.isEmpty(updateParam) && !ObjectUtil.isNull(paramObj) && !StrUtil.isEmpty(paramObj.toString())) {
sql += updateParam + "=?,";
paramList.add(paramObj);
} else {
continue;
}
}
if (sql.endsWith(",")) {
sql = sql.substring(0, sql.length() - 1);
}
}
return sql;
}
}
对应sql
sync_config.sql
-- ----------------------------
-- Table structure for sync_config
-- ----------------------------
DROP TABLE IF EXISTS `sync_config`;
CREATE TABLE `sync_config` (
`table_name_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '这次同步的表格主键名称,为了区分一个表可能同步几次,所以没有做联合主键',
`from_table_schema` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '来源数据库的db名称',
`update_table_schema` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '将要更新的db名称',
`from_table_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '来源table的名称',
`update_table_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '将要更新table的名称',
`from_field` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '来源数据的table名称',
`update_field` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '将要更新数据的table名称',
`from_db_config` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '来源db配置,账号###密码###URL',
`update_db_config` text CHARACTER SET utf8 COLLATE utf8_general_ci COMMENT '更新db配置,账号###密码###URL',
PRIMARY KEY (`table_name_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- ----------------------------
-- Records of sync_config
-- ----------------------------
INSERT INTO `sync_config` VALUES ('city_1', 'nm_shared_info', 'nm_shared_info', 'city', 'city', 'city_id,city_full_name,city_short_name,province_id,order_in_grp,status,create_time,created_by,last_updated_by,last_updated_time', 'city_id,city_full_name,city_short_name,province_id,order_in_grp,status,create_time,created_by,last_updated_by,last_updated_time', 'name###password###jdbc:mysql://11.11.11.11:3306/nm_shared_info?characterEncoding=utf-8', 'name###password###jdbc:mysql://10.180.5.15:3306/nm_shared_info?characterEncoding=utf-8');
TempDBUtils.java
package com.cter.util;
import org.apache.commons.lang.StringUtils;
import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
public class TempDBUtils {
public static String mysql_driver="jdbc1.driver=net.sf.log4jdbc.DriverSpy";
// 表示定义数据库的用户名
private String USERNAME ;
// 定义数据库的密码
private String PASSWORD ;
// 定义数据库的驱动信息
private String DRIVER ;
private String URL ;
// 定义数据库的链接
private Connection con = null;
// 定义sql语句的执行对象
private PreparedStatement pstmt = null;
// 定义查询返回的结果集合
private ResultSet resultSet = null;
public TempDBUtils(String USERNAME, String PASSWORD, String DRIVER, String URL) {
this.USERNAME = USERNAME;
this.PASSWORD = PASSWORD;
this.DRIVER = DRIVER;
this.URL = URL;
try {
Class.forName(this.DRIVER);
// System.out.println("注册驱动成功!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
if(con==null||con.isClosed()){
con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public TempDBUtils(String USERNAME, String PASSWORD, String URL) {
this.USERNAME = USERNAME;
this.PASSWORD = PASSWORD;
this.DRIVER = "com.mysql.jdbc.Driver";
this.URL = URL;
try {
Class.forName(this.DRIVER);
// System.out.println("注册驱动成功!");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
if(con==null||con.isClosed()){
con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 完成对数据库的表的添加删除和修改的操作
*
* @param sql
* @param params
* @return
* @throws SQLException
*/
public int executeUpdate(String sql, List<Object> params) throws SQLException {
boolean flag = false;
int result = 0;
pstmt = con.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
int index = 1;
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
result = pstmt.executeUpdate();
return result;
}
/**
* 从数据库中查询数据
*
* @param sql sql
* @param params ? 参数设值
* @return
* @throws SQLException
*/
public List<Map<String, Object>> executeQuery(String sql,
List<Object> params) throws SQLException {
List<Map<String, Object>> list= new LinkedList<Map<String, Object>>();
// List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = con.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
return list;
}
/**
* 从数据库中查询数据 关闭连接 处理异常
*
* @param sql sql
* @param params ? 参数设值
* @return
* @throws SQLException
*/
public List<Map<String, Object>> executeQueryCE(String sql, List<Object> params) {
List<Map<String, Object>> list= null;
try {
list = new LinkedList<Map<String, Object>>();
// List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
int index = 1;
pstmt = con.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
Map<String, Object> map = new HashMap<String, Object>();
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
map.put(cols_name, cols_value);
}
list.add(map);
}
this.closeDB();
} catch (SQLException e) {
e.printStackTrace();
}
return list;
}
/**
* 处理了异常 并且关闭了连接
* @param sql sql
* @param params 参数
* @param classz 返回的实体类型
* @return
*/
public <T> List<T> executeQueryByRefTExc(String sql, List<Object> params , Class<T> classz) {
List<T> list=new ArrayList<T>();
try {
list= (List<T>) executeQueryByRef(sql, params, classz);
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeDB();
}
if(list!=null && list.size()>0){
return list;
}
return null;
}
/**
* jdbc的封装可以用反射机制来封装,把从数据库中获取的数据封装到一个类的对象里
*
* @param sql
* @param params
* @param cls
* @return
* @throws Exception
*/
public <T> List<T> executeQueryByRef(String sql, List<Object> params, Class<T> cls) throws Exception {
List<T> list = new ArrayList<T>();
int index = 1;
pstmt = con.prepareStatement(sql);
if (params != null && !params.isEmpty()) {
for (int i = 0; i < params.size(); i++) {
pstmt.setObject(index++, params.get(i));
}
}
resultSet = pstmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int cols_len = metaData.getColumnCount();
while (resultSet.next()) {
T resultObject = cls.newInstance(); // 通过反射机制创建实例
for (int i = 0; i < cols_len; i++) {
String cols_name = metaData.getColumnName(i + 1);
Object cols_value = resultSet.getObject(cols_name);
if (cols_value == null) {
cols_value = "";
}
Field field;
try {
field= cls.getDeclaredField(cols_name);
} catch (Exception e) {
field = cls.getDeclaredField( initcapCol(cols_name));
}
// System.out.println(cols_value+"\t"+cols_name+"\t"+field.getType());
field.setAccessible(true); // 打开javabean的访问private权限
//如果返回没有值的时候BigDecimal和Long类型会转为String,所以要新加个对应的值
if(StringUtils.isBlank(cols_value.toString())){
if(field.getType().toString() .indexOf("BigDecimal")>-1){
field.set(resultObject, new BigDecimal(0));
}else if(field.getType().toString() .indexOf("Long")>-1 ){
field.set(resultObject, 0L);
}else if(field.getType().toString() .indexOf("Date")>-1 ){
field.set(resultObject, null);
}
} else{
field.set(resultObject, cols_value);
}
}
list.add(resultObject);
}
return list;
}
/**
* 功能:将输入字符串的下划线后的字母改成大写
* @param str
* @return
*/
private String initcapCol(String str) {
String[] arr= str.split("_");
String tempStr ="";
if(arr.length>1){
int i=0;
for(String st:arr){
if(i>0){
char[] c = st.toCharArray();
if(c[0] >= 'a' && c[0] <= 'z'){
c[0] = (char)(c[0] - 32);
}
tempStr += new String(c);
}else{
tempStr += st;
}
i++;
}
}else{
tempStr = str;
}
return tempStr;
}
/**
* 查询分页后数据
* @param sql sql语句
* @param params 参数
* @param classz calss类型
* @param page 第几页开始
* @param limit 一页多少条
* @return
* @throws Exception
*/
public <T> List<T> loadPage(String sql, List<Object> params, Class<T> classz, int page, int limit) throws Exception {
if(page!=0&&limit!=0){
sql=sql +" limit "+((page-1)*limit)+","+limit;
}
return executeQueryByRef(sql, params, classz);
}
/**
* 根据sql查询分页 关闭了连接
* @param sql
* @param params 占位符的参数
* @param classz 实体类额类型
* @param page 第几页开始
* @param limit 第几条
* @return
*/
public <T> List<T> loadPageTExc(String sql, List<Object> params , Class<T> classz, int page, int limit) {
List<T> list=new ArrayList<T>();
try {
if(page!=0&&limit!=0){
sql=sql +" limit "+((page-1)*limit)+","+limit;
}
return executeQueryByRef(sql, params, classz);
} catch (Exception e) {
e.printStackTrace();
}finally{
this.closeDB();
}
if(list!=null && list.size()>0){
return list;
}
return null;
}
/**
*
* @param sql
* @param params
* @return
* @throws Exception
*/
public int findCount(String sql, List<Object> params ) throws Exception {
sql="select count(*) from ("+sql+" ) a";
List<Map<String, Object>> list=executeQuery(sql, params);
return Integer.valueOf(String.valueOf(list.get(0).get("count(*)")));
}
/**
* 关闭连接
*/
public void closeDB() {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (con != null) {
try {
con.close();
con=null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* map对象转换为实体类
* @param map map实体类对象包含属性
* @param clazz 对象实体类类型
* @return
*/
public static Object Map2Object (Map<String, Object> map, Class<?> clazz){
if(map==null){
return null;
}
Object obejct=null;
try {
obejct=clazz.newInstance();
Field[] fields=obejct.getClass().getDeclaredFields();
for (Field field:fields){
int mod=field.getModifiers();
if(Modifier.isStatic(mod)|| Modifier.isFinal(mod)){
continue;
}
field.setAccessible(true);
field.set(obejct, map.get(field.getName()));
}
} catch (Exception e) {
e.printStackTrace();
}
return obejct;
}
public static void main(String[] args) {
Long strat= System.currentTimeMillis();
String username="";
String password="";
String url="jdbc:mysql://11.11.11.11:3306/nm_shared_info?characterEncoding=utf-8";
String driver="com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils=new TempDBUtils(username,password,driver,url);
String sql = "select ip,full_name from devices ";
List<Map<String, Object>> list=new ArrayList<Map<String, Object>>();
Map<String, Map<String, Object>> map=new LinkedHashMap<String, Map<String, Object>>();
List<Object> params=new ArrayList<Object>();
list= tempDBUtils.executeQueryCE(sql,params);
for(Map<String, Object> o:list){
map.put(o.get("full_name").toString(),o);
// System.out.println(JSONUtil.toJsonStr(o));
}
Long end= System.currentTimeMillis();
System.out.println((end-strat)/1000.00+"秒");
}
/**
* 关闭连接
* 根据语句和参数 和dbUtils 返回更新受影响行数
* @param db
* @param sql
* @param params
* @return
*/
public static int executeUpdate(TempDBUtils db , String sql, List<Object> params ) {
int i=0;
try {
i= db.executeUpdate(sql, params);
} catch (Exception e) {
e.printStackTrace();
} finally {
db.closeDB();
}
return i;
}
/**
* 关闭连接
* 根据语句和参数 和dbUtils 返回查询结果
* @param db
* @param sql
* @param params
* @return
*/
public static <T> List<T> executeQueryByRef(TempDBUtils db , String sql, List<Object> params, Class<T> Class ) {
int i=0;
List<T> list=new ArrayList<>();
try {
list= db.executeQueryByRef(sql, params, Class);
} catch (Exception e) {
e.printStackTrace();
} finally {
db.closeDB();
}
return list;
}
/**
* 获取 localhost 连接
* @return
*/
public static TempDBUtils getLocalhostConnection(){
String username = "";
String password = "";
String url = "jdbc:mysql://localhost:3306/external_system?characterEncoding=utf-8";
String driver = "com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils = new TempDBUtils(username, password, driver, url);
return tempDBUtils;
}
/**
* @return
*/
public static TempDBUtils getIDCConnection(){
String username = "";
String password = "";
String url = "jdbc:mysql://11.11.11.11:3306/external_system?characterEncoding=utf-8";
String driver = "com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils = new TempDBUtils(username, password, driver, url);
return tempDBUtils;
}
/**
* @return
*/
public static TempDBUtils getNoteBook(String dbName){
String username = "";
String password = "";
String url = "jdbc:mysql://11.11.11.111:3306/external_system?characterEncoding=utf-8";
String driver = "com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils = new TempDBUtils(username, password, driver, url);
return tempDBUtils;
}
/**
*
* @return
*/
public static TempDBUtils getZabbixConnection(){
String username = "";
String password = "";
String url = "jdbc:mysql://11.11.11.11:3306/zabbix?characterEncoding=utf-8";
String driver = "com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils = new TempDBUtils(username, password, driver, url);
return tempDBUtils;
}
/**
*
* @return
*/
public static TempDBUtils getNoteBookConnection(String dbName){
String username = "";
String password = "";
String url = "jdbc:mysql://11.11.11.11:3306/"+dbName+"?characterEncoding=utf-8";
String driver = "com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils = new TempDBUtils(username, password, driver, url);
return tempDBUtils;
}
/**
* 获取 cb2 连接
* @return
*/
public static TempDBUtils getTrunckBC2Connection(String dbName){
String username = "";
String password = "";
String url = "jdbc:mysql://11.11.11.11:3306/"+dbName+"?characterEncoding=utf-8";
String driver = "com.mysql.jdbc.Driver";
TempDBUtils tempDBUtils = new TempDBUtils(username, password, driver, url);
return tempDBUtils;
}
}
pom
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.25</version>
</dependency>
<!--hutool 工具集合引入-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.1.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>