用 java 反射实现 的 jdbc 批量插入

package com.ibm.crl.gis.map.tools.load.data;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import com.ibm.crl.gis.map.tools.sql.DBField;
import com.ibm.crl.gis.map.tools.sql.ParameterType;
import com.ibm.crl.gis.map.tools.sql.ParseSqlFile;
import com.ibm.crl.gis.map.tools.sql.SqlManager;
import com.ibm.crl.gis.map.util.DBUtil;

public abstract class ImportDataToDB implements ImportDataToDBSupport {
private static final String SET = "set";
private static final String GET = "get";
SqlManager sqlm = SqlManager.getInstance();
ParseSqlFile sqlfile = ParseSqlFile.getInstance();

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#saveBatchObject
* (java.util.List, java.lang.String)
*/
public <T> List<T> saveBatchObject(List<T> listParam, String tableName) {
Connection conn = getDB2Connection();
try {
return saveBatchObject(listParam, tableName, conn);
} finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#queryForObject
* (java.lang.Class, java.lang.String, java.lang.String, java.lang.Object)
*/
public <T> T queryForObject(Class<T> ob, String tableName,
String tableFieldName, Object value) {
return queryObject(ob, tableName, tableFieldName, value).get(0);

}

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#queryObject
* (java.lang.Class, java.lang.String)
*/
public <T> List<T> queryObject(Class<T> ob, String tableName) {
return queryObject(ob, tableName, null, null);
}

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#queryObject
* (java.lang.Class, java.lang.String, java.lang.String, java.lang.Object)
*/
public <T> List<T> queryObject(Class<T> ob, String tableName,
String tableFieldName, Object value) {
List<T> list = new ArrayList<T>();
Connection conn = getDB2Connection();
try {
Statement stmt = conn.createStatement();
String sql = sqlm.getQuerySqlByTableName(tableName);
if (!StringUtils.isEmpty(tableFieldName)) {
sql += " where " + tableFieldName + "=" + value;
}
ResultSet set = stmt.executeQuery(sql);
List<DBField> fileds = sqlfile.getTableFields(tableName);
Map<String, Method> setMap = getMethod(set, GET);
while (set.next()) {
T object = (T) ob.newInstance();
Map<String, Method> map = getMethod(object, SET);
for (DBField dbf : fileds) {
map.get((SET + dbf.getFiledName()).toUpperCase()).invoke(
object,
setMap.get(
ParameterType.getGetType(dbf.getType())
.toUpperCase()).invoke(set,
dbf.getName()));
}
list.add(object);
}
set.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return list;

}

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#saveBatchObject
* (java.util.List, java.lang.String, java.sql.Connection)
*/
public <T> List<T> saveBatchObject(List<T> listParam, String tableName,
Connection con) {
PreparedStatement pre = null;
Long pkidfirst = null;
String pkidName = null;
try {
try {
List<DBField> list = sqlfile.getTableFields(tableName);
if (list != null && !list.isEmpty()) {
for (DBField field : list) {
if (field.isAutoValue()) {
pkidName = field.getName();
break;
}
}
}
if (pkidName != null) {
Statement comm = con.createStatement();
ResultSet set = comm.executeQuery("select case when max("
+ pkidName + " ) is null then 3000 else max("
+ pkidName + " ) end from " + tableName);
// FIXME pkId is repeat because you are propriety people.
if (set.next()) {
pkidfirst = set.getLong(1);
}
set.close();
comm.close();
}
con.setAutoCommit(false);
String sql = sqlm.getSaveSqlByTableName(tableName);
System.out.println(sql);
pre = con
.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
Map<String, Method> methMap = getMethod(pre, SET);
for (int k = 0; k < listParam.size(); k++) {
Object ob = listParam.get(k);
System.out.println("batch="
+ listParam.get(k).getClass().getSimpleName());
Map<String, Method> methpara = getMethod(ob, GET);
for (int i = 0; i < list.size(); i++) {
DBField field = list.get(i);
if (!field.isAutoValue()) {
Method method = methMap.get(ParameterType
.getSetType(field.getType()).toUpperCase());
Object fieldValue = methpara.get(
(GET + field.getFiledName()).toUpperCase())
.invoke(ob);
System.out.println(field.getFiledName()+",value="+fieldValue);
if (fieldValue != null
&& method.getParameterTypes().length == 2) {
method.invoke(pre,
pkidName == null ? i + 1 : i,
fieldValue);
} else {
method.invoke(pre,
pkidName == null ? i + 1 : i,
getDefaultValue(field.getType()));
}
}
}
pre.addBatch();
if (k % 100 == 0) {
pre.executeBatch();
con.commit();
}
}
pre.executeBatch();
con.commit();
if (pkidfirst != null) {
for (int i = 0; i < listParam.size(); i++) {
Object ob = listParam.get(i);
pkidName = pkidName.replaceAll("_", "");
getMethod(ob, SET)
.get((SET + pkidName).toUpperCase())
.invoke(
ob,
Long
.valueOf(pkidfirst == 3000 ? (i + pkidfirst)
: pkidfirst + i + 1));
}
}
} finally {
if (pre != null) {
pre.close();
}
}

} catch (SQLException e) {
e.printStackTrace();
try {
System.out.println(con.getAutoCommit());
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new RuntimeException(e);
} catch (IllegalArgumentException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (IllegalAccessException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
try {
con.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new RuntimeException(e);
} finally {
try {
con.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
}
return listParam;
}

private Object getDefaultValue(String fieldType) {
String type = ParameterType.getSetType(fieldType);
if (type != null
&& (type.contains("setLong") || type.contains("setInt") || type
.contains("setDouble"))) {
return 0;

} else {
return "";
}
}

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#saveObject(T,
* java.lang.String, java.sql.Connection)
*/
public <T> T saveObject(T parameter, String tableName, Connection con) {
PreparedStatement pre = null;
try {
try {
String sql = sqlm.getSaveSqlByTableName(tableName);
System.out.println(sql);
pre = con
.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
String pkidName = null;
List<DBField> list = sqlfile.getTableFields(tableName);
for (DBField field : list) {
if (field.isAutoValue()) {
pkidName = field.getFiledName();
break;
}
}
Map<String, Method> methMap = getMethod(pre, SET);
Map<String, Method> methpara = getMethod(parameter, GET);
for (int i = 0; i < list.size(); i++) {
DBField field = list.get(i);
if (!field.isAutoValue()) {
Method method = methMap.get(ParameterType.getSetType(
field.getType()).toUpperCase());
Object fieldValue = methpara.get(
(GET + field.getFiledName()).toUpperCase())
.invoke(parameter);
if (fieldValue != null
&& method.getParameterTypes().length == 2) {
method.invoke(pre, pkidName == null ? i + 1 : i,
fieldValue);
} else {
method.invoke(pre, pkidName == null ? i + 1 : i,
getDefaultValue(field.getType()));
}
} else {
pkidName = field.getFiledName();
}
}
pre.execute();
if (pkidName != null) {
Map<String, Method> methForSet = getMethod(parameter, SET);
ResultSet set = pre.getGeneratedKeys();
if (set.next()) {
methForSet.get((SET + pkidName).toUpperCase()).invoke(
parameter, set.getLong(1));
}
}
} finally {
if (pre != null) {
pre.close();
}
}
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (IllegalArgumentException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (IllegalAccessException e) {
e.printStackTrace();
throw new RuntimeException(e);
} catch (InvocationTargetException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
return parameter;
}

private Map<String, Method> getMethod(Object pre, String methodType) {
if (pre == null) {
return null;
}
Method[] method = pre.getClass().getMethods();
Map<String, Method> methMap = new HashMap<String, Method>();
for (int i = 0; i < method.length; i++) {
if (method[i].getName().startsWith(methodType)) {
if (methodType.equals(SET)
&& method[i].getParameterTypes().length != 3) {
methMap.put(method[i].getName().toUpperCase(), method[i]);
} else if (!methodType.equals(SET)) {
methMap.put(method[i].getName().toUpperCase(), method[i]);
}
}
}
return methMap;
}

/*
* (non-Javadoc)
*
* @see
* com.ibm.crl.gis.map.tools.load.data.ImportDataToDBSupport#getDB2Connection
* ()
*/
public Connection getDB2Connection() {
return DBUtil.getDB2Conn();
}

public static void main(String[] args) {
// ImportDataToDB df = new ImportDataToDB();
}
}