某些情况下,对大字段类型的表,需要通过代码来迁移数据。在这里记录一下以前使用的代码片段。
代码片段:
package sqlserver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
public class SqlServerTest {
public static Connection getOrclConnection2() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver")
.newInstance();
String URL = "jdbc:oracle:thin:@192.168.11.251:1521:orcl";
String USER = "TEST1";
String PASSWORD = "1";
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
return conn;
} catch (Exception ce) {
ce.printStackTrace();
}
return null;
}
public static Connection getOrclConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver")
.newInstance();
String URL = "jdbc:oracle:thin:@192.168.11.251:1521:orcl";
String USER = "TEST2";
String PASSWORD = "1";
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
return conn;
} catch (Exception ce) {
ce.printStackTrace();
}
return null;
}
/*public static Connection getMsConnection() {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
.newInstance();
String URL = "jdbc:sqlserver://192.168.11.251:1433;DatabaseName=test_data";
String USER = "TEST3";
String PASSWORD = "1";
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
return conn;
} catch (Exception ce) {
ce.printStackTrace();
}
return null;
}*/
/**
* @param args
*/
public static void main(String[] args) throws Exception{
String fsql = "select * from htmlinfo";
Connection orclConn = getOrclConnection();
PreparedStatement ps = orclConn.prepareStatement(fsql);
ResultSet frs = ps.executeQuery();
ResultSetMetaData rsmd = ps.getMetaData();
int size = rsmd.getColumnCount();
List<Map<String, Object>> tbl = new ArrayList<Map<String, Object>>();
StringBuilder questionStr = new StringBuilder();
while(frs.next()){
StringBuilder tsql = new StringBuilder("insert into htmlinfo (");
StringBuilder questions = new StringBuilder();
Map<String, Object> rowMap = new LinkedHashMap<String, Object>();
//遍历列信息
for(int i = 1; i <= size ;i++){
FieldInfo fi = new FieldInfo();
String colName = rsmd.getColumnName(i);
String type = rsmd.getColumnTypeName(i);
Object value = frs.getObject(colName);
fi.setName(colName);
fi.setType(type);
fi.setValue(value);
if(value != null){
tsql.append(colName);
tsql.append(",");
questions.append("?,");
}
rowMap.put(colName, fi);
}
tsql.deleteCharAt(tsql.length() - 1);
questions.deleteCharAt(questions.length() - 1);
tsql.append(") values (");
tsql.append(questions);
tsql.append(")");
rowMap.put("sql", tsql.toString());
tbl.add(rowMap);
}
for(Map<String, Object> rowMap : tbl){
Connection msConn = getOrclConnection2();//getMsConnection();
PreparedStatement tps = null;
String sql = (String) rowMap.get("sql");
tps = msConn.prepareStatement(sql.toString());
Iterator<Entry<String, Object>> it = rowMap.entrySet().iterator();
int k = 1;
for(int i = 0; i < rowMap.size()-1; i++){
Entry<String, Object> entry = it.next();
FieldInfo fi = (FieldInfo) entry.getValue();
Object value = fi.getValue();
String name = fi.getName();
if(isNotEmpty(value)){
tps.setObject(k++, value);
}
}
System.err.println("执行插入语句------" + sql.toString());
tps.execute();
msConn.close();
}
orclConn.close();
System.out.println("导入成功!");
}
public static boolean isNotEmpty(Object str){
if(str != null && !"".equals(str)){
return true;
}
return false;
}
}
class FieldInfo {
String name;
String type;
Object value;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public Object getValue() {
return value;
}
public void setValue(Object value) {
this.value = value;
}
}