Tunnel via ssh(port 22) then port forward 3306 to access the database - SOAP UI groovy

I just implemented a function in groovy for my team project to test data in AWS RDS with ssh tunnel. And when I googled it, there was no much results about how to make ssh tunnel via jsch. I spent quite a long time to figure it out, and saw your question post, so want to share here and hope it would be helpful.

  import groovy.sql.Sql
  import com.jcraft.jsch.*

def resultSet = queryDatabase(sql)
// do verification with resultSet

// make an ssh tunnel to database in AWS, execute query with specific sql and // return result set as a list.
def queryDatabase(String sql){
	def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
	def projectPath = groovyUtils.projectPath //gets the path of the project root
	def sshKeyPath = projectPath+"/../key"
	
	JSch jsch = new JSch()
	jsch.addIdentity(sshKeyPath)
	
	Session session = jsch.getSession(sshUser, sshHost, sshPort);
	session.setConfig("StrictHostKeyChecking", "no")
	
	try{
	  session.connect()
	  session.setPortForwardingL(1521, targetHost, targetPort);

	  // Query database and process the result.
	  sql = Sql.newInstance(
	  'jdbc:oracle:thin:@localhost:1521'
	  + testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_SERVICE_NAME"),
	  testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_USERNAME"),
	  testRunner.testCase.testSuite.project.getPropertyValue("DATABASE_PASSWORD"),
	  'oracle.jdbc.driver.OracleDriver'
	  )
		
	  def resultset = sql.rows(sql)
	resultset
	}finally{
	  session.disconnect()
	  null
	}
}

NOTE:

1. Download jsch-0.1.54.jar from https://mvnrepository.com/artifact/com.jcraft/jsch/0.1.54 and put in <soapui-dir>/bin/ext directory.

2. Restart soapui.

3. Remember to disconnect the session when exception occurs, otherwise the connection would be hang, then the port cannot be bound to.