从一个数据库导入到另一个数据库中

package cn.dao; import java.io.BufferedWriter; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.Reader; import java.sql.Connection; import java.sql.DriverManager; 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.Iterator; import org.apache.commons.lang.StringUtils; import org.apache.log4j.Logger; import org.logicalcobwebs.proxool.configuration.JAXPConfigurator; /** * yicha * Dec 11, 2008 */ public class DataSql { private Logger logger=Logger.getLogger(DataSql.class); private Connection mysqlConn=null; private Connection conn = null; private Statement stat = null; private Statement mysqlStat=null; /** * jdbc链接 * @return */ public Connection getConn(){ String url="jdbc:microsoft:sqlserver://192.168.0.214:1433;DatabaseName=unionfee"; String user="unionfee"; String password="unionfee"; try{ Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver"); conn=DriverManager.getConnection(url, user, password); }catch(Exception e){ logger.error("Connecter=>getConnection error: "+e); } return conn; } /** * mysql 的jdbc链接 * @return */ public Connection getMysqlConn(){ String url="jdbc:mysql://192.168.0.225/unionfee?useUnicode=true&characterEncoding=utf8"; String user="root"; String password="root"; try{ Class.forName("com.mysql.jdbc.Driver"); mysqlConn=DriverManager.getConnection(url, user, password); }catch(Exception e){ logger.error("Connecter=>getConnection error: "+e); } return mysqlConn; } public Statement getStatement(){ try{ conn = this.getConn(); if(conn!=null)stat = conn.createStatement(); }catch(Exception e){ logger.error("Connecter=>getConnection error: "+e); } return stat; } public void close(){ try{ if(stat!=null)stat.close(); stat= null; }catch(Exception e){ logger.error("Connecter=>close statement error: "+e); } try{ if(conn!=null)conn.close(); conn = null; }catch(Exception e){ logger.error("Connecter=>close connection error: "+e); } } public ArrayList getTableData(String tableName) { // String tableName="t_ad_union_user"; String sql="select * from "+tableName; HashMap hashMap=null; ArrayList result=new ArrayList(); ResultSet rs; try { rs = this.getStatement().executeQuery(sql); //使用元数据获取一个表字段的总数 ResultSetMetaData rsmd=rs.getMetaData(); int coulum=rsmd.getColumnCount(); while(rs.next()){ hashMap=new HashMap(); // String[] array=new String[coulum]; for(int i=0;i<coulum;i++){ String columName=rsmd.getColumnName(i+1); hashMap.put(columName, rs.getString(i+1)); // logger.info(columName); // array[i]=rs.getString(i+1); //在数据库中可以插入日期，int,float,double,varchar // int columType=rsmd.getColumnType(i+1); // if(columType==java.sql.Types.INTEGER ){ // rs.getInt(i+1); // }else if(columType==java.sql.Types.BIGINT){ // rs.getInt(i+1); // }else if(columType==java.sql.Types.DATE){ // rs.getString(i+1); // } // else{ // rs.getString(i+1); // } } // result.add(array); result.add(hashMap); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return result; } public String[] DataToSql(ArrayList al,String tableName){ // String tableName="t_ad_union_user"; String[] result=new String[al.size()]; String sql=""; for(int i=0;i<al.size();i++){ String fieldSql=""; String valueSql=""; HashMap data=(HashMap)al.get(i); Iterator it=data.keySet().iterator(); while(it.hasNext()){ String field=it.next().toString(); String value=data.get(field).toString(); fieldSql+=field+","; valueSql+="'"+value+"',"; // logger.info(field); } //去除最后字段的最后一个,符号，去除值的最后两个',符号 fieldSql=fieldSql.substring(0, fieldSql.length()-1); valueSql=valueSql.substring(0, valueSql.length()-1); sql="insert into "+tableName+"("+fieldSql+") "+" values("+valueSql+");"; // logger.info(sql); // logger.info(fieldSql); result[i]=sql; } return result; } /** * 写入到文件中 * @param str * @param path */ public void stringWriteToTxt(String tableName){ String path="c:\\"+tableName+".txt"; String[] content=this.DataToSql(this.getTableData(tableName), tableName); BufferedWriter bw=null; String str=null; try { bw=new BufferedWriter(new FileWriter(path)); for(int i=0;i<content.length;i++){ str=content[i]+"\n"; bw.write(str); } System.out.println("写文件成功！"+path); } catch (IOException e) { System.out.println("写文件时有错误！"+path); e.printStackTrace(); }finally{ if(bw!=null){ try { bw.close(); } catch (IOException e) { e.printStackTrace(); } } } } public void getTableData1(String tableName) { // String tableName="t_ad_union_user"; String sql="select * from "+tableName; String mysqlSql="select * from "+tableName; ResultSet rs=null; ResultSet mysqlRs=null; String path="c:\\"+tableName+".txt"; BufferedWriter bw=null; mysqlConn=this.getMysqlConn(); int count=0; long oldTime=System.currentTimeMillis(); try { // bw=new BufferedWriter(new FileWriter(path)); rs = this.getStatement().executeQuery(sql); //使用元数据获取一个表字段的总数 ResultSetMetaData rsmd=rs.getMetaData(); int coulum=rsmd.getColumnCount(); while(rs.next()){ String fieldSql=""; String valueSql=""; for(int i=0;i<coulum;i++){//获取mssql的字段 String columName=rsmd.getColumnName(i+1); String field=columName; String value=rs.getString(i+1); if(value!=null && !value.equalsIgnoreCase("NULL")){ fieldSql+=field+","; // valueSql+="'"+value.replaceAll("\'", "\\\\'")+"',"; valueSql+="'"+value.replaceAll("\\\\'", "\\\\\\\\'")+"',"; } } //生成sql语句 fieldSql=fieldSql.substring(0, fieldSql.length()-1); valueSql=valueSql.substring(0, valueSql.length()-1); sql="insert into "+tableName+"("+fieldSql+") "+" values("+valueSql+");"; mysqlStat.executeUpdate(sql); // if(mysqlConn.createStatement().executeUpdate(sql)>0){ // logger.info((count++)+"success"); // }else{ // logger.info("false"); // } //把sql语句写到文件中 // bw.write(sql+"\n"); // logger.info(sql); } } catch (Exception e) { logger.error("SQLException有错误！"+sql,e); } finally{ try { if(mysqlStat!=null) mysqlStat.close(); if(rs!=null) rs.close(); if(bw!=null) bw.close(); if(mysqlConn!=null) mysqlConn.close(); if(conn!=null) conn.close(); } catch (Exception ex) { logger.error("关闭conn时出错", ex); } } long now=System.currentTimeMillis(); logger.info("生成完毕,共花费了"+(now-oldTime)/1000+"s时间"); } /** * 数据库转移到mysql上，mysqlTableName上的字段必须和msFields的字段一一对应。 * @param mysqlTableName 目标数据库表 * @param tableName 源数据库表 * @param msFields tableName表中的字段 */ public void getTableData2(String mysqlTableName,String tableName,String[] msFields) { // String tableName="t_ad_union_user"; String sql="select * from "+tableName; String mysqlSql="select * from "+mysqlTableName; ResultSet rs=null; ResultSet mysqlRs=null; String path="c:\\"+mysqlT