spring mvc 配置ssh 连接mysql

近遇到一个问题,就是要远程连接一个mysql数据库

因为这个数据库布在别人的内网上面,所以要通过ssh去连接这个数据库

好了废话不多说了

第一步

添加jar包

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

 第二步 配置连接对象

package com.wx.util.ssh;

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

import java.util.Properties;


/**
 * Created by Administrator on 2021/12/1.
 */

public class SSHConnection {
    private final static int LOCAl_PORT = 3307; //本地跳转服务器
    private final static int REMOTE_PORT = 3306; //远程要转发的端口
    private final static int SSH_REMOTE_PORT = 22;
    private final static String SSH_USER = "远程服务器用户";
    private final static String SSH_PASSWORD = "ssh的密码";
    private final static String SSH_REMOTE_SERVER = "中间服务器的ip";
    private final static String MYSQL_REMOTE_SERVER = "mysql的服务器的ip";

    private Session sesion; //represents each ssh session


    public void closeSSH() {
        sesion.disconnect();
    }

    public SSHConnection() throws Throwable {

        JSch jsch = null;
        jsch = new JSch();

        sesion = jsch.getSession(SSH_USER, SSH_REMOTE_SERVER, SSH_REMOTE_PORT);
        sesion.setPassword(SSH_PASSWORD);

        Properties config = new Properties();
        config.put("StrictHostKeyChecking", "no");
        sesion.setConfig(config);

        sesion.connect(); //ssh connection established!

        //by security policy, you must connect through a fowarded port
        sesion.setPortForwardingL(LOCAl_PORT, MYSQL_REMOTE_SERVER, REMOTE_PORT);

    }
}

第三步 初始化扫描

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

@WebListener
public class MyListener implements ServletContextListener {

    private SSHConnection conexionssh;


    public MyListener() {
        super();
    }


/**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */

    public void contextInitialized(ServletContextEvent arg0) {
        System.out.println("Context initialized ... !");
        try {
            conexionssh = new SSHConnection(); // 监听到了 就装配文件
        } catch (Throwable e) {
            e.printStackTrace(); // error connecting SSH server
        }
    }


/**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */

    public void contextDestroyed(ServletContextEvent arg0) {
        System.out.println("Context destroyed ... !");
        conexionssh.closeSSH(); // disconnect
    }

}

 第四步 修改配置文件

// 原先配置
base.url=jdbc:mysql://192.168.18.183:3306/data?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useAffectedRows=true
base.username=root
base.password=root
//修改后的配置
base.url=jdbc:mysql://127.0.0.1:3307/data?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
base.username=root
base.password=root

 如果你要测试的话,还有第5步下面

 

 

 

package com.tiaojia;

import com.wx.util.ssh.SSHConnection;
import org.springframework.test.context.TestContext;
import org.springframework.test.context.TestExecutionListener;

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

@WebListener
public class MyContextListener implements  TestExecutionListener {

    private SSHConnection conexionssh;


    public MyContextListener() {
        super();
    }


/**
     * @see ServletContextListener#contextInitialized(ServletContextEvent)
     */

    public void contextInitialized(ServletContextEvent arg0) {
        System.out.println("Context initialized ... !");
        try {
            conexionssh = new SSHConnection();
        } catch (Throwable e) {
            e.printStackTrace(); // error connecting SSH server
        }
    }

/**
     * @see ServletContextListener#contextDestroyed(ServletContextEvent)
     */

    public void contextDestroyed(ServletContextEvent arg0) {
        System.out.println("Context destroyed ... !");
        conexionssh.closeSSH(); // disconnect
    }

    @Override
    public void beforeTestClass(TestContext testContext) throws Exception {

    }

    @Override
    public void prepareTestInstance(TestContext testContext) throws Exception {
        System.out.println("Context initialized ... !");
        try {
            conexionssh = new SSHConnection();
        } catch (Throwable e) {
            e.printStackTrace(); // error connecting SSH server
        }

    }

    @Override
    public void beforeTestMethod(TestContext testContext) throws Exception {

    }

    @Override
    public void afterTestMethod(TestContext testContext) throws Exception {

    }

    @Override
    public void afterTestClass(TestContext testContext) throws Exception {

    }
}

第6步完成

你可以写你的测试代码了