Utilisation de ssh et mysql en java

Télécharger le projet
Commenter

Description

Suite a un besoin, j'ai du mettre en place cette application, que j'ai aimé partager.

1 ) Description de besoin:
nous avons une BDD où sont enregistré nos clients et leurs abonnements.
A l'expiration d'un abonnement, le client se voit couper, et au renouvellement son abonnement est rétablie.
Alors, nous avons une bdd MySql ou j'ai ajouté 2 tables, qui serviront comme des boite au lettre. Ces 2 table sont remplis par les utilisateurs d'une autre application. et sont vidés par cette application que j'ai mis en place.

2 ) L'environnement et mise en place
une machine Linux (Debian), Mysql, JVM
mon application tourne en cron, et s'execute chaque 5 minute

3) L'application
Avant de commencer, il faut avoir la librairie JSCH que vous trouvrer dans ce lient http://www.jcraft.com/jsch/. ainsi que le connecteur Mysql et que vous pouvez le trouver ici http://cdn.mysql.com/Downloads/Connector-J/mysql-connector-java-5.1.25.zip.
cette application des constituer de 9 classes regroupés dans 3 pacquages.

Source / Exemple : 


package javassh;

import entity.ActivationPool;
import entity.DesactivationPool;
import java.util.List;
import session.AbonnementFacade;
import session.ActivationPoolFacade;
import session.DesactivationPoolFacade;

public class PoolAbonnement {

    public static void main(String[] args) {

        AbonnementFacade abonnementFacade = new AbonnementFacade();
        ActivationPoolFacade activationFacade = new ActivationPoolFacade();
        List<ActivationPool> activationList = activationFacade.getActivationList();
        for (ActivationPool activationPool : activationList) {
            String exec[] = new String[]{"conf t", "interface GigabitEthernet0/1." + activationPool.getVlan(), "ip address " + activationPool.getIp() + " " + activationPool.getMask() + " secondary", "exit", "exit", "show run | i " + activationPool.getIp(), "wr"};
            JavaSsh tunnel = new JavaSsh(activationPool.getIpRouter(), activationPool.getPortRouter(), activationPool.getLoginRouter(), activationPool.getPasswordRouter());
            if (tunnel.connect()) {
                if (tunnel.send(exec)) {
                    activationFacade.remove(activationPool.getIdAcivationPool());
                    abonnementFacade.setEtat(activationPool.getIdAbonnement(), 1);
                } else {
                }
            } else {
            }
            if (!tunnel.disconnect()) {
                System.out.println("Erreur de deconnection");
                ////System.exit(0);
            }
        }
        activationFacade = null;
        
        DesactivationPoolFacade desactivationPoolFacade = new DesactivationPoolFacade();
        List<DesactivationPool> desactivationList = desactivationPoolFacade.getDesactivationList();
        for (DesactivationPool desactivationPool : desactivationList) {
            String exec[] = new String[]{"conf t", "interface GigabitEthernet0/1." + desactivationPool.getVlan(), "no ip address " + desactivationPool.getIp() + " " + desactivationPool.getMask() + " secondary", "exit", "exit", "show run | i " + desactivationPool.getIp(), "wr"};
            JavaSsh tunnel = new JavaSsh(desactivationPool.getIpRouter(), desactivationPool.getPortRouter(), desactivationPool.getLoginRouter(), desactivationPool.getPasswordRouter());
            if (tunnel.connect()) {
                if (tunnel.send(exec)) {
                    desactivationPoolFacade.remove(desactivationPool.getIdDesacivationPool());
                    abonnementFacade.setEtat(desactivationPool.getIdAbonnement(), 2);
                } else {
                }
            } else {
            }
            if (!tunnel.disconnect()) {
                System.out.println("Erreur de deconnection");
                //System.exit(0);
            }
        }
        desactivationPoolFacade = null;

    }
}

package javassh;

import com.jcraft.jsch.*;

import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.util.logging.Level;

public class JavaSsh {

    private String endLineStr = "#"; // it is dependant to the server 
    private String host; // host IP 
    private String user; // username for SSH connection 
    private String password; // password for SSH connection 
    private int port; // default SSH port 
    private JSch shell;
    private Session session;
    private DataInputStream dataIn;
    private DataOutputStream dataOut;
    private Channel channel;

    public String getEndLineStr() {
        return endLineStr;
    }

    public void setEndLineStr(String endLineStr) {
        this.endLineStr = endLineStr;
    }

    public String getHost() {
        return host;
    }

