set mysql connection behind ssh in groovy script SoapUI

Question

From groovy script in SoapUI I need to connect to a mysql database to perform some queries. The problem is that due to security reasons no external access is possible. Therefore it is required to get an ssh access (like a tunnel) and invoke mysql locally.

Initially I was reading the below project properties and then connect to mysql:

ServerUrl=jdbc:mysql://10.255.255.122:3306/db
ServerDbUser=user
ServerDbPwd=password
ServerDriver=com.mysql.jdbc.Driver

def url=testRunner.testCase.testSuite.project.getPropertyValue("ServerUrl")
def usr=testRunner.testCase.testSuite.project.getPropertyValue("ServerDbUser")
def pwd=testRunner.testCase.testSuite.project.getPropertyValue("ServerDbPwd")
def driver=testRunner.testCase.testSuite.project.getPropertyValue("ServerDriver")

com.eviware.soapui.support.GroovyUtils.registerJdbcDriver(driver)
sqlServer = Sql.newInstance(url, usr, pwd, driver)`

But this didn't work so now it is required to establish first a ssh connection to the server with the IP 10.255.255.122 and then open the mysql connection locally. So I guess the Server Url will change to:

ServerUrl=jdbc:mysql://127.0.0.1:3306/db

But I don't know how to set first the ssh connection to the server.

Can someone help me with this?

Thanks.

Answer 1

Have a look at http://forum.soapui.org/viewtopic.php?t=15400 and connect to remote mysql database through ssh using java

It will give you an idea about implementing it in soapUI.

Below is the code by Ripon Al Wasim which is available as an answer at the stackoverflow link mentioned above

package mypackage;
import java.sql.*;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

public class UpdateMySqlDatabase {
    static int lport;
    static String rhost;
    static int rport;
    public static void go(){
        String user = "ripon";
        String password = "wasim";
        String host = "myhost.ripon.wasim";
        int port=22;
        try
            {
            JSch jsch = new JSch();
            Session session = jsch.getSession(user, host, port);
            lport = 4321;
            rhost = "localhost";
            rport = 3306;
            session.setPassword(password);
            session.setConfig("StrictHostKeyChecking", "no");
            System.out.println("Establishing Connection...");
            session.connect();
            int assinged_port=session.setPortForwardingL(lport, rhost, rport);
            System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
            }
        catch(Exception e){System.err.print(e);}
    }
    public static void main(String[] args) {
        try{
            go();
        } catch(Exception ex){
            ex.printStackTrace();
        }
          System.out.println("An example for updating a Row from Mysql Database!");
          Connection con = null;
          String driver = "com.mysql.jdbc.Driver";
          String url = "jdbc:mysql://" + rhost +":" + lport + "/";
          String db = "testDB";
          String dbUser = "wasim";
          String dbPasswd = "riponalwasim123";
          try{
          Class.forName(driver);
          con = DriverManager.getConnection(url+db, dbUser, dbPasswd);
          try{
          Statement st = con.createStatement();
          String sql = "UPDATE MyTableName " +
                  "SET email = 'ripon.wasim@smile.com' WHERE email='peace@happy.com'";

          int update = st.executeUpdate(sql);
          if(update >= 1){
          System.out.println("Row is updated.");
          }
          else{
          System.out.println("Row is not updated.");
          }
          }
          catch (SQLException s){
          System.out.println("SQL statement is not executed!");
          }
          }
          catch (Exception e){
          e.printStackTrace();
          }
          }
        }