package com.product.data.sync.util;
|
|
import cn.hutool.core.util.IdUtil;
|
import com.product.common.utils.spring.SpringUtils;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.data.sync.config.SystemCode;
|
import com.product.util.BaseUtil;
|
import org.apache.commons.lang3.StringUtils;
|
|
import java.lang.reflect.InvocationTargetException;
|
import java.lang.reflect.Method;
|
import java.sql.*;
|
import java.util.*;
|
import java.util.Date;
|
|
public class DataManipulationUtils {
|
|
/**
|
* 获取jdbc连接
|
*
|
* @param diver jdbc连接类型
|
* @param jdbc 连接url
|
* @param user 用户名
|
* @param pwd 密码
|
* @return JDBC连接
|
* @throws BaseException
|
*/
|
public static Connection getConnection(String diver, String jdbc, String user, String pwd) throws ClassNotFoundException, SQLException {
|
Class.forName(diver);
|
Connection connection = DriverManager.getConnection(jdbc, user, pwd);
|
return connection;
|
}
|
|
/**
|
* 关闭数据库的连接
|
*
|
* @param rs 结果集
|
* @param stmt 执行sql的类
|
* @param con JDBC连接
|
* @throws SQLException
|
*/
|
public static void close(ResultSet rs, Statement stmt, Connection con) throws SQLException {
|
if (rs != null)
|
rs.close();
|
if (stmt != null)
|
stmt.close();
|
if (con != null && !con.isClosed())
|
con.close();
|
}
|
|
/**
|
* 查询数据总条数
|
* 主要通过map里面表名和条件查询条数
|
*
|
* @param com 连接
|
* @param map 参数map
|
* @return 数据总条数
|
* @throws SQLException
|
*/
|
public static Integer getResultSetRow(Connection com, Map<String, String> map) throws SQLException {
|
StringBuffer sql = new StringBuffer();
|
//源数据表名
|
String dataOriginName = map.get("dataOriginName");
|
//查询条件
|
String syncCondition = map.get("syncCondition");
|
sql.append(" SELECT count(*) FROM ").append(dataOriginName);
|
if (!BaseUtil.strIsNull(syncCondition)) {
|
sql.append(" WHERE ").append(syncCondition);//参数失败 改为字符串拼接
|
}
|
PreparedStatement ps = com.prepareStatement(sql.toString());
|
ResultSet rs = ps.executeQuery();
|
while (rs.next()) {
|
return rs.getInt(1);
|
}
|
return 0;
|
}
|
|
/**
|
* 查询数据
|
*
|
* @param com JDBC连接
|
* @param map 查询必备的Map
|
* @return
|
* @throws SQLException
|
*/
|
public static ResultSet getResultSet(Connection com, Map<String, String> map) throws SQLException {
|
String type = map.get("databaseType");
|
if ("mysql".equals(type)) {
|
return DataManipulationUtils.getMysqlResultSet(com, map);
|
} else if ("oracle".equals(type)) {
|
return DataManipulationUtils.getOracleResultSet(com, map);
|
} else if ("sqlserver".equals(type)) {
|
return DataManipulationUtils.getSqlServerResultSet(com, map);
|
} else if ("informix".equals(type)) {
|
return DataManipulationUtils.getInforMixResultSet(com, map);
|
} else {
|
throw new SQLException(SystemCode.SYSTEM_UNKNOWN_DATABASE_TYPE.getValue(), SystemCode.SYSTEM_UNKNOWN_DATABASE_TYPE.getText());
|
}
|
}
|
|
public static void main(String[] args) {
|
long l = System.currentTimeMillis();
|
Set<String> uuids = new HashSet<>();
|
int data = 1;
|
for (int i = 0; i < 10000; i++) {
|
String s = IdUtil.randomUUID();
|
uuids.add(s);
|
data++;
|
if (data % 1000 == 0) {
|
System.out.println(System.currentTimeMillis() - l);
|
l = System.currentTimeMillis();
|
}
|
}
|
System.out.println(uuids.size());
|
}
|
|
/**
|
* MySql查询数据
|
*
|
* @param con 连接
|
* @param map 表名集参数
|
* @return 数据集
|
* @throws SQLException
|
*/
|
public static ResultSet getMysqlResultSet(Connection con, Map<String, String> map) throws SQLException {
|
String sql = map.get("sql");
|
Integer currentPage = Integer.parseInt(map.get("currentPage"));
|
Integer pageSize = Integer.parseInt(map.get("pageSize"));
|
Integer start = (currentPage - 1) * pageSize;
|
sql = sql.replaceFirst("[?]", map.get("fileName"));
|
sql = sql.replaceFirst("[?]", map.get("dataOriginName"));
|
sql = sql.replaceFirst("[?]", String.valueOf(start));
|
sql = sql.replaceFirst("[?]", String.valueOf(pageSize));
|
SpringMVCContextHolder.getSystemLogger().info(sql);
|
PreparedStatement ps = con.prepareStatement(sql);
|
ResultSet resultSet = ps.executeQuery();
|
return resultSet;
|
}
|
|
/**
|
* SqlServer查询数据
|
*
|
* @param con 连接
|
* @param map 表名集参数
|
* @return 数据集
|
* @throws SQLException
|
*/
|
public static ResultSet getSqlServerResultSet(Connection con, Map<String, String> map) throws SQLException {
|
String sql = map.get("sql");
|
Integer currentPage = Integer.parseInt(map.get("currentPage"));
|
Integer pageSize = Integer.parseInt(map.get("pageSize"));
|
Integer start = (currentPage - 1) * pageSize;
|
sql = sql.replaceFirst("[?]", String.valueOf(pageSize));
|
sql = sql.replaceFirst("[?]", map.get("fileName"));
|
sql = sql.replaceFirst("[?]", map.get("dataOriginName"));
|
|
sql = sql.replaceFirst("[?]", String.valueOf(start));
|
sql = sql.replaceFirst("[?]", map.get("dataOriginName"));
|
SpringMVCContextHolder.getSystemLogger().info(sql);
|
PreparedStatement ps = con.prepareStatement(sql);
|
return ps.executeQuery();
|
}
|
|
/**
|
* InforMix查询数据
|
*
|
* @param con 连接
|
* @param map 表名集参数
|
* @return 数据集
|
* @throws SQLException
|
*/
|
public static ResultSet getInforMixResultSet(Connection con, Map<String, String> map) throws SQLException {
|
String sql = map.get("sql");
|
Integer currentPage = Integer.parseInt(map.get("currentPage"));
|
Integer pageSize = Integer.parseInt(map.get("pageSize"));
|
Integer start = (currentPage - 1) * pageSize;
|
sql = sql.replaceFirst("[?]", String.valueOf(start));
|
sql = sql.replaceFirst("[?]", String.valueOf(pageSize));
|
sql = sql.replaceFirst("[?]", map.get("fileName"));
|
sql = sql.replaceFirst("[?]", map.get("dataOriginName"));
|
SpringMVCContextHolder.getSystemLogger().info(sql);
|
PreparedStatement ps = con.prepareStatement(sql);
|
return ps.executeQuery();
|
}
|
|
/**
|
* Oracle查询数据
|
*
|
* @param con 连接
|
* @param map 表名集参数
|
* @return 数据集
|
* @throws SQLException
|
*/
|
public static ResultSet getOracleResultSet(Connection con, Map<String, String> map) throws SQLException {
|
String sql = map.get("sql");
|
Integer currentPage = Integer.parseInt(map.get("currentPage"));
|
Integer pageSize = Integer.parseInt(map.get("pageSize"));
|
Integer start = (currentPage - 1) * pageSize;
|
Integer end = currentPage * pageSize;
|
sql = sql.replaceFirst("[?]", map.get("fileName"));
|
sql = sql.replaceFirst("[?]", map.get("dataOriginName"));
|
sql = sql.replaceFirst("[?]", String.valueOf(end));
|
sql = sql.replaceFirst("[?]", String.valueOf(start));
|
SpringMVCContextHolder.getSystemLogger().info(sql);
|
PreparedStatement ps = con.prepareStatement(sql);
|
return ps.executeQuery();
|
}
|
|
//事件前方法
|
|
/**
|
* 反射调用方法
|
*
|
* @param invokeTarget 调用Bean的方法 bean.fun
|
* @param fse 传入的 FieldSetEntity
|
* @throws NoSuchMethodException
|
* @throws InvocationTargetException
|
* @throws IllegalAccessException
|
*/
|
public static void codeCalls(String invokeTarget, FieldSetEntity fse) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
|
//获取bean
|
String beanName = StringUtils.substringBeforeLast(invokeTarget, ".");
|
Object bean = SpringUtils.getBean(beanName);
|
//获取方法名
|
String methodName = StringUtils.substringAfterLast(invokeTarget, ".");
|
Method method = bean.getClass().getDeclaredMethod(methodName, FieldSetEntity.class);
|
method.invoke(bean, fse);
|
}
|
}
|
