Connect to Remote database through SSH using Port Forwarding with Java

Yesterday I need connect connect our remote MySQL database that was setup on SSH enabled server and query database. Since it was on SSH enabled server, we can’t connect it directly using JDBC connection API.To achieve this, we first need to create SSH session and then using Port Forwarding we can forward the request to server and connect to database. I used JSch. JSch is a pure Java implementation of SSH2.

JSch allows you to connect to an sshd server and use port forwarding, X11 forwarding, file transfer, etc., and you can integrate its functionality into your own Java programs. JSch is licensed under BSD style license.

package com.handysofts.mysqlconnection;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;
import java.sql.PreparedStatement;

import java.sql.Connection;
import java.util.HashMap;

public class MySqlConnOverSSH {
 
    private Connection      dbConnAlfresco    = null;

    public static void main(String[] args) throws SQLException {
        int lport=3306;
        String host="100.100.100.100";
        String rhost="127.0.0.1";
        int rport=3306;
        String user="root";
        String password="#password#";
        String dbuserNameAlfresco = "dbuser";
        String dbpasswordAlfresco = "dbpass";
        String urlAlfresco = "jdbc:mysql://localhost:"+rport+"/alfresco?useUnicode=true&characterEncoding=UTF-8";
        String driverName="com.mysql.jdbc.Driver";
        Session session= null;
        
        try{
            //Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
            java.util.Properties config = new java.util.Properties();
            config.put("StrictHostKeyChecking", "no");
            JSch jsch = new JSch();
            session=jsch.getSession(user, host, 22);
            session.setPassword(password);
            session.setConfig(config);
            session.connect();
            System.out.println("Connected");
            int assinged_port=session.setPortForwardingL(lport, rhost, rport);
            System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
            System.out.println("Port Forwarded");
 
            //mysql database connectivity
            Class.forName(driverName).newInstance();
            dbConnAlfresco  = DriverManager.getConnection (urlAlfresco, dbuserNameAlfresco, dbpasswordAlfresco);
            
            System.out.println ("Database connection established");
            System.out.println("DONE");

            dbConnAlfresco.setAutoCommit(false);
     String qnameSQL = "SELECT * FROM alf_node;";
      
     PreparedStatement prepStmnt = dbConnAlfresco.prepareStatement(qnameSQL);
     ResultSet rs = prepStmnt.executeQuery();
     while (rs.next()){
      System.out.println("*** "+rs.getString("uuid")+" ***");
     }

            
        }catch(Exception e){
         dbConnIstinad.rollback();
            e.printStackTrace();            
        }finally{
            if(dbConnAlfresco != null && !dbConnAlfresco.isClosed()){
                System.out.println("Closing ALFRESCO Database Connection");
                dbConnAlfresco.close();
            }
            if(session !=null && session.isConnected()){
                System.out.println("Closing SSH Connection");
                session.disconnect();
            }
        }
    }
}

NOTE: If connection fails then open task manager and kill your own mysql from active process and run again application.