Java通过ssh连接访问Mysql数据库

java可通过JSch实现ssh的端口转发, 从而实现数据库的连接

实现方式:

1.本地和远程建立ssh连接通道;
2.设置ssh本地端口转发，本地转发到远程;
3.通过访问本地的转发端口，实现和远程的数据库建立连接

实现

1. 建立ssh连接通道依赖

  <dependency>
        <groupId>com.jcraft</groupId>
        <artifactId>jsch</artifactId>
  <version>0.1.53</version>

2. 参数条件准备

package com.sync;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

/**
 * ConnectionEntity
 *
 * @author xiezb
 * @version 1.0
 * @description 链接实体
 * @date 2022/9/17 10:34
 */
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ConnectionEntity implements java.io.Serializable{
    private static final long serialVersionUID = 1L;
    // 服务器登录名
    private String sshUser = "sshUser ";
    // 登陆密码
    private String sshPassword = "sshPassword ";
    // 服务器公网IP
    private String sshHost = "xx.xx.xx.xx";
    // 跳板机ssh开放的接口   默认端口 22, 填写跳板机开放端口
    private Integer sshPort = 22;

    // 要访问的mysql所在的host    服务器局域网IP（127.0.0.1也行）
    private String remoteHost = "xx.xx.xx.xx";
    // 要访问的mysql所在的port
    private Integer remotePort = 3306;
    // mysql username
    private String remoteUser = "mysqlroot";
    // mysql password
    private String remotePassword = "mysqlpassword";

    // 映射本地的 local prot,这个是本地的端口，很重要！！！选取一个没有占用的port即可
    private Integer localPort = 33071;
    // 映射本地的 local ip
    private String localIp = "127.0.0.1";

}
 

3. 建立通道连接工具

package com.sync;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import com.platform.exception.BusinessException;
import lombok.NonNull;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * SSHConnection
 *
 * @author xiezb
 * @version 1.0
 * @description 连接类：SSHConnectionw
 * @date 2022/9/16 15:54
 */
@Slf4j
public class SSHConnection {
    // SSH Session
    Session session = null;

    // MYSQL connection
    Connection connection = null;

    private volatile boolean isConnected = false;

    private static String driverName_5= "com.mysql.jdbc.Driver";

    private static String driverName_8= "com.mysql.cj.jdbc.Driver";

    public String getDriverNameByType(String driverType) {
        if ("0".equals(driverType)) {
            return driverName_5;
        }
        if ("1".equals(driverType)) {
            return driverName_8;
        }
        throw new RuntimeException("驱动类型不支持...");
    }

    /**
     * 建立SSH连接
     */
    public void SSHConnection(ConnectionEntity connection) throws Exception {
        try {
            JSch jsch = new JSch();
            session = jsch.getSession(connection.getSshUser(), connection.getSshHost(), connection.getSshPort());
            session.setPassword(connection.getSshPassword());
            session.setConfig("StrictHostKeyChecking", "no");
            // 日志打印自己脑补
            session.connect();
            int assinged_port = session.setPortForwardingL(connection.getLocalPort(), connection.getRemoteHost(), connection.getRemotePort());
            System.out.println("localhost:" + assinged_port + " -> " + connection.getRemoteHost() + ":" + connection.getRemotePort());
            System.out.println("Port Forwarded");
        } catch (Exception e) {
            // do something
            e.printStackTrace();
            closeSSH();
        }
    }

    /**
     * 断开SSH连接
     */
    public void closeSSH() throws Exception {
        this.session.disconnect();
    }

    public void mysql5SSH(ConnectionEntity connectionEntity, String dbName) {
        String url = "jdbc:mysql://"+ connectionEntity.getLocalIp() +":"+connectionEntity.getLocalPort()+"/"+dbName;
        mysqlSSH(connectionEntity, "0", url);
    }

    public void mysqlSSH(ConnectionEntity connectionEntity, String driverNameType, String url) {
        try {
            //Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
            SSHConnection(connectionEntity);
            System.out.println("Connected");
            //mysql database connectivity
            String driverName = getDriverNameByType(driverNameType);
            System.out.println(driverName);
            Class.forName(driverName).newInstance();
            // String url = "jdbc:mysql://"+ connectionEntity.getLocalIp() +":"+connectionEntity.getLocalPort()+"/"+dbName;
            log.info(url);
            this.connection = DriverManager.getConnection(url, connectionEntity.getRemoteUser(), connectionEntity.getRemotePassword());
            System.out.println("MYSQL Connected..");
            this.isConnected = true;
        } catch (Exception e) {
            e.printStackTrace();
            closeConnection();
        }
    }

    public List<Map<String, Object>> executeQuery(@NonNull String sql, @NonNull final List<String> fields) {
        if (!this.isConnected) {
            log.info("Reconnect...");
            return null;
        }
        log.info("===> Start Run sql: {}", sql);
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            int fetchSize = resultSet.getFetchSize();
            final List<Map<String, Object>> list = new ArrayList<>(fetchSize > 0 ? fetchSize : 5);
            int total = 0;
            // 5.处理结果遍历要查询的数据
            while (resultSet.next()) {
                // 一条记录
                Map<String, Object> map = new LinkedHashMap<>(fields.size());
                fields.stream().filter(field -> field != null && field.trim().length() > 0).forEach(field -> {
                    try {
                        map.put(field.trim(), resultSet.getObject(field.trim()));
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                });
                total++;
                list.add(map);
            }
            log.info("ResultSet total: {}", total);
            resultSet.close();
            preparedStatement.close();
            return list;
        } catch (Exception e) {
            log.error("Execute sql error.", e);
            closeConnection();
        } finally {
            log.info("<=== End Run sql.");
        }
        return null;
    }

    public void closeConnection() {
        try {
            this.isConnected = false;
            if (connection != null && !connection.isClosed()) {
                System.out.println("Closing Database Connection");
                connection.close();
            }
            if (session != null && session.isConnected()) {
                System.out.println("Closing SSH Connection");
                session.disconnect();
            }
        } catch (SQLException e) {
            log.error("关闭连接错误");
            throw new RuntimeException(e);
        }
    }
}
 

4. 简单测试

 public static void main(String[] args) {
        // 获取数据库连接
        SSHConnection sshConnection = new SSHConnection();
        // build ConnectionEntity()参数对象, 数据库: test01
        sshConnection.mysql5SSH(new ConnectionEntity(), "test01");
         // 1. 获取测试库test01中的userb表用户信息
        List<Map<String, Object>> query = sshConnection.executeQuery("select * from user", Arrays.asList("id", "name"));
        // 自己的逻辑操作
        System.out.println(JSONObject.toJSONString(query));
        // 关闭资源连接
        sshConnection.closeConnection();
    }