数据迁移,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自我保护而断开。
	}
}