java实现oracle和sqlserver数据库转换（包含大数据）

package com.data.controller; import java.sql.Connection; import java.sql.ResultSet; import java.sql.ResultSetMetaData; 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 com.main.ConnectDB; import com.tools.StringUtil; public class GenaralController { private String names=null; private String tablespace=null; public GenaralController(){ } public GenaralController(String names,String tablespace){ this.names=names; this.tablespace=tablespace; } private Connection con=null; private Connection conn=null; private Statement sqlserverstmt=null; private Statement oraclestmtt=null; private ResultSet rsset=null; private ResultSet rs=null; private Statement oraclestmt=null; ResultSetMetaData md=null; @SuppressWarnings("static-access") public void displayDbProperties(){ ArrayList<String> tablesList = new ArrayList<String>(); ArrayList<String> tablesNeeded = new ArrayList<String>(); Map<Integer,String> flag=new HashMap<Integer,String>(); List<String>col_namesNoLong=new ArrayList<String>(); List<String>idlist=new ArrayList<String>(); Integer j=-1; String[]tbnames=names.split(","); for(String s:tbnames){ tablesNeeded.add(s); } try { con=new ConnectDB().getConnection("oracle"); conn=new ConnectDB().getConnection("sqlserver"); //创建statement对象 oraclestmt=con.createStatement(); sqlserverstmt=conn.createStatement(); //查询ILANNT表空间下所有表 StringBuffer sql=new StringBuffer("select * from all_tables where tablespace_name='"); sql.append(tablespace) .append("' "); ResultSet rsmd=oraclestmt.executeQuery(sql.toString()); while(rsmd.next()){ String tableName=rsmd.getString(2); tablesList.add(tableName); } for(String tablename:tablesList){ if(tablesNeeded.contains(tablename)){ //执行查询命令，结果放到ResultSet String sql4select="select * from "+tablename; rs=null; oraclestmt=null; oraclestmt=con.createStatement(); rs= oraclestmt.executeQuery(sql4select); idlist.clear(); while(rs.next()){ idlist.add(rs.getString("id")); } //得到表相关信息 md=null; md=rs.getMetaData(); //取得表中的列数量 int no_cols=md.getColumnCount(); System.out.println(tablename+"表列数量："+no_cols); String []col_names=new String[no_cols]; //取得ResultSet对象中各字段名称 col_namesNoLong.clear(); for(int i=0;i<no_cols;i++){ col_names[i]=md.getColumnLabel(i+1); if(!"LONG".equalsIgnoreCase(md.getColumnTypeName(i+1))){ col_namesNoLong.add(md.getColumnLabel(i+1)); } } //创建表结构 String[][]tableStructure=new String[no_cols+1][3]; tableStructure[0][0]=tablename; j=-1; flag.clear(); for (int i = 1; i <=no_cols; i++) { String columnName=md.getColumnLabel(i); String columnType=convert2columnType(md.getColumnTypeName(i),String.valueOf(md.getColumnDisplaySize(i)))[0]; String columnSize=convert2columnType(md.getColumnTypeName(i),String.valueOf(md.getColumnDisplaySize(i)))[1]; tableStructure[i][0]=columnName; tableStructure[i][1]=columnType; tableStructure[i][2]=columnSize; if("LONG".equalsIgnoreCase(md.getColumnTypeName(i))){ j=i; flag.put(j,md.getColumnLabel(i)); } } //在sqlserver中创建新表 StringBuffer sql4createtable=new StringBuffer("create table "+tablename+" ("); for (int i = 1; i <=no_cols; i++) { if(i<no_cols && !"".equals(tableStructure[i][2])){ sql4createtable.append(" "+tableStructure[i][0]+" "+tableStructure[i][1]+"("+tableStructure[i][2]+"),"); } if(i<no_cols && "".equals(tableStructure[i][2])){ sql4createtable.append(" "+tableStructure[i][0]+" "+tableStructure[i][1]+","); } if(i==no_cols && !"".equals(tableStructure[i][2])){ sql4createtable.append(" "+tableStructure[i][0]+" "+tableStructure[i][1]+"("+tableStructure[i][2]+")") .append(" )"); } if(i==no_cols && "".equals(tableStructure[i][2])){ sql4createtable.append(" "+tableStructure[i][0]+" "+tableStructure[i][1]) .append(" )"); } } System.out.println(sql4createtable); //if EXISTS( select 1 from sysobjects where name='WS_COLUMN' and type = 'u') DROP TABLE WS_COLUMN StringBuffer sql4dropcreatable=new StringBuffer("if EXISTS( select 1 from sysobjects where name='"); sql4dropcreatable.append(tablename+"' and type = 'u')") .append(" DROP TABLE ") .append(tablename); sqlserverstmt.execute(sql4dropcreatable.toString()); sqlserverstmt.execute(sql4createtable.toString()); //记录为Long类型的字段名和位置 /// //oraclestmt.close(); //con.close(); //cono=new ConnectDB().getConnection("oracle"); //oraclestmtt=cono.createStatement(); oraclestmtt=null; oraclestmtt=con.createStatement(); //重新读取数据 StringBuffer sqlx=new StringBuffer("select "); for(int i=1;i<=col_names.length;i++){ if(i<col_names.length && i!=j){ sqlx.append(col_names[i-1]+","); } if(i==col_names.length && i==j){ sqlx.deleteCharAt(sqlx.length()-1); } if(i==col_names.length && i!=j){ sqlx.append(col_names[i-1]); } } sqlx.append(" from "+tablename); System.out.println(sqlx); rsset=null; rsset=oraclestmtt.executeQuery(sqlx.toString()); while(rsset.next()){ StringBuffer sql4Insert=new StringBuffer("insert into "+tablename+"("); for(int i=0;i<col_namesNoLong.size();i++){ sql4Insert.append(col_namesNoLong.get(i)+","); } sql4Insert.deleteCharAt(sql4Insert.length()-1); sql4Insert.append(") values( "); for(int k=1;k<=col_namesNoLong.size();k++){ if(k<col_namesNoLong.size()){ if(rsset.getString(k)==null || "".equals(rsset.getString(k))){ sql4Insert.append("'"+null+"',"); }else{ sql4Insert.append("'"+StringUtil.Encode(rsset.getString(k))+"',"); } } if(k==col_namesNoLong.size()){ if(rsset.getString(k)==null || "".equals(rsset.getString(k))){ sql4Insert.append("'"+null+"')"); }else{ sql4Insert.append("'"+StringUtil.Encode(rsset.getString(k))+"')"); } } } System.out.println(sql4Insert); sqlserverstmt.execute(sql4Insert.toString()); } /** ResultSet rsset=oraclestmtt.executeQuery(sqlx.toString()); //打印每行数据列中各字段数据 while(rsset.next()){ //向新建的表里插入数据 StringBuffer sql4Insert=new StringBuffer("insert into "+tablename+" values("); for(int i=1;i<=col_names.length;i++){ if(i<col_names.length){ if(rsset.getObject(col_names[i-1])==null || "".equals(rsset.getObject(col_names[i-1]))){ sql4Insert.append("'"+null+"',"); }else{ sql4Insert.append("'"+rsset.getObject(col_names[i-1])+"',"); } } if(i==col_names.length){ if(rsset.getObject(col_names[i-1])==null || "".equals(rsset.getObject(col_names[i-1]))){ sql4Insert.append("'"+null+"')"); }else{ sql4Insert.append("'"+rsset.getObject(col_names[i-1])+"')"); } } } System.out.println(sql4Insert); sqlserverstmt.execute(sql4Insert.toString());