某些情況下，對大字段類型的表，需要通過代碼來遷移數據。在這里記錄一下以前使用的代碼片段。 代碼片段：
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; }}新聞熱點
疑難解答