    public void setHost(String host) {
        this.host = host;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public int getPort() {
        return port;
    }

    public void setPort(int port) {
        this.port = port;
    }

    public String getUser() {
        return user;
    }

    public void setUser(String user) {
        this.user = user;
    }

    public JavaSsh() {
    }

    public JavaSsh(String host, int port, String user, String password) {
        this.host = host;
        this.user = user;
        this.password = password;
        this.port = port;
    }

    public boolean connect() {
        try {
            shell = new JSch();
            session = shell.getSession(user, host, port);
            // set user password and connect to a channel 
            session.setUserInfo(new SSHUserInfo(password));
            session.connect();
            channel = session.openChannel("shell");
            channel.connect();
            dataIn = new DataInputStream(channel.getInputStream());
            dataOut = new DataOutputStream(channel.getOutputStream());
            return true;
        } catch (IOException ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (JSchException ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    public boolean  send(String[] exec) {
        try {
            for (String string : exec) {
                dataOut.writeBytes(string + " \r\n");
                dataOut.flush();
                // and print the response  è
                String line = dataIn.readLine();
                System.out.println(line);
                while (!line.endsWith(endLineStr)) {
                    System.out.println(line);
                    line = dataIn.readLine();
                }
            }
            return true;
        } catch (IOException ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }

    }
    
    public boolean disconnect(){
        try {
            dataIn.close();
            dataOut.close();
            channel.disconnect();
            session.disconnect();
            return true;
        } catch (IOException ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (Exception ex) {
            java.util.logging.Logger.getLogger(JavaSsh.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        }
    }

    // this class implements jsch UserInfo interface for passing password to the session 
    static class SSHUserInfo implements UserInfo {

        private String password;

        SSHUserInfo(String password) {
            this.password = password;
        }

        public String getPassphrase() {
            return null;
        }

        public String getPassword() {
            return password;
        }

        public boolean promptPassword(String arg0) {
            return true;
        }

        public boolean promptPassphrase(String arg0) {
            return true;
        }

        public boolean promptYesNo(String arg0) {
            return true;
        }

        public void showMessage(String arg0) {
            System.out.println(arg0);
        }
    }
}

package session;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class AbstractSQL {
    
    private String dbURL = "jdbc:mysql://localhost:3306/abonnement_db";
    private String driverClass = "com.mysql.jdbc.Driver";
    private String serverName = "localhost";
    private String portNumber = "3306";
    private String databaseName = "abonnement_db";
    private String user = "root";
    private String password = "";
    
    private java.sql.Connection dbConnect = null;
    private java.sql.Statement dbStatement = null;
    
    public Boolean connect() {
        try {
            Class.forName(driverClass).newInstance();
            this.dbConnect = DriverManager.getConnection(dbURL, this.user, this.password);
            this.dbStatement = this.dbConnect.createStatement();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    /**


         
  •  Executer une requete SQL

    •      
  •  @param sql

    •      
  •  @return resultat de la requete

    •      
  • /

    public ResultSet execResult(String sql) {
        try {
            ResultSet rs = this.dbStatement.executeQuery(sql);
            return rs;
        } catch (SQLException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    public void exec(String sql){
        try {
            this.dbStatement.executeUpdate(sql);
        } catch (SQLException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    /**


         
  •  Fermer la connexion au serveur de DB

    •      
  • /

    public void close() {
        try {
            this.dbStatement.close();
            this.dbConnect.close();
            this.dbConnect.close();
        } catch (SQLException ex) {
            Logger.getLogger(AbstractSQL.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

package entity;

import java.util.Date;

public class Abonnement {
    private int idAbonnement;
    private int idClient;
    private Date dateDebut;
    private Date dateFin;
    private int etat;
    

    public Date getDateDebut() {
        return dateDebut;
    }

    public void setDateDebut(Date dateDebut) {
        this.dateDebut = dateDebut;
    }

    public Date getDateFin() {
        return dateFin;
    }

    public void setDateFin(Date dateFin) {
        this.dateFin = dateFin;
    }

    public int getEtat() {
        return etat;
    }

    public void setEtat(int etat) {
        this.etat = etat;
    }

    public int getIdAbonnement() {
        return idAbonnement;
    }

    public void setIdAbonnement(int idAbonnement) {
        this.idAbonnement = idAbonnement;
    }

    public int getIdClient() {
        return idClient;
    }

    public void setIdClient(int idClient) {
        this.idClient = idClient;
    }

    public Abonnement(int idAbonnement, int idClient, Date dateDebut, Date dateFin, int etat) {
        this.idAbonnement = idAbonnement;
        this.idClient = idClient;
        this.dateDebut = dateDebut;
        this.dateFin = dateFin;
        this.etat = etat;
    }
}

package entity;

public class ActivationPool {
    private int idAcivationPool;
    private String vlan;
    private String ip;
    private String mask;
    private String ipRouter;
    private String loginRouter;
    private String passwordRouter;
    private int portRouter;
    private int idAbonnement;

    public int getIdAcivationPool() {
        return idAcivationPool;
    }

    public void setIdAcivationPool(int idAcivationPool) {
        this.idAcivationPool = idAcivationPool;
    }

    public String getIp() {
        return ip;
    }

    public void setIp(String ip) {
        this.ip = ip;
    }

    public String getIpRouter() {
        return ipRouter;
    }

    public void setIpRouter(String ipRouter) {
        this.ipRouter = ipRouter;
    }

    public String getLoginRouter() {
        return loginRouter;
    }

    public void setLoginRouter(String loginRouter) {
        this.loginRouter = loginRouter;
    }

    public String getMask() {
        return mask;
    }

    public void setMask(String mask) {
        this.mask = mask;
    }

    public String getPasswordRouter() {
        return passwordRouter;
    }

    public void setPasswordRouter(String passwordRouter) {
        this.passwordRouter = passwordRouter;
    }

    public int getPortRouter() {
        return portRouter;
    }

    public void setPortRouter(int portRouter) {
        this.portRouter = portRouter;
    }

    public String getVlan() {
        return vlan;
    }

    public void setVlan(String vlan) {
        this.vlan = vlan;
    }

    public int getIdAbonnement() {
        return idAbonnement;
    }

    public void setIdAbonnement(int idAbonnement) {
        this.idAbonnement = idAbonnement;
    }
    
    public ActivationPool(int idAcivationPool, String vlan, String ip, String mask, String ipRouter, String loginRouter, String passwordRouter, int portRouter, int idAbonnement) {
        this.idAcivationPool = idAcivationPool;
        this.vlan = vlan;
        this.ip = ip;
        this.mask = mask;
        this.ipRouter = ipRouter;
        this.loginRouter = loginRouter;
        this.passwordRouter = passwordRouter;
        this.portRouter = portRouter;
        this.idAbonnement = idAbonnement;
    }
}

package entity;

public class DesactivationPool {
    private int idDesacivationPool;
    private String vlan;
    private String ip;
    private String mask;
    private String ipRouter;
    private String loginRouter;
    private String passwordRouter;
    private int portRouter;
    private int idAbonnement;

    public int getIdDesacivationPool() {
        return idDesacivationPool;
    }

    public void setIdDesacivationPool(int idDesacivationPool) {
        this.idDesacivationPool = idDesacivationPool;
    }

    public String getIp() {
        return ip;
    }

    public void setIp(String ip) {
        this.ip = ip;
    }

    public String getIpRouter() {
        return ipRouter;
    }

    public void setIpRouter(String ipRouter) {
        this.ipRouter = ipRouter;
    }

    public String getLoginRouter() {
        return loginRouter;
    }

    public void setLoginRouter(String loginRouter) {
        this.loginRouter = loginRouter;
    }

    public String getMask() {
        return mask;
    }

    public void setMask(String mask) {
        this.mask = mask;
    }

    public String getPasswordRouter() {
        return passwordRouter;
    }

    public void setPasswordRouter(String passwordRouter) {
        this.passwordRouter = passwordRouter;
    }

    public int getPortRouter() {
        return portRouter;
    }

    public void setPortRouter(int portRouter) {
        this.portRouter = portRouter;
    }

    public String getVlan() {
        return vlan;
    }

    public void setVlan(String vlan) {
        this.vlan = vlan;
    }

    public int getIdAbonnement() {
        return idAbonnement;
    }

    public void setIdAbonnement(int idAbonnement) {
        this.idAbonnement = idAbonnement;
    }
    
    public DesactivationPool(int idDesacivationPool, String vlan, String ip, String mask, String ipRouter, String loginRouter, String passwordRouter, int portRouter, int idAbonnement) {
        this.idDesacivationPool = idDesacivationPool;
        this.vlan = vlan;
        this.ip = ip;
        this.mask = mask;
        this.ipRouter = ipRouter;
        this.loginRouter = loginRouter;
        this.passwordRouter = passwordRouter;
        this.portRouter = portRouter;
        this.idAbonnement = idAbonnement;
    }
}

package session;

import entity.Abonnement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class AbonnementFacade extends AbstractSQL {

    public AbonnementFacade() {
    }

    public void remove(int id){
        if (connect()) {
            exec("DELETE FROM abonnement WHERE id_abonnement="+id);
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();
    }
    
    public void edit(Abonnement abonnement){
        if (connect()) {
            exec("UPDATE abonnement SET etat_abonnement ="+abonnement.getEtat()+", id_client ="+abonnement.getIdClient()+", date_debut ='"+abonnement.getDateDebut()+"', date_fin ='"+abonnement.getDateFin()+"' WHERE id_abonnement ="+abonnement.getIdAbonnement());
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();
    }
    
    public void setEtat(int idAbonnement, int etat){
        if (connect()) {
            exec("UPDATE abonnement SET etat_abonnement ="+etat+" WHERE id_abonnement ="+idAbonnement);
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();
    }

    public List<Abonnement> getEndAbonnementList(Date day) {
        List<Abonnement> lcs = new ArrayList<Abonnement>();

        if (connect()) {
            try {
                String now = new SimpleDateFormat("yyyy-MM-dd").format(day);
                ResultSet rs = execResult("SELECT id_abonnement, id_client, date_debut, date_fin, etat_abonnement FROM abonnement WHERE date_fin = '"+now+"' ORDER BY date_debut ASC");
                if (rs != null) {
                    while (rs.next()) {
                        lcs.add(new Abonnement(rs.getInt("id_abonnement"), rs.getInt("id_client"), rs.getDate("date_debut"), rs.getDate("date_fin"), rs.getInt("etat_abonnement")));
                    }
                }
            } catch (SQLException ex) {
            }
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();

        return lcs;
    }

    public List<Abonnement> getStartAbonnementList(Date day) {
        List<Abonnement> lcs = new ArrayList<Abonnement>();

        if (connect()) {
            try {
                String now = new SimpleDateFormat("yyyy-MM-dd").format(day);
                ResultSet rs = execResult("SELECT id_abonnement, id_client, date_debut, date_fin, etat_abonnement FROM abonnement WHERE date_debut = '"+now+"' ORDER BY date_debut ASC");
                if (rs != null) {
                    while (rs.next()) {
                        lcs.add(new Abonnement(rs.getInt("id_abonnement"), rs.getInt("id_client"), rs.getDate("date_debut"), rs.getDate("date_fin"), rs.getInt("etat_abonnement")));
                    }
                }
            } catch (SQLException ex) {
            }
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();

        return lcs;
    }

}

package session;

import entity.ActivationPool;
import entity.DesactivationPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ActivationPoolFacade extends AbstractSQL {

    public ActivationPoolFacade() {
    }

    public List<ActivationPool> getActivationList() {
        List<ActivationPool> lcs = new ArrayList<ActivationPool>();

        if (connect()) {
            try {
                ResultSet rs = execResult("SELECT a.id_activation_pool, a.ip, a.mask, a.vlan, r.ip as ip_router, r.port, r.login, r.password, a.id_abonnement FROM activation_pool a JOIN routeur r ON a.num_routeur =r.num_routeur ORDER BY vlan ASC");
                if (rs != null) {
                    while (rs.next()) {
                        lcs.add(new ActivationPool(rs.getInt("id_activation_pool"), rs.getString("vlan"), rs.getString("ip"), rs.getString("mask"), rs.getString("ip_router"),rs.getString("login"),rs.getString("password"),rs.getInt("port"), rs.getInt("id_abonnement")));
                    }
                }
            } catch (SQLException ex) {
            }
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();

        return lcs;
    }
    
    public void remove(int id){
        if (connect()) {
            exec("DELETE FROM activation_pool WHERE id_activation_pool="+id);
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();
    }

}

package session;

import entity.DesactivationPool;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DesactivationPoolFacade extends AbstractSQL {

    public DesactivationPoolFacade() {
    }

    public List<DesactivationPool> getDesactivationList() {
        List<DesactivationPool> lcs = new ArrayList<DesactivationPool>();

        if (connect()) {
            try {
                ResultSet rs = execResult("SELECT a.id_desactivation_pool, a.ip, a.mask, a.vlan, r.ip as ip_router, r.port, r.login, r.password, a.id_abonnement FROM desactivation_pool a JOIN routeur r ON a.num_routeur =r.num_routeur ORDER BY vlan ASC");
                if (rs != null) {
                    while (rs.next()) {
                        lcs.add(new DesactivationPool(rs.getInt("id_desactivation_pool"), rs.getString("vlan"), rs.getString("ip"), rs.getString("mask"), rs.getString("ip_router"), rs.getString("login"), rs.getString("password"), rs.getInt("port"), rs.getInt("id_abonnement")));
                    }
                }
            } catch (SQLException ex) {
            }
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();
        return lcs;
    }

    public void remove(int id) {
        if (connect()) {
            exec("DELETE FROM desactivation_pool WHERE id_desactivation_pool=" + id);
        } else {
            System.out.println("Mysql connection failed !!!");
        }
        close();
    }
}
Télécharger le projet

Codes Sources

A voir également

S'inscrire maintenant

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes et codes sources.

Le fait d'être membre vous permet d'avoir des options supplémentaires.

Du même auteur (medkhd)