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();
                }
        }

	}

}