SSH tunneling with Java, a database connection example

Wednesday November 07, 2012 ()

SSH tunnel

This blog is about using SSH tunnel in Java through jsch, a Java an implementation of SSH2.   As an example, we connect to an MySQL installation in a remote host behind a firewall which not accessible from the outside.   What an SSH tunnel does is forward accesses from a local port to a remote port on a remote host and transfer data in a secure manner.

First download jsch here and add it to your project.    The only requirements are,   you have to have an SSH account on the remote host,  and for the database example,    a database account with a "connect" permission.

Here is the code. Please refer to the inline comments from the class below.

import java.util.logging.Logger;
import com.jcraft.jsch.*;
import java.util.logging.Level;

public class MysqlManager {
    // Logger
    private final static Logger LOGGER =
    public static void main(String args[]) {
        MysqlManager mng = new MysqlManager ();

    public void connect() {
        int assigned_port;   
        final int local_port=3309;
        // Remote host and port
        final int remote_port=3306;
        final String remote_host="";
        try {
            JSch jsch = new JSch(); 
            // Create SSH session.  Port 22 is your SSH port which
            // is open in your firewall setup.
            Session session = jsch.getSession("user", remote_host, 22);

            // Additional SSH options.  See your ssh_config manual for
            // more options.  Set options according to your requirements.
            java.util.Properties config = new java.util.Properties();
            config.put("StrictHostKeyChecking", "no");
            config.put("Compression", "yes");
            // Connect
            // Create the tunnel through port forwarding.  
            // This is basically instructing jsch session to send 
            // data received from local_port in the local machine to 
            // remote_port of the remote_host
            // assigned_port is the port assigned by jsch for use,
            // it may not always be the same as
            // local_port.
            assigned_port = session.setPortForwardingL(local_port, 
                    remote_host, remote_port);
        } catch (JSchException e) {            
            LOGGER.log(Level.SEVERE, e.getMessage()); return;
        if (assigned_port == 0) {
            LOGGER.log(Level.SEVERE, "Port forwarding failed !"); 

        // Database access credintials.  Make sure this user has
        // "connect" access to this database;
        // these may be initialized somewhere else in your code.
        final String database_user="user";
        final String database_password="password";
        final String database = "database";
        // Build the  database connection URL.
        StringBuilder url =
                new StringBuilder("jdbc:mysql://localhost:");
        // use assigned_port to establish database connection
        url.append(assigned_port).append ("/").append(database).append ("?user=").
                append(database_user).append ("&password=").
                append (database_password);
        try {
            java.sql.Connection connection =

            java.sql.DatabaseMetaData metadata = connection.getMetaData();

             // Get all the tables and views
            String[] tableType = {"TABLE", "VIEW"};                       
            java.sql.ResultSet tables = metadata.getTables(null, null, "%", tableType);
            String tableName;
            while ( {
                tableName = tables.getString(3);

                // Get the columns from this table
                java.sql.ResultSet columns = 
                        metadata.getColumns(null, tableName, null, null);

                String columnName;
                int dataType;
                while ( {
                    columnName = columns.getString(4);
                    dataType = columns.getInt(5);
                    // Your actual task;

        } catch (ClassNotFoundException |
                IllegalAccessException |
                InstantiationException |
                java.sql.SQLException e) {
            LOGGER.log(Level.SEVERE, e.getMessage());

SSH uses remote host configuration information from the options specified in line 41 in the code above and,   in the $HOME/.ssh/ssh_config file from your local sysytem.   The code above will fail if this file ($HOME/.ssh/ssh_config) is not present.    This is usually the case if you have never used ssh in your local machine before.   To create this file enter the following:

ssh remote_host

Enter yes to when prompted.

Please visit Jcraft's website for more about jsch.

That's it Good Luck.


Comments (SSH tunneling with Java, a database connection example )