转载请注明:http://blog.csdn.net/qq_27762917/article/details/75425767
最近在做项目中写了好几次数据库中的表数据对接问题，发现可以提取出一个通用的方法，在此记录一下
方法中的缺点就是两个数据库中的字段要人为地匹配，如果字段过多的话，可能匹配起来有些麻烦
实现的功能是同一数据库中的两个表数据对接和跨数据库的对接
主要运用的原理就是反射获取实体类中的getXXX()和setXXX()方法
Source和Taget继承的Entity是公司封装的框架里的实体类，用的时候其实不需要
/**
* 数据对接工具类<br/>
* 功能1:数据库内部传输全部数据数据。<br/>
* 此时Source是资源数据表对应的实体类，Target 是目标数据表对应的实体类<br/>
* 功能2:跨数据库传输每日更新的增量数据。<br/>
* 此时Source是根据资源数据库中表的字段在本地创建的为了接收查询记录创建的中间类，Target是本地数据库中需要导入数据的表对应的实体类<br/>
*
* @param <Source>
* 资源数据表对应实体类
* @param <Target>
* 目标数据表对应实体类
*/
public class SyncDataUtils<Source extends Entity<?>, Target extends Entity<?>> {
private static final String GET = "get";
private static final String SET = "set";
private static final String INT = "int";
private static final String DOUBLE = "double";
private static final String LONG = "long";
private static final String FLOAT = "float";
private static final String BOOLEAN = "boolean";
//这里用的公司的写好的工具注入的
//具体实现就不贴出来了
//可以用ApplicationContext自己写下很简单
@Resource
private static SyncDataDaoImpl syncDataDaoImpl = SpringContextHolder.getBean(SyncDataDaoImpl.class);
public Source source;
public Target target;
public SyncDataUtils(Source source, Target target) {
this.source = source;
this.target = target;
}
/**
* 实现数据库内的全部数据对接<br/>
* 其中资源表需要的字段数组中的顺序与目标表需要的字段数组中的顺序必须一一对应且两数组长度相同（即人为进行字段的匹配）<br/>
* 数组中的名称一定要与对应实体类中的 getXXX() 或者setXXX() 方法中的字段名一致,不能加入没有get/set方法对应的字段
*
* @param targetTable
* 目标数据表表名
* @param sourceProperties
* 资源表需要的字段
* @param targetProperties
* 目标表需要的字段
* @param fileName
* 数据库配置文件名称
*/
public void syncAllData(String targetTable, String[] sourceProperties,
String[] targetProperties, String fileName) {
if (sourceProperties.length != targetProperties.length)
return;
List<Source> sourceList = getSourceAllData();
List<Target> targetList = transformData(sourceList, sourceProperties, targetProperties);
batchInsert(targetList, fileName, targetTable, targetProperties);
}
/**
* 实现跨数据库的每日增量数据传输，字段数组的要求与全量数据相同。<br/>
* 数据库配置文件中必须要有目标数据库的 jdbcUrl (以 mydb2.url 命名)
*
* @param sourceTable
* 目标数据表表名
* @param targetTable
* 资源数据表表名
* @param sourceProperties
* 资源表需要的字段
* @param targetProperties
* 目标表需要的字段
* @param fileName
* 数据库配置文件名称(数据库配置文件中必须要有目标数据库的 jdbcUrl (以 mydb2.url 命名))
* @param date
* 默认为执行此方法时的前一天
* @param datePropName
* 目标表数据创建日期字段的名称
*/
public void syncPartData(String sourceTable, String targetTable,
String[] sourceProperties, String[] targetProperties,
String fileName, Date date, String datePropName) {
if (sourceProperties.length != targetProperties.length)
return;
List<Source> sourceList = getSourcePartData(fileName, sourceTable, sourceProperties, date, datePropName);
List<Target> targetList = transformData(sourceList, sourceProperties, targetProperties);
batchInsert(targetList, fileName, targetTable, targetProperties);
}
/**
* 获取资源表的所有数据
*
* @return
*/
@SuppressWarnings("unchecked")
private List<Source> getSourceAllData() {
return (List<Source>) syncDataDaoImpl.getSourceAllData(source.getClass());
}
/**
* 获取资源表每日的更新数据
*
* @param fileName
* @param sourceTable
* @param sourceProperties
* @param date
* @param datePropName
* @return
*/
private List<Source> getSourcePartData(String fileName, String sourceTable,
String[] sourceProperties, Date date, String datePropName) {
return syncDataDaoImpl.getSourcePartData(fileName, sourceTable,sourceProperties, source, date, datePropName);
}
/**
* 将数据批量插入目标表中
*
* @param targetList
* @param fileName
* @param targetTable
* @param targetProperties
*/
private void batchInsert(List<Target> targetList, String fileName,
String targetTable, String[] targetProperties) {
syncDataDaoImpl.batchInsert(targetList, target.getClass(),"db.properties", targetTable, targetProperties);
}
/**
* 把从资源表中查到的所有数据与目标表的字段匹配赋值
*
* @param sourceList
* @param sourceProperties
* @param targetProperties
* @return 字段匹配复制后的目标表对应实体类的数据
*/
@SuppressWarnings("unchecked")
private List<Target> transformData(List<Source> sourceList,
String[] sourceProperties, String[] targetProperties) {
List<Target> targetList = new LinkedList<Target>();
try {
for (Source entity : sourceList) {
for (int i = 0; i < sourceProperties.length; i++) {
// 获取资源实体类中的get方法
Method sourceMethod = entity.getClass().getDeclaredMethod(
GET + sourceProperties[i]);
Object sourceProperty = sourceMethod.invoke(entity);
String type = sourceMethod.getGenericReturnType().toString();
Method targetMethod;
/*
* 根据资源数据表对应实体类中的getXXX()方法返回值类型获取目标数据表对应实体类setXXX()方法中的参数类型
* 以下的if判断中一部分添加try/catch是为了防止资源实体类中的getXXX方法返回值为封装类
* 而目标实体类中对应匹配字段的getXXX()方法的返回值可能为其对应的基本数据类型
*/
if (type.endsWith("String")) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], String.class);
targetMethod.invoke(target, sourceProperty);
continue;
}
if (type.endsWith("Integer") || INT.equals(type)) {
try {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i],
type.endsWith("Integer") ? Integer.class : int.class);
targetMethod.invoke(target, sourceProperty);
} catch (NoSuchMethodException e) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], int.class);
targetMethod.invoke(target, sourceProperty);
}
continue;
}
if (type.endsWith("Long") || LONG.equals(type)) {
try {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i],
type.endsWith("Long") ? Long.class : long.class);
targetMethod.invoke(target, sourceProperty);
} catch (NoSuchMethodException e) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], long.class);
targetMethod.invoke(target, sourceProperty);
}
continue;
}
if (type.endsWith("Double") || DOUBLE.equals(type)) {
try {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i],
type.endsWith("Double") ? Double.class : double.class);
targetMethod.invoke(target, sourceProperty);
} catch (NoSuchMethodException e) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], double.class);
targetMethod.invoke(target, sourceProperty);
}
continue;
}
if (type.endsWith("Float") || FLOAT.equals(type)) {
try {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i],
type.endsWith("Float") ? Float.class : float.class);
targetMethod.invoke(target, sourceProperty);
} catch (NoSuchMethodException e) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], float.class);
targetMethod.invoke(target, sourceProperty);
}
continue;
}
if (type.endsWith("Date")) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], Date.class);
targetMethod.invoke(target, sourceProperty);
continue;
}
if (type.endsWith("Boolean") || BOOLEAN.equals(type)) {
try {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i],
type.endsWith("Boolean") ? Boolean.class : boolean.class);
targetMethod.invoke(target, sourceProperty);
} catch (NoSuchMethodException e) {
targetMethod = target.getClass().getDeclaredMethod(
SET + targetProperties[i], boolean.class);
targetMethod.invoke(target, sourceProperty);
}
continue;
}
}
targetList.add(target);
target = (Target) target.getClass().newInstance();
}
} catch (Exception e) {
e.printStackTrace();
}
return targetList;
}
}
下面是SyncDataDaoImpl
这里面关于数据库文件的配置命名方式可以自己修改，但是如果实现跨数据库传输的话，必须要把资源表的数据库配置信息也写上
@Repository下面是声明的接口
public class SyncDataDaoImpl implements SyncDataDao {
private static final String GET = "get";
private static final String SET = "set";
private static final String ALL_DATA_URL = "mydb.url";
private static final String PART_DATA_URL = "mydb2.url";
private static final String USER = "mydb.username";
private static final String PASSWORD = "mydb.password";
private static final String DRIVER_CLASS_NAME = "mydb.driverClassName";
private static final String INT = "int";
private static final String DOUBLE = "double";
private static final String LONG = "long";
private static final String FLOAT = "float";
private static final String BOOLEAN = "boolean";
private final Logger logger = Logger.getLogger(SyncDataDaoImpl.class);
@Override
public <T extends Entity<?>> List<T> getSourceAllData(Class<T> sourceClass) {
//这里用的是公司封装的框架写的，就不贴出来了
//实现的功能很简单，就是查询资源表中的所有数据
//写个SQL语句一查就出来了
}
@SuppressWarnings("unchecked")
@Override
public <T> List<T> getSourcePartData(String fileName, String sourceTable,
String[] sourceProperties, T t, Date date, String datePropName) {
List<T> list = new LinkedList<T>();
String sql = " SELECT * FROM " + sourceTable + " WHERE 1 = 1 AND "
+ datePropName + " >= ? AND " + datePropName + " < ? ";
Connection connection = null;
PreparedStatement prest = null;
ResultSet resultSet = null;
try {
// 获取数据库连接
Properties properties = PropertiesLoaderUtils.loadAllProperties(fileName);
Class.forName(properties.getProperty(DRIVER_CLASS_NAME));
connection = DriverManager.getConnection(
properties.getProperty(PART_DATA_URL),
properties.getProperty(USER),
properties.getProperty(PASSWORD));
connection.setAutoCommit(false);
prest = connection
.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
prest.setString(1, DatetimeFactory.getDateStartTime(date));
prest.setString(2, DatetimeFactory.getDateEndTime(date));
// 根据查询结果将数据插入 List中
resultSet = prest.executeQuery();
while (resultSet.next()) {
for (int i = 0; i < sourceProperties.length; i++) {
Method sourceGetMethod = t.getClass().getDeclaredMethod(
GET + sourceProperties[i]);
String type = sourceGetMethod.getGenericReturnType().toString();
Method sourceSetMethod;
// 根据查询结果对应实体类中的getXXX()方法的返回值类型type判断setXXX()方法的参数类型
if (type.endsWith("String")) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i], String.class);
sourceSetMethod.invoke(t, resultSet.getString(sourceProperties[i]));
continue;
}
if (type.endsWith("Integer") || type.endsWith(INT)) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i],
type.endsWith("Integer") ? Integer.class : int.class);
sourceSetMethod.invoke(t, resultSet.getInt(sourceProperties[i]));
continue;
}
if (type.endsWith("Double") || type.endsWith(DOUBLE)) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i],
type.endsWith("Double") ? Double.class : double.class);
sourceSetMethod.invoke(t, resultSet.getDouble(sourceProperties[i]));
continue;
}
if (type.endsWith("Long") || type.endsWith(LONG)) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i],
type.endsWith("Long") ? Long.class : long.class);
sourceSetMethod.invoke(t, resultSet.getLong(sourceProperties[i]));
continue;
}
if (type.endsWith("Float") || type.endsWith(FLOAT)) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i],
type.endsWith("Float") ? Float.class : float.class);
sourceSetMethod.invoke(t, resultSet.getFloat(sourceProperties[i]));
continue;
}
if (type.endsWith("Date")) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i], Date.class);
sourceSetMethod.invoke(t, resultSet.getDate(sourceProperties[i]));
continue;
}
if (type.endsWith("Boolean") || type.endsWith(BOOLEAN)) {
sourceSetMethod = t.getClass().getDeclaredMethod(
SET + sourceProperties[i],
type.endsWith("Boolean") ? Boolean.class : boolean.class);
sourceSetMethod.invoke(t, resultSet.getBoolean(sourceProperties[i]));
continue;
}
}
list.add(t);
t = (T) t.getClass().newInstance();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
resultSet.close();
prest.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
@SuppressWarnings("unchecked")
@Override
public <T extends Entity<?>> void batchInsert(List<?> targetList,
Class<T> targetClass, String fileName, String targetTable,
String[] targetProperties) {
targetList = (List<T>) targetList;
Connection connection = null;
PreparedStatement prest = null;
try {
// 拼接 SQL 插入语句
String sql = "INSERT INTO " + targetTable + " (";
for (int i = 0; i < targetProperties.length; i++) {
if (i == targetProperties.length - 1) {
sql += targetProperties[i] + ") VALUES (";
for (int j = 0; j < targetProperties.length; j++) {
if (j == targetProperties.length - 1) {
sql += "?)";
break;
}
sql += "?,";
}
break;
}
sql += targetProperties[i] + ",";
}
// 获取数据库连接
Properties properties = PropertiesLoaderUtils.loadAllProperties(fileName);
Class.forName(properties.getProperty(DRIVER_CLASS_NAME));
connection = DriverManager.getConnection(
properties.getProperty(ALL_DATA_URL),
properties.getProperty(USER),
properties.getProperty(PASSWORD));
connection.setAutoCommit(false);
prest = connection
.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
int k = 1;
for (int i = 0; i < targetList.size(); i++) {
T t = (T) targetList.get(i);
for (int j = 0; j < targetProperties.length; j++) {
Method targetMethod = t.getClass().getDeclaredMethod(
GET + targetProperties[j]);
String type = targetMethod.getGenericReturnType().toString();
// 根据getXXX()方法的返回值类型创建对应类型的返回值对象
if (type.endsWith("String")) {
String targetPropVal = (String) targetMethod.invoke(t);
prest.setString(k++, targetPropVal);
continue;
}
if (type.endsWith("Integer") || INT.equals(type)) {
Integer targetPropVal = (Integer) targetMethod.invoke(t);
prest.setInt(k++, targetPropVal);
continue;
}
if (type.endsWith("Double") || DOUBLE.equals(type)) {
Double targetPropVal = (Double) targetMethod.invoke(t);
prest.setDouble(k++, targetPropVal);
continue;
}
if (type.endsWith("Long") || LONG.equals(type)) {
Long targetPropVal = (Long) targetMethod.invoke(t);
prest.setLong(k++, targetPropVal);
continue;
}
if (type.endsWith("Float") || FLOAT.equals(type)) {
Float targetPropVal = (Float) targetMethod.invoke(t);
prest.setFloat(k++, targetPropVal);
continue;
}
if (type.endsWith("Date")) {
Date targetPropVal = (Date) targetMethod.invoke(t);
if (targetPropVal != null)
prest.setDate(k++,
new java.sql.Date(targetPropVal.getTime()));
else
prest.setDate(k++, null);
continue;
}
if (type.endsWith("Boolean") || BOOLEAN.equals(type)) {
Boolean targetPropVal = (Boolean) targetMethod.invoke(t);
prest.setBoolean(k++, targetPropVal);
continue;
}
}
prest.addBatch();
k = 1;
}
prest.toString();
prest.executeBatch();
connection.commit();
} catch (Exception e) {
logger.error("批量插入执行错误", e);
} finally {
try {
prest.close();
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
public interface SyncDataDao {
/**
* 获取资源表的所有数据
*
* @param sourceClass
* @return
*/
<T extends Entity<?>> List<T> getSourceAllData(Class<T> sourceClass);
/**
* 获取资源表每日的更新数据
*
* @param sourceClass
* @return
*/
<T> List<T> getSourcePartData(String fileName, String sourceTable,
String[] sourceProperties, T t, Date date, String datePropName);
/**
* 将数据批量插入目标表
*
* @param targetList
* @param sourceClass
* @param fileName
* @param targetTable
* @param targetProperties
*/
<T extends Entity<?>> void batchInsert(List<?> targetList,
Class<T> sourceClass, String fileName, String targetTable,
String[] targetProperties);
}