Connecting to MYSQL DB with the help of SSH tunnel

Product Forums Katalon Studio
,
1

Hello Team,

I am new to Katalon and using katalon from few days. I have requirement where I need to connect to MySQL DB with the help of SSH tunnel. SSH tunnel uses .pem file for authentication.

I am using below two codes for creating SSH connection and connecting to MYSQL DB, Please find details below:

  1. Connect Over SSH

package com.journaldev.java.ssh

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

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

public class MySqlConnOverSSH {
public static void main(String[] args) throws SQLException {

	int lport=5656;
    String rhost="secure.journaldev.com";
    String host="secure.journaldev.com";
    int rport=3306;
    String user="sshuser";
    String password="sshpassword";
    String dbuserName = "mysql";
    String dbpassword = "mysql123";
    String url = "jdbc:mysql://localhost:"+lport+"/mydb";
    String driverName="com.mysql.jdbc.Driver";
    Connection conn = null;
    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();
        conn = DriverManager.getConnection (url, dbuserName, dbpassword);
        System.out.println ("Database connection established");
        System.out.println("DONE");
    }catch(Exception e){
    	e.printStackTrace();
    }finally{
    	if(conn != null && !conn.isClosed()){
    		System.out.println("Closing Database Connection");
    		conn.close();
    	}
    	if(session !=null && session.isConnected()){
    		System.out.println("Closing SSH Connection");
    		session.disconnect();
    	}
    }
}

}

  1. Connect to mysql DB with the code provided at Katalon community :

@Keyword

def connectDB(String driverType ,String url, String dbname, String port, String username, String password){

	//Load driver class for your specific database type

	String conn = driverType + url + ":" + port + "/" + dbname

	//Class.forName("org.sqlite.JDBC")

	//String connectionString = "jdbc:sqlite:" + dataFile

	if(connection != null && !connection.isClosed()){

		connection.close()

	}

What basically I need toperform below steps to connect to database:

  1. Connect to SSH tunnel using key authentication → Available Java code

(https://www.journaldev.com/235/java-mysql-ssh-jsch-jdbc)

2.Connect to MySQL DB post successful SSH connection.

(https://docs.katalon.com/katalon-studio/docs/connect_db_gui_testing.html)

Need to perform all steps in Katalon, Any leads please help.

Can anyone help me over this?

Thanks for your help in advance :slight_smile: