Spring Boot remote Mysql Connection through SSL

Question

I've a remote MySQL Database Server that needs to be connected with my Spring Boot application that runs on my local.

Before Spring Boot Applications loads, it starts with a ssh tunnelling with remote server.

Here is the main application code that first creates and ssh tunnel then starts the main app.

public class XXXCoreApplication {

public static void main(String[] args) throws SQLException {
    Connection connection = null;
    Session session = null;

    String host = "XX.XXX.XX.XXX";
    String servUser = "user";
    String servPwd = "pass";
    int port = 22;

    String rhost = "localhost";
    int rport = 3306;
    int lport = 3307;

    String driverName = "com.mysql.jdbc.Driver";
    String db2Url = "jdbc:mysql://localhost:" + lport + "/xxx_core";
    String dbUsr = "MT";
    String dbPwd = "****";

    try {
        JSch jsch = new JSch();
        // Get SSH session
        session = jsch.getSession(servUser, host, port);
        session.setPassword(servPwd);
        java.util.Properties config = new java.util.Properties();
        // Never automatically add new host keys to the host file
        config.put("StrictHostKeyChecking", "no");
        session.setConfig(config);
        // Connect to remote server
        session.connect();
        // Apply the port forwarding
        session.setPortForwardingL(lport, rhost, rport);
        // Connect to remote database
        Class.forName(driverName);
        connection = DriverManager.getConnection(db2Url, dbUsr, dbPwd);
        System.out.println("Connection to database established!");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
        if (session != null && session.isConnected()) {
            session.disconnect();
        }
    }
    SpringApplication.run(XXXCoreApplication.class, args);
}

}

But my application.properties file is empty, probably the point of failure.

It gives me the error of with an empty application.properties :

"Cannot determine embedded database driver class for database type NONE"

What should I give to the spring.datasource.url value in application.properties? Or any other recommendations? Thanks.

Answer 1

You are closing the SSH tunnel in the finally block.

I would suggest that you also let Spring create the SSH tunnel instead of doing it in your main method. This would allow you to selective enable/disable the tunnel for different environments and you could use properties and other Spring Boot features to configure, start and stop the tunnel.

Something like this would work.

public class SshTunnelStarter {

    @Value("${ssh.tunnel.url}")
    private String url;

    @Value("${ssh.tunnel.username}")
    private String username;

    @Value("${ssh.tunnel.password}")
    private String password;

    @Value("${ssh.tunnel.port:22}")
    private int port;

    private Session session;

    @PostConstruct
    public void init() throws Exception {
       JSch jsch = new JSch();
       // Get SSH session
       session = jsch.getSession(servUser, host, port);
       session.setPassword(servPwd);
       java.util.Properties config = new java.util.Properties();
       // Never automatically add new host keys to the host file
       config.put("StrictHostKeyChecking", "no");
       session.setConfig(config);
       // Connect to remote server
       session.connect();
       // Apply the port forwarding
       session.setPortForwardingL(lport, rhost, rport);
    }

    @PreDestroy
    public void shutdown() throws Exception {
       if (session != null && session.isConnected()) {
           session.disconnect();
       }
    }
}

This way you can use Spring for configuration and managing of the tunnel. You could even make it conditional for certain environments if you like. No tunnel when doing local development and enable it for test and production environment.