[FIXED] Spring Data JPA with ssh tunnel to a remote MySQL server

Issue

I'm using Spring Data JPA with Hibernate as persistence provider in conjunction with a remote MySQL5 Server for a job that periodically replicates a subset of internal data. The job (i.e. a quartz-scheduled java application) runs once per dai and needs approx. 30seconds to complete the synchronization). For safety reasons we don't want to open the remote server for direct connections from outside (i.e. other than localhost).

I've seen examples with Jsch to programmatically set up an ssh tunnel, but could not finde any resources on how to integrate Jsch with spring data. One problem I'm seeing is that certain of my spring beans (i.e. org.apache.commons.configuration.DatabaseConfiguration) are created at application startup and already needs access to the datasource.

I could open the ssh tunnel outside of the application, but then it would be opened all the time, but I wanted to avoid that as I only need it opened 30seconds per day.

EDIT:

After some research I found several ways to get a ssh tunnel

A) Implementing my own DataSource (I extended org.springframework.jdbc.datasource.DriverManagerDataSource) and then used PostContruct and Predestroy to setup / close the ssh tunnel with Jsch

--> Problem: The ssh tunnel remains open for the lifetime of the application, what is not what I want

B) Implementing my own Driver (I extended com.mysql.jdbc.Driver) and overwrite "connect" to create the ssh tunnel before the connection

--> Problem: I'm not able to close the ssh tunnel connection

Any more suggestions are welcome

Solution

If you have a DataSource bean in your Spring configuration, you can create your own DataSource implementation that opens an SSH tunnel before attempting to make a connection using the provided JDBC URL. As an example, consider the following configuration that uses a HikariDataSource:

<bean id="entityManagerFactory"
  class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
  <property name="dataSource">
    <bean class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">...</bean>
  </property>
</bean>

You can extend the class HikariDataSource to provide your own implementation. An example below:

class TunneledHikariDataSource extends HikariDataSource implements InitializingBean {
  private boolean createTunnel = true;
  private int tunnelPort = 3306;

  public void afterPropertiesSet() {
    if(createTunnel) {
      // 1. Extract remote host name from the JDBC URL.
      // 2. Extract/infer remote tunnel port (e.g. 3306)
      // from the JDBC URL.
      // 3. Create a tunnel using Jsch and sample code
      // at http://www.jcraft.com/jsch/examples/PortForwardingL.java.html
      ...
    }
  }
}

Then, instantiate a bean instance for the custom class instead of HikariDataSource.

Answered By - manish
Answer Checked By - Katrina (JavaFixing Volunteer)