数据迁移,Oracle,Mysql,Sql server,数据库数据传输

package com.lzjs.jup.action.test;import java.io.FileNotFoundException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;/** * Oracle MySql Sql Server 数据传输 * @author lvtao * @date   2017年11月1日 */public class Tset {public static void main(String[] args) throws FileNotFoundException, SQLException {getConnection();getSqlConnection();tableInput();}/** * 连接Oracle数据库 * @return */public static Connection getConnection() {Connection conn = null;String DRIVER = "oracle.jdbc.driver.OracleDriver";String url = "jdbc:oracle:thin:@192.168.10.200:1521:ORCL";String user = "DATA_ANALYSIS";String password = "123456";try {Class.forName(DRIVER);// 加载数据库驱动程序} catch (ClassNotFoundException e) {e.printStackTrace();}try {conn = DriverManager.getConnection(url, user, password);// 获得Connection对象} catch (SQLException e) {e.printStackTrace();}return conn;}/** * 连接MySql数据库 * @return */public static Connection getSqlConnection() {Connection conn = null;String DRIVER = "com.mysql.jdbc.Driver";String url = "jdbc:mysql://localhost:3306/test1";String user = "root";String password = "root";try {Class.forName(DRIVER);// 加载数据库驱动程序} catch (ClassNotFoundException e) {e.printStackTrace();}try {conn = DriverManager.getConnection(url, user, password);// 获得Connection对象} catch (SQLException e) {e.printStackTrace();}return conn;}/** * 连接Sql Srever数据库 * @return */public static Connection getSerConnection() {Connection conn = null;String DRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbname";String user = "root";String password = "root";try {Class.forName(DRIVER);// 加载数据库驱动程序} catch (ClassNotFoundException e) {e.printStackTrace();}try {conn = DriverManager.getConnection(url, user, password);// 获得Connection对象} catch (SQLException e) {e.printStackTrace();}return conn;}/** * 查询方法 * @return * @throws FileNotFoundException * @throws SQLException */public static List<List<String>> tableInput(){List<List<String>> FindList = new ArrayList<List<String>>();Connection con = getConnection();PreparedStatement pre = null;ResultSet resultSet = null;String sql = "select VC_ID,VC_CONTENT from SYN_MAIL_INFO ";try {pre = con.prepareStatement(sql);resultSet = pre.executeQuery();String[] columu = { "VC_ID", "VC_CONTENT"};System.out.println("查询完毕!");int i = 0;while (resultSet.next()) {List<String> minList = new ArrayList<String>();for (String each : columu) {minList.add(resultSet.getString(each));}FindList.add(minList);i++;if(i % 1000 == 0){//每次提交1000条数据                executeManySql(FindList);                FindList.removeAll(FindList);                System.out.println("已插入"+i+"条数据!");            }}executeManySql(FindList);//剩余数据System.out.println("插入完毕!"+"共"+i+"条数据!");return FindList;} catch (SQLException e) {e.printStackTrace();} finally {try {pre.close();} catch (SQLException e) {e.printStackTrace();}try {con.close();} catch (SQLException e) {e.printStackTrace();}}return null;}/** * 插入方法 * @param FindList * @throws SQLException */@SuppressWarnings("unused")public static void executeManySql(List<List<String>> FindList) throws SQLException {    Connection conn = getSqlConnection();    conn.setAutoCommit(false);    Statement stat = null;    PreparedStatement pst = (PreparedStatement) conn.prepareStatement("insert into sqltest values (?,?)");    for (List<String> minList: FindList) {        for(int i=0;i<minList.size();i++){            pst.setString(i+1, minList.get(i));        }        // 把一个SQL命令加入命令列表        pst.addBatch();    }    // 执行批量更新    pst.executeBatch();    // 语句执行完毕，提交本事务    conn.commit();    pst.close();    conn.close();//一定要记住关闭连接，不然mysql回应为too many connection自我保护而断开。}}