JDBC遠程從一個MySql資料庫中的一張表裡面讀出資料（這個資料庫需要用SSH隧道串連，大約8W條資料），然後分別插入到另一個資料庫中的兩張表裡

標籤：blog   java   os   io   資料   for   ar   div   

package com.eeepay.lzj.db;import java.sql.Connection;import java.sql.Date;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.jcraft.jsch.JSch;import com.jcraft.jsch.Session;public class ChangeDB {public static int lport = 33102;//本地連接埠（隨便取）      public static String rhost = "172.***.***.***";//遠程MySQL伺服器      public static int rport = 3306;//遠程MySQL服務連接埠        public static void go() {String user = "***";//SSH串連使用者名稱String password = "******";//SSH串連密碼String host = "120.132.***.***";//SSH伺服器int port = *****;//SSH訪問連接埠try {JSch jsch = new JSch();Session session = jsch.getSession(user, host, port);session.setPassword(password);session.setConfig("StrictHostKeyChecking", "no");session.connect();System.out.println(session.getServerVersion());//這裡列印SSH伺服器版本資訊int assinged_port = session.setPortForwardingL(lport, rhost, rport);System.out.println("localhost:" + assinged_port + " -> " + rhost + ":" + rport);} catch (Exception e) {e.printStackTrace();}}public static void main(String[] args) {String mobileNo;//mobile_username    String accountName;//account_name    String accountNo;//account_no    String cnaps;//cnaps_no    String bankName;//bank_name        String realName;//lawyer    int status;//open_status    String password;//mobile_password    String idCard;//id_card_no    int realNameAuth;//real_flag    Date createTime;go();try {            //1、載入驅動            Class.forName("com.mysql.jdbc.Driver");        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        //2、建立串連        Connection conn = null;        Connection conn2 = null;         try {        conn = DriverManager.getConnection("jdbc:mysql://localhost:33102/posp", "****", "***");        conn2 = DriverManager.getConnection("jdbc:mysql://115.29.***.***:3306/bag", "****", "*****");        } catch (SQLException e) {            System.out.println("未串連上資料庫");            e.printStackTrace();        }        PreparedStatement pstmt = null;        PreparedStatement pstmt2 = null;        PreparedStatement pstmt22 = null;        PreparedStatement pstmt3 = null;        try {            String sql = "select mobile_username,account_name,account_no,cnaps_no,bank_name,lawyer,open_status,mobile_password,id_card_no,real_flag,create_time from pos_merchant";        //String sql = "select * from settle_account where id=1";            String sql2 = "insert into bag_login(mobile_no,status,real_name,create_time,password,pay_password,idcard,real_name_auth) values(?,?,?,?,?,?,?,?)";            String sql22 = "select * from bag_login where mobile_no=?";            String sql3 = "insert into settle_account(mobile_no,account_name,account_no,cnaps,bank_name,create_time) values(?,?,?,?,?,?)";            pstmt = conn.prepareStatement(sql);            pstmt2 = conn2.prepareStatement(sql2);            pstmt22 = conn2.prepareStatement(sql22);            pstmt3 = conn2.prepareStatement(sql3);            ResultSet rs = pstmt.executeQuery();            int i=1;            while(rs.next()){            System.out.println(i++);            createTime = rs.getDate("create_time");            mobileNo = rs.getString("mobile_username");            accountName = rs.getString("account_name");            accountNo = rs.getString("account_no");            cnaps = rs.getString("cnaps_no");            bankName = rs.getString("bank_name");            realName = rs.getString("lawyer");            status = rs.getInt("open_status");            password = rs.getString("mobile_password");            idCard = rs.getString("id_card_no");            realNameAuth = rs.getInt("real_flag");            pstmt3.setString(1, mobileNo);            pstmt3.setString(2, accountName);            pstmt3.setString(3, accountNo);            pstmt3.setString(4, cnaps);            pstmt3.setString(5, bankName);            pstmt3.setDate(6, createTime);            pstmt3.execute();                        pstmt22.setString(1, mobileNo);            if(!pstmt22.execute()){            pstmt2.setString(1, mobileNo);                pstmt2.setInt(2, status);                pstmt2.setString(3, realName);                pstmt2.setDate(4, createTime);                pstmt2.setString(5, password);                pstmt2.setString(6, password);                pstmt2.setString(7, idCard);                pstmt2.setInt(8, realNameAuth);                pstmt2.execute();            }                        }                     } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } finally{             try {                    pstmt.close();                    conn.close();                    pstmt2.close();                    conn2.close();                    pstmt3.close();                                     } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }        }}}