navicat可以通过ssh通道连接mysql数据库,那java中如何实现了,springboot又该怎样集成呢?
1.添加包
<dependency>
<groupId>com.jcraft</groupId>
<artifactId>jsch</artifactId>
<version>0.1.53</version>
</dependency>
2.创建ssh连接工具类,连接到ssh server
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/hdwang/.ssh/id_rsa”;
private final static String S_PATH_FILE_KNOWN_HOSTS = “/Users/hdwang/.ssh/known_hosts”;
private final static String S_PASS_PHRASE = “”;
private final static int LOCAl_PORT = 3307;
private final static int REMOTE_PORT = 3306;
private final static int SSH_REMOTE_PORT = 1022;
private final static String SSH_USER = “zhangsan”;
private final static String SSH_PASSWORD = “123456”;
private final static String SSH_REMOTE_SERVER = “192.168.0.2”;
private final static String MYSQL_REMOTE_SERVER = “123.mysql.com”;
private Session sesion; //represents each ssh session
public void closeSSH ()
{
sesion.disconnect();
}
public SSHConnection () throws Throwable
{
JSch jsch </span>= <span style="color: #0000ff">null</span><span style="color: #000000">;
jsch </span>= <span style="color: #0000ff">new</span><span style="color: #000000"> JSch();
jsch.setKnownHosts(S_PATH_FILE_KNOWN_HOSTS);
</span><span style="color: #008000">//</span><span style="color: #008000">jsch.addIdentity(S_PATH_FILE_PRIVATE_KEY);</span>
sesion =
jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
sesion.setPassword(SSH_PASSWORD);
Properties config </span>= <span style="color: #0000ff">new</span><span style="color: #000000"> Properties();
config.put(</span>"StrictHostKeyChecking", "no"<span style="color: #000000">);
sesion.setConfig(config);
sesion.connect(); </span><span style="color: #008000">//</span><span style="color: #008000">ssh connection established!
</span><span style="color: #008000">//</span><span style="color: #008000">by security policy, you must connect through a fowarded port </span>
sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);
}
}
3.管理ssh连接
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.annotation.WebListener;
@WebListener
public class MyContextListener implements ServletContextListener {
</span><span style="color: #0000ff">private</span><span style="color: #000000"> SSHConnection conexionssh;
</span><span style="color: #0000ff">public</span><span style="color: #000000"> MyContextListener() {
</span><span style="color: #0000ff">super</span><span style="color: #000000">();
}
</span><span style="color: #008000">/**</span><span style="color: #008000">
* </span><span style="color: #808080">@see</span><span style="color: #008000"> ServletContextListener#contextInitialized(ServletContextEvent)
</span><span style="color: #008000">*/</span>
<span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span><span style="color: #000000"> contextInitialized(ServletContextEvent arg0) {
System.out.println(</span>"Context initialized ... !"<span style="color: #000000">);
</span><span style="color: #0000ff">try</span><span style="color: #000000"> {
conexionssh </span>= <span style="color: #0000ff">new</span><span style="color: #000000"> SSHConnection();
} </span><span style="color: #0000ff">catch</span><span style="color: #000000"> (Throwable e) {
e.printStackTrace(); </span><span style="color: #008000">//</span><span style="color: #008000"> error connecting SSH server</span>
}
}
</span><span style="color: #008000">/**</span><span style="color: #008000">
* </span><span style="color: #808080">@see</span><span style="color: #008000"> ServletContextListener#contextDestroyed(ServletContextEvent)
</span><span style="color: #008000">*/</span>
<span style="color: #0000ff">public</span> <span style="color: #0000ff">void</span><span style="color: #000000"> contextDestroyed(ServletContextEvent arg0) {
System.out.println(</span>"Context destroyed ... !"<span style="color: #000000">);
conexionssh.closeSSH(); </span><span style="color: #008000">//</span><span style="color: #008000"> disconnect</span>
}
}
4.配置数据库,和你之前配置的一样,但是要修改一下连接地址
spring.datasource.url=jdbc:mysql://127.0.0.1:3307/test?characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
说明:
原理是,程序在本机创建ssh连接,连接到ssh server,然后再发送数据库操作指令,指令会被转发到目标数据库服务器上,返回操作结果。
注意:数据库连接地址由原来的123.mysql.com:3306改为127.0.0.1:3307,这样子,ssh连接会为每一个127.0.0.1:3307上的操作转发到123.mysql.com:3306上去,便可以正常操作数据库了。ssh连接的创建,可以采用私钥的方式,亦可以采用用户名密码的方式。
参考地址:
1.https://stackoverflow.com/questions/33526427/spring-boot-ssh-mysql
2.http://blog.weiqinxue.cn/blogs/index.php/User/articleview/ArticleID/U2A213
转载至:http://www.cnblogs.com/hdwang/p/8670594.html