一、
Table or Result To ArrayList
将数据源
connA
中的
tableName
表的内容查询出来放到一个
ArrayList
中。
private static ArrayList TableToArrayList(String tableName, Connection connA) {
Statement stmt = null;
ArrayList list = new ArrayList();
try {
stmt = connA.createStatement();
String sql = "select * from " + tableName;
System.out.println(sql);
ResultSet result = stmt.executeQuery(sql);
int columnCount = result.getMetaData().getColumnCount();//
列数。
Integer[] columnType = new Integer[columnCount];//
列的数据类型的数字表示。
for (int i = 0; i < columnCount; i++) {
columnType[i] = new Integer(result.getMetaData().getColumnType(
i + 1));
}
list.add(columnType);
while (result.next()) {
Object[] columnValue = new Object[columnCount];//
列值。
for (int i = 0; i < columnCount; i++) {
columnValue[i] = result.getObject(i + 1);
}
list.add(columnValue);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
if (connA != null)
connA.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
二、
ArrayList To Table
将
list
中的内容插入到数据源
connB
的
tableName
表中。
private static void ArrayListToTable(ArrayList list, Connection connB,
String tableName) {
Statement stmt = null;
PreparedStatement pstmt = null;
try {
stmt = connB.createStatement();
//
把
list
中的数据插入到表
tableName
中
.
//
如果
tableName
表中已经有数据,去掉数据。
String querySql = "Select * from " + tableName;
stmt = connB.createStatement();
ResultSet rs = stmt.executeQuery(querySql);
if (rs.next()) {
String deleteSql = "delete from " + tableName;
stmt.execute(deleteSql);
}
Integer[] columnType = (Integer[]) list.get(0);
Object[] columnValue = null;
int columnCountNew = columnType.length;
StringBuffer preSql = new StringBuffer("");
for (int i = 0; i < columnCountNew - 1; i++) {
preSql.append("?,");
}
preSql.append("?");
String insertSql = "insert into " + tableName + " values("
+ preSql + ")";
System.out.println("pre insertSql is:" + insertSql);
pstmt = connB.prepareStatement(insertSql);
System.out.println("ParameterCount
:
"
+ pstmt.getParameterMetaData().getParameterCount());
// insert the data by row.
for (int i = 1; i < list.size(); i++) {
columnValue = (Object[]) list.get(i);
int type, j;
for (j = 0; j < columnCountNew; j++) {
type = columnType[j].intValue();
int temp = j + 1;
if (columnValue[j] == null) {
pstmt.setNull(temp, type);
} else {
switch (type) {
case java.sql.Types.BIGINT:
pstmt.setLong(temp, (Long) columnValue[j]);
break;
case java.sql.Types.INTEGER:
pstmt.setLong(temp, (Long) columnValue[j]);
break;
case java.sql.Types.FLOAT:
pstmt.setFloat(temp, (Float) columnValue[j]);
break;
case java.sql.Types.DOUBLE:
pstmt.setDouble(temp, (Double) columnValue[j]);
break;
case java.sql.Types.DATE:
pstmt.setDate(temp, (Date) columnValue[j]);
break;
case java.sql.Types.TIME:
pstmt.setTime(temp, (Time) columnValue[j]);
break;
case java.sql.Types.TIMESTAMP:
pstmt
.setTimestamp(temp,
(Timestamp) columnValue[j]);
break;
default:
pstmt.setString(temp, columnValue[j].toString());
break;
}
}
}
System.out.println("The row " + i + " :" + insertSql);
pstmt.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
if (connB != null)
connB.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
三、表的拷贝,从一个数据源到另一个数据源。
private static void importData(String tableName, Connection connA,
Connection connB) {
ArrayList list = new ArrayList();
list = TableToArrayList(tableName, connA);
ArrayListToTable(list, connB, tableName);
}