1

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!

remux -
  • 37
  • 7
  • how do you open connection to mysql in your code? – Iłya Bursov Mar 11 '16 at 21:31
  • change it to `jdbc:mysql://mysql.server.remote:3306/remoteDB` – Iłya Bursov Mar 11 '16 at 21:35
  • dbConn = DriverManager.getConnection ("jdbc:mysql://localhost:3306/remoteDB, dbuserName, dbpassword"); - after the tunnel is created – remux - Mar 11 '16 at 21:38
  • Connected localhost:4321 -> localhost:3306 Port Forwarded 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. at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) Closing SSH Connection – remux - Mar 11 '16 at 21:43
  • If I use **remote server** in the connection gives me `Error : Communications link failure`; If I use **localhost** the `Error :Unknown database 'remoteDB'` – remux - Mar 11 '16 at 21:53
  • Please edit your question to include the code that you have for making the ssh connection. Include any error messages or exceptions that you get when running it. – Kenster Mar 12 '16 at 11:10

1 Answers1

0

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.

Bahgu Kiitar
  • 51
  • 3