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自我保护而断开。
}
}