Spring boot ssh dataSource,通过SSH协议链接mysql（3306未开放通过端口转发连接）

思路：在连接数据之前先打开一个ssh的连接，使用SSH将数据库转发为本地连接，然后使用本地连接去连接数据库

相关文章：http://stackoverflow.com/questions/1968293/connect-to-remote-mysql-database-through-ssh-using-java

               http://stackoverflow.com/questions/33526427/spring-boot-ssh-mysql

需要添加maven:

   <dependency>
        <groupId>com.jcraft</groupId>
        <artifactId>jsch</artifactId>
        <version>0.1.53</version>
    </dependency>

单独测试代码：

/**
 * Created by YouGuessWho on 2017/3/28.
 */
import java.sql.*;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.Session;

public class SSHConnectTest {
    static int lport;
    static String rhost;
    static int rport;
    public static void go(){
        String user = "SSHUser";
        String password = "SSHPSW";
        String host = "SSHHOST";
        int port=SSHPORT;
        try
        {
            JSch jsch = new JSch();
            Session session = jsch.getSession(user, host, port);
            lport = 3307;
            rhost = "MYSQL_ADDRESS_YRL";
            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://localhost:" + lport + "/";//" + rhost +"
        String db = "target_database_test";
        String dbUser = "dbUser";
        String dbPasswd = "Db_psw";
        try{
            Class.forName(driver);
            con = DriverManager.getConnection(url+db, dbUser, dbPasswd);
            try{
                Statement st = con.createStatement();
                String sql = "select * from contracts " ;

                ResultSet result = st.executeQuery(sql);
                while(result.next()){
                    System.out.println(result.getString("name") + " ");
                }
            }
            catch (SQLException s){
                System.out.println("SQL statement is not executed!");
            }
        }
        catch (Exception e){
            e.printStackTrace();
        }
    }
}

集成：

@Configuration
@EnableJpaRepositories("cn..repository")
@EnableJpaAuditing(auditorAwareRef = "springSecurityAuditorAware")
@EnableTransactionManagement
@EnableElasticsearchRepositories("cn..repository.search")
public class DatabaseConfiguration {

    private final Logger log = LoggerFactory.getLogger(DatabaseConfiguration.class);

    @Inject
    private Environment env;

    @Autowired(required = false)
    private MetricRegistry metricRegistry;
    private SSHConnection conexionssh;

    @Bean(destroyMethod = "close")
    public DataSource dataSource(DataSourceProperties dataSourceProperties, EdiantiProperties ediantiProperties, CacheManager cacheManager) {
        log.debug("Configuring Datasource");
        try {
//打开SSH
            conexionssh = new SSHConnection();
        } catch (Throwable e) {
            e.printStackTrace(); // error connecting SSH server
        }
        if (dataSourceProperties.getUrl() == null) {
            log.error("Your database connection pool configuration is incorrect! The application" +
                            " cannot start. Please check your Spring profile, current profiles are: {}",
                    Arrays.toString(env.getActiveProfiles()));

            throw new ApplicationContextException("Database connection pool is not configured correctly");
        }
        HikariConfig config = new HikariConfig();
        config.setDataSourceClassName(dataSourceProperties.getDriverClassName());
        config.addDataSourceProperty("url", dataSourceProperties.getUrl());
        if (dataSourceProperties.getUsername() != null) {
            config.addDataSourceProperty("user", dataSourceProperties.getUsername());
        } else {
            config.addDataSourceProperty("user", ""); // HikariCP doesn't allow null user
        }
        if (dataSourceProperties.getPassword() != null) {
            config.addDataSourceProperty("password", dataSourceProperties.getPassword());
        } else {
            config.addDataSourceProperty("password", ""); // HikariCP doesn't allow null password
        }

        //MySQL optimizations, see https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        if ("com.mysql.jdbc.jdbc2.optional.MysqlDataSource".equals(dataSourceProperties.getDriverClassName())) {
            config.addDataSourceProperty("cachePrepStmts", ediantiProperties.getDatasource().isCachePrepStmts());
            config.addDataSourceProperty("prepStmtCacheSize", ediantiProperties.getDatasource().getPrepStmtCacheSize());
            config.addDataSourceProperty("prepStmtCacheSqlLimit", ediantiProperties.getDatasource().getPrepStmtCacheSqlLimit());
        }
        config.setMinimumIdle(ediantiProperties.getDatasource().getMinimumIdle());
        config.setMaximumPoolSize(ediantiProperties.getDatasource().getMaximumPoolSize());
        config.setConnectionTestQuery(ediantiProperties.getDatasource().getConnectionTestQuery());
        config.setConnectionInitSql(ediantiProperties.getDatasource().getConnectionInitSql());
        if (metricRegistry != null) {
            config.setMetricRegistry(metricRegistry);
        }
        return new HikariDataSource(config);
    }


    @Bean
    public Hibernate4Module hibernate4Module() {
        return new Hibernate4Module();
    }
}

交流：QQ群：391053981