Spring Boot SSH连接数据库

需求：本地(Win10)测试连接服务器失败(报错信息如下)，对比本地navicat后发下使用的是SSH连接。因此，java也需要使用SSH方式连接。
注意：由于本地3306端口被Navicat占用，所以程序中随意换了一个端口。只要保证数据库配置中的IP和端口与程序中设置的IP和端口一直就行。

错误信息：

2021-09-28 18:46:08.436 main [] DEBUG com.zaxxer.hikari.pool.PoolBase.newConnection:368 - HikariPool-1 - Failed to create/setup connection: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
2021-09-28 18:46:08.448 main [] DEBUG com.zaxxer.hikari.pool.HikariPool.createPoolEntry:499 - HikariPool-1 - Cannot acquire connection from data sourcecom.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

1. 添加依赖

    <!--mysql-ssh-->
    <dependency>
      <groupId>com.jcraft</groupId>
      <artifactId>jsch</artifactId>
      <version>0.1.55</version>
    </dependency>

2. 创建连接

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

import java.util.Properties;

/**
 * @description: mysql-ssh连接
 * https://blog.csdn.net/weixin_45248492/article/details/118406072
 * https://blog.csdn.net/weixin_40461281/article/details/103695882
 * @date 2021/9/22 14:48
 */
public class SSHConnection {

	/**
	 * 需要时打开
	 */
//	private final static String S_PATH_FILE_PRIVATE_KEY = "/Users/xxx/.ssh/id_rsa";
	/**
	 * 需要时打开
	 */
//	private final static String S_PATH_FILE_KNOWN_HOSTS = "/Users/xxx/.ssh/id_rsa/.ssh/known_hosts";

	/**
	 * linux服务器登录名
	 */
	private final static String SSH_USER = "root";
	/**
	 * linux登陆密码
	 */
	private final static String SSH_PASSWORD = "123456";
	/**
	 *linux服务器公网IP
	 */
	private final static String SSH_REMOTE_SERVER = "192.168.1.216";
	/**
	 *跳板机ssh开放的接口,ssh通道端口   默认端口 22
	 */
	private final static int SSH_REMOTE_PORT = 22;
	/**
	 *服务器上数据库端口号
	 */
	private final static int REMOTE_PORT = 3306;
	/**
	 *这个是本地的端口(即配置文件中数据源的PORT)，选取一个没有占用的port即可
	 */
	private final static int LOCAL_PORT = 3307;
	/**
	 *要访问的mysql所在的ip (即配置文件中数据源的HOST, 本地(Windows)测试只能使用localhost)
	 */
	private final static String MYSQL_REMOTE_SERVER = "localhost";

	private Session session = null;
	/**
	 *    建立SSH连接
	 */
	public SSHConnection() throws Throwable{
		JSch jsch = new JSch();
		// 需要时开启
		// jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
		//jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY);
		session = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
		session.setPassword(SSH_PASSWORD);
		Properties config = new Properties();
		config.put("StrictHostKeyChecking", "no");
		session.setConfig(config);
		session.connect();
		session.setPortForwardingL(LOCAL_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
	}
	/**
	 *    断开SSH连接
	 */
	public void closeSSH (){
		this.session.disconnect();
	}
}

3. 添加监听器
   并非所有情况都是用SSH连接的方式，所以要按照条件（@Profile(value = “dev”)和@Conditional({SSHConnectionCondition.class})）注册监听器

import com.healsci.common.condition.SSHConnectionCondition;
import com.healsci.common.toolkit.SSHConnection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.annotation.Conditional;
import org.springframework.context.annotation.Profile;
import org.springframework.stereotype.Component;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;

/**
 * @description: mysql-ssh listener
 * @date 2021/9/22 14:56
 */
@Profile(value = "dev")
@Conditional({SSHConnectionCondition.class})
@WebListener
@Component
public class SSHConnectionListener implements ServletContextListener {

	private static final Logger logger = LoggerFactory.getLogger(SSHConnectionListener.class);

	private SSHConnection sshConnection;

	public SSHConnectionListener() {
		super();
	}

	@Override
	public void contextInitialized(ServletContextEvent arg0) {
		// 建立连接
		try {
			sshConnection = new SSHConnection();
			logger.info("SSHConnectionListener initialized ... !");
		} catch (Throwable e) {
			logger.error("SSHConnectionListener create connection failed ... !");
			e.printStackTrace();
		}
	}

	@Override
	public void contextDestroyed(ServletContextEvent arg0) {
		// 断开连接
		try {
			// disconnect
			sshConnection.closeSSH();
			logger.info("SSHConnectionListener destroyed ... !");
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("SSHConnectionListener disconnect failed ... !");
		}
	}
}

4. @Conditional的判断逻辑

import org.apache.commons.lang3.StringUtils;
import org.springframework.context.annotation.Condition;
import org.springframework.context.annotation.ConditionContext;
import org.springframework.core.env.Environment;
import org.springframework.core.type.AnnotatedTypeMetadata;

/**
 * @description:  目前仅测试环境216服务器上的mysql用到SSH连接
 * @date 2021/9/22 15:39
 */
public class SSHConnectionCondition implements Condition {

	private static final String SSH_HOST_KEY = "datasource.staging.jdbc-url";
	private static final String SSH_HOST_VALUE = "localhost";
	@Override
	public boolean matches(ConditionContext context, AnnotatedTypeMetadata metadata) {
		Environment environment = context.getEnvironment();
		String property = environment.getProperty(SSH_HOST_KEY);
		if (StringUtils.isNotBlank(property) && property.contains(SSH_HOST_VALUE)){
			return true;
		}
		return false;
	}
}

5. 数据库配置
   按照代码中SSH映射关系配置数据库访问的IP和端口，到时会转发给目标服务器

datasource.staging.driver-class-name=com.mysql.cj.jdbc.Driver
datasource.staging.jdbc-url=jdbc:mysql://localhost:3307/${DB_NAME_STAGING:staging}?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true&serverTimezone=GMT%2B8
datasource.staging.username=admin
datasource.staging.password=123456