需求：本地(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.
- 添加依赖
com.jcraft
jsch
0.1.55
- 创建连接
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.util.Properties;
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";
private final static String SSH_USER = "root";
private final static String SSH_PASSWORD = "123456";
private final static String SSH_REMOTE_SERVER = "192.168.1.216";
private final static int SSH_REMOTE_PORT = 22;
private final static int REMOTE_PORT = 3306;
private final static int LOCAL_PORT = 3307;
private final static String MYSQL_REMOTE_SERVER = "localhost";
private Session session = null;
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);
}
public void closeSSH (){
this.session.disconnect();
}
}
- 添加监听器
并非所有情况都是用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;
@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 ... !");
}
}
}
- @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;
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;
}
}
- 数据库配置
按照代码中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
