JDBC远程从一个MySql数据库中的一张表里面读出数据(这个数据库需要用SSH隧道连接,大约8W条数据),然后分别插入到另一个数据库中的两张表里
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(); } } } }