针对数据库中含大字段类型字段的表,进行数据迁移

2/22/2017来源:ASP.NET技巧人气:3010

某些情况下,对大字段类型的表,需要通过代码来迁移数据。在这里记录一下以前使用的代码片段。 代码片段:

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; } }