jdbc或springboot通过ssh连接数据库

备注

通过ssh连接的数据库通常需要部署到对应的环境才能连接，但这样就不方便本地debug，使用jsch就可以很方便的连接需要通过ssh连接的数据库了。

Maven

添加maven

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

一、jdbc通过ssh连接数据库

如果只是低频次的查询其它需要ssh连接的数据库，直接用jdbc比较方便

    /**
     * jdbc+JSch连接需要ssh连接的数据库
     * @param sql sql语句
     * @param sshHost 服务器地址
     * @param sshPort 服务器端口
     * @param sshUsername 服务器用户名
     * @param sshPassword 服务器密码
     * @param dbHost 数据库地址
     * @param dbPort 数据库端口
     * @param dbUsername 数据库用户名
     * @param dbPassword 数据库密码
     * @param dbName 数据库库名
     * @return 查询结果
     */
    public JSONArray connectionSshMysql(String sql,
                                        String sshHost, int sshPort, String sshUsername, String sshPassword,
                                        String dbHost, int dbPort, String dbUsername, String dbPassword, String dbName) {
        int localPort = 5678; // 本地临时端口
        try {
            // 1.建立SSH连接
            JSch jsch = new JSch();
            Session session = jsch.getSession(sshUsername, sshHost, sshPort);
            session.setPassword(sshPassword);
            session.setConfig("StrictHostKeyChecking", "no");
            session.connect();
            session.setPortForwardingL(localPort, dbHost, dbPort);
            // 2.建立数据库连接
            String dbUrl = "jdbc:mysql://127.0.0.1:" + localPort + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8";
            Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
            // 3.获取数据库操作对象
            Statement statement = connection.createStatement();
            // 4.执行sql
            ResultSet resultSet = statement.executeQuery(sql); // 查
            //int affectedRows = statement.executeUpdate(sql); // 增删改
            // 4.1将查询到的数据转为JSON字符串
            JSONArray jsonArray = new JSONArray();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnLabel(i);
                    String columnValue = resultSet.getString(i);
                    jsonObject.put(columnName, columnValue);
                }
                jsonArray.add(jsonObject);
            }
            // 5.关闭数据库连接
            connection.close();
            session.delPortForwardingL(localPort);
            session.disconnect();
            // 6.返回执行sql结果
            return jsonArray;
            //return affectedRows;
        } catch (JSchException | SQLException e) {
            e.printStackTrace();
            return new JSONArray();
        }
    }

二、springboot通过ssh连接数据库

如果是高频次的连接需要通过ssh连接的数据库，那么使用该方法比较合适

1.添加配置类

import org.springframework.context.annotation.Import;
import java.lang.annotation.*;

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE})
@Documented
@Import(SSHConfigDefinitionRegistrar.class)
public @interface EnableSSH {

    String[] profiles() default "default";

    String sshHost() default "";

    int sshPort() default 22;

    String sshUser() default "root";

    String sshPassword() default "";

    String remoteHost() default "";

    int remotePort() default 3306;

    int localPort() default 3307;

}

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import org.springframework.beans.factory.support.BeanDefinitionRegistry;
import org.springframework.beans.factory.support.BeanNameGenerator;
import org.springframework.context.EnvironmentAware;
import org.springframework.context.annotation.ImportBeanDefinitionRegistrar;
import org.springframework.core.env.Environment;
import org.springframework.core.type.AnnotationMetadata;
import org.springframework.core.type.StandardAnnotationMetadata;
import org.springframework.stereotype.Component;

import java.io.Closeable;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;
import java.util.Properties;

@Component
public class SSHConfigDefinitionRegistrar implements ImportBeanDefinitionRegistrar, EnvironmentAware {

    private static String activeProfile = "default";

    @Override
    public void setEnvironment(Environment environment) {
        String[] activeProfiles = environment.getActiveProfiles();
        if (activeProfiles != null && activeProfiles.length > 0) activeProfile = activeProfiles[0];
    }

    @Override
    public void registerBeanDefinitions(AnnotationMetadata metadata, BeanDefinitionRegistry registry, BeanNameGenerator importBeanNameGenerator) {
        System.out.println("开始连接");
        StandardAnnotationMetadata meta = (StandardAnnotationMetadata) metadata;
        Class<?> bootClass = meta.getIntrospectedClass();
        EnableSSH enableSSH = bootClass.getAnnotation(EnableSSH.class);
        if (enableSSH == null) return;
        String[] profiles = enableSSH.profiles();
        List<String> list = Arrays.asList(profiles);
        if (!list.contains(activeProfile)) {
            return;
        }
        SSHConnection connection = new SSHConnection(enableSSH, activeProfile);
        try {
            connection.connect();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        System.out.println("建立连接");
    }

    class SSHConnection implements Closeable {

        private EnableSSH config;
        private String profile;
        private Session session;

        public void connect() throws InterruptedException {
            String sshHost = config.sshHost();
            int sshPort = config.sshPort();
            String sshUser = config.sshUser();
            String sshPassword = config.sshPassword();
            int localPort = config.localPort();
            String remoteHost = config.remoteHost();
            int remotePort = config.remotePort();
            JSch jsch = new JSch();
            Thread thread = new Thread(() -> {
                try {
                    session = jsch.getSession(sshUser, sshHost, sshPort);
                    session.setPassword(sshPassword);
                    Properties config = new Properties();
                    config.put("StrictHostKeyChecking", "no");
                    session.setConfig(config);
                    session.connect();
                    session.setPortForwardingL(localPort, remoteHost, remotePort);
                } catch (JSchException e) {
                    e.printStackTrace();
                }
            });
            thread.start();
            Thread.sleep(1000*30);
        }

        public SSHConnection(EnableSSH enableSshConfigs, String profile) {
            this.config = enableSshConfigs;
            this.profile = profile;
        }

        @Override
        public void close() throws IOException {
            if (session != null) session.disconnect();
        }
    }
}

2.修改启动类

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import javax.annotation.PostConstruct;
import java.util.Properties;

@SpringBootApplication
@MapperScan("com.xxx.xxx.mapper")
public class Application {

    private static Session session;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    //用于本地测试时连接ssh数据库
    @PostConstruct
    public void init() {
        JSch jsch = new JSch();
        Thread thread = new Thread(() -> {
            try {
                session = jsch.getSession(ssh服务器用户名, ssh服务器地址, ssh服务器端口);
                session.setPassword(ssh服务器密码);
                Properties config = new Properties();
                config.put("StrictHostKeyChecking", "no");
                session.setConfig(config);
                session.connect();
                session.setPortForwardingL(自定义端口号, 数据库地址, 数据库端口);
            } catch (JSchException e) {
                e.printStackTrace();
            }
        });
        thread.start();
    }
}

3.修改配置文件

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:自定义端口号/库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8
    username: 数据库用户名
    password: 数据库密码