import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;

    import com.jcraft.jsch.JSch;
    import com.jcraft.jsch.Session;
    import java.sql.PreparedStatement;

    import java.sql.Connection;

    public class MySqlConnOverSSH {

        private static Connection dbConnAlfresco = null;

        public static void main(String[] args) throws SQLException {
            int lport=50000;
            String host="lucia.cs.unibo.it";
            String rhost="localhost";
            int rport=5432;
            String user="user_lab";
            String password="pass_user_lab";
            String dbuserNameAlfresco = "ingsw12_03";
            String dbpasswordAlfresco = null;
            String urlAlfresco = "jdbc:postgresql://localhost:"+rport+"/ingsw12_03";
            String driverName="org.postgresql.Driver";
            Session session= null;

            try{
                //Set StrictHostKeyChecking property to no to avoid UnknownHostKey issue
                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();
                System.out.println("Connected");
                int assinged_port=session.setPortForwardingL(lport, rhost, rport);
                System.out.println("localhost:"+assinged_port+" -> "+rhost+":"+rport);
                System.out.println("Port Forwarded");

                //mysql database connectivity
                Class.forName(driverName).newInstance();
                dbConnAlfresco = DriverManager.getConnection (urlAlfresco, dbuserNameAlfresco, dbpasswordAlfresco);

                System.out.println ("Database connection established");
                System.out.println("DONE");

                dbConnAlfresco.setAutoCommit(false);
         String qnameSQL = "SELECT * FROM alf_node;";

         PreparedStatement prepStmnt = dbConnAlfresco.prepareStatement(qnameSQL);
         ResultSet rs = prepStmnt.executeQuery();
         while (rs.next()){
          System.out.println("*** "+rs.getString("uuid")+" ***");
         }


            }catch(Exception e){
                e.printStackTrace();
            }finally{
                if(dbConnAlfresco != null && !dbConnAlfresco.isClosed()){
                    System.out.println("Closing ALFRESCO Database Connection");
                    dbConnAlfresco.close();
                }
                if(session !=null && session.isConnected()){
                    System.out.println("Closing SSH Connection");
                    session.disconnect();
                }
            }
        }
    }