Java SSH MySQL connection

问题:

How can I implement this in Java code?

ssh -L 1234:localhost:3306 mysql.server.remote

I have a Java desktop application with the database in a shared server (internet) to be accessible from everywhere. The guys that will use the software will not open terminals to execute commands before and after will use the software...

So I need a solution to connect my application with the database when the application starts and kill the connections (conn.close(), and ssh) when they will close the application. For that, all this (the code above) should be inside my script... And this is what i cannot achieve...

I've used JSch, and I successfully create the SSH tunnel (on port 22), but no way to connect MySQL... Any help it's welcome.

The whole code here:

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

    String host="ssh.Mysite.com";
    String rhost="localhost";
    int rport=3306;
    int lport=4321;

    String user="userMysite.com";
    String password="passMysite.com";

    String dbuserName = "UserDB.com";
    String dbpassword = "PassDB.com";
    String url = "jdbc:mysql://localhost:3306/DB.com";
    String driverName="com.mysql.jdbc.Driver";
    Session session= null;

    try{
  //ssh tunnel creation          
        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();
        int assinged_port=session.setPortForwardingL(lport, rhost, rport);

  //mysql database connect                  
        Class.forName(driverName).newInstance();
        dbConn  = DriverManager.getConnection (url, dbuserName, dbpassword);

 String sql = "SELECT * FROM client ";      
     PreparedStatement ps = dbConn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery();
            while (rs.next()){
                  System.out.println(rs.getString("name"));
            }

    }catch(JSchException e){
        e.printStackTrace();            
    }finally{
        if(dbConn != null && !dbConn.isClosed()){
            dbConn.close();
        }
        if(session !=null && session.isConnected()){
            session.disconnect();
        }
    }
}

For test purposes i made the following code:

String url = "jdbc:mysql://localhost:3306";
String driverName="com.mysql.jdbc.Driver";
Class.forName(driverName);
Connection dbConn = DriverManager.getConnection (url, dbuserName, dbpassword); 

        try {
           ResultSet rs = dbConn.getMetaData().getCatalogs();
                        while (rs.next()) {
                            System.out.println("TABLE_CAT = " + rs.getString("TABLE_CAT") );
                        }           
         }catch(SQLException e){
                         e.printStackTrace(); 
         }

Results:

localhost:3306 => Print me all the databases in my computer's localhost;

remote.server.mysql:3306 or remote.server.mysql:4321 or localhost:4321 => ERROR

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Thank you!

回答1:

Could you try running ssh binary from Java? For example:

Runtime rt = Runtime.getRuntime();
Process pr = rt.exec("ssh -L 1234:localhost:3306 mysql.server.remote");

Obviously, that requires a system to have ssh installed in a PATH.