import java.sql.*;
import oracle.jdbc.OracleTypes;

public class Test
{
    public static void main(String [ ] args) throws SQLException {
        //Cha�nes de connexion
        String ur1= "jdbc:oracle:thin:@205.237.244.251:1521:orcl";
        String ur2="augermat";
        String ur3="ORACLE1";
        Connection connexion =null;
        CallableStatement callstm=null;
        CallableStatement callstmt=null;
        CallableStatement callstm1=null;

        //Charger le driver
        try
        {
            Class.forName("oracle.jdbc.OracleDriver");
            System.out.println("Driver charge");
        }
        catch (ClassNotFoundException cnfe)
        {
            System.out.println("driver manquant");
        }

        //Connexion et fermeture de connexion
        try
        {
            connexion =DriverManager.getConnection(ur1, ur2, ur3);
            System.out.println("connecte");
        }
        catch (SQLException se)
        {
            System.out.println(se);
        }

        //Afficher des enregistrements
        Statement stm2= null;
        ResultSet rst=null;
        String sql2 = "select NOMEMP, PRENOMEMP, SALAIRE from employesbidon";
        try {
            stm2=connexion.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            rst= stm2.executeQuery(sql2);

            //Chaines
            String noms = null;
            String prenoms = null;
            String salaires = null;

            //Premier enregistrement
            System.out.println();
            System.out.print("Premier enregistrement");
            System.out.println();
            rst.first();
            noms = rst.getString(1);
            prenoms = rst.getString(2);
            salaires = rst.getString(3);
            System.out.print(noms +" "+ prenoms+" "+salaires);
            System.out.println();

            //Dernier enregistrement
            System.out.println();
            System.out.print("Dernier enregistrement");
            System.out.println();
            rst.last();
            noms = rst.getString(1);
            prenoms = rst.getString(2);
            salaires = rst.getString(3);
            System.out.print(noms +" "+ prenoms+" "+salaires);
            System.out.println();

            //Troisi�me enregistrement
            System.out.println();
            System.out.print("Troisieme enregistrement");
            System.out.println();
            rst.absolute(3);
            noms = rst.getString(1);
            prenoms = rst.getString(2);
            salaires = rst.getString(3);
            System.out.print(noms +" "+ prenoms+" "+salaires);
            System.out.println();

            //Troisi�me enregistrement � partir de celui courant, le 6i�me
            System.out.println();
            System.out.print("Troisieme enregistrement a partir de celui courant");
            System.out.println();
            rst.absolute(rst.getRow()+3);
            noms = rst.getString(1);
            prenoms = rst.getString(2);
            salaires = rst.getString(3);
            System.out.print(noms +" "+ prenoms+" "+salaires);
            System.out.println();

            //On obtient la premiere rang�e avec rst.absolute(1) et le dernier avec rst.absolute(-1);

            //L'enregistrement avant le premier est avant l'objet

            //Faire lecture inverse
            System.out.println();
            System.out.print("Lecture inverse");
            System.out.println();
            rst.last();
            noms = rst.getString(1);
            prenoms = rst.getString(2);
            salaires = rst.getString(3);
            System.out.print(noms +" "+ prenoms+" "+salaires);
            System.out.println();
            while(rst.previous())
            {
                noms = rst.getString(1);
                prenoms = rst.getString(2);
                salaires = rst.getString(3);
                System.out.print(noms +" "+ prenoms+" "+salaires);
                System.out.println();
            }
        }
        catch(SQLException se1) {System.out.println (se1); }

        //Insertions et updates
        try
        {
            System.out.println();
            System.out.print("Insertions: ");
            System.out.println();
            //Insertion
            String insertTableSQL = "INSERT INTO employesbidon"
                    + "(numemp, nomemp, prenomemp, emploi, salaire) VALUES"
                    + "(SEQ1.nextval,?,?,?,?)";
            PreparedStatement preparedStatement = connexion.prepareStatement(insertTableSQL);
            preparedStatement.setString(1, "Saliha");
            preparedStatement.setString(2, "Yacoub");
            preparedStatement.setString(3, "FINANCES");
            preparedStatement.setInt(4, 35000);
            // execute insert SQL statement
            preparedStatement .executeUpdate();
            System.out.println("Insertion fonctionnel");

            //Update du salaire
            String sqlupdate ="update employesbidon set salaire = ? where numemp = ?";
            PreparedStatement stm = connexion.prepareStatement(sqlupdate);
            stm.setInt(1,35000 );
            stm.setInt(2, 50);
            stm.executeUpdate();
            System.out.println("Update fonctionnel");
            stm.clearParameters();

            //Affichage selon emploi avec preparedstatement en ordre et en ordre inverse
            System.out.println();
            System.out.print("Affichage selon emploi avec preparedstatement");
            System.out.println();
            String selectSQL = "select NOMEMP, PRENOMEMP, SALAIRE from employesbidon WHERE emploi = ?";
            PreparedStatement Pstatement = connexion.prepareStatement(selectSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            Pstatement.setString(1, "PROGRAMMEUER");
            ResultSet rs = Pstatement.executeQuery();
            String noms = null;
            String prenoms = null;
            String salaires = null;
            while (rs.next())
            {
                noms = rs.getString(1);
                prenoms = rs.getString(2);
                salaires = rs.getString(3);
                System.out.print(noms +" "+ prenoms+" "+salaires);
                System.out.println();
            }
            System.out.println();
            System.out.print("Affichage inverse selon emploi avec preparedstatement");
            System.out.println();
            while (rs.previous())
            {
                noms = rs.getString(1);
                prenoms = rs.getString(2);
                salaires = rs.getString(3);
                System.out.print(noms +" "+ prenoms+" "+salaires);
                System.out.println();
            }

        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
        try
        {
            System.out.println();
            System.out.print("Appel de la function lister avec CODEDEP = INF");
            System.out.println();
            callstm = connexion.prepareCall("{?=call GESTIONEMPLOYES.LISTER(?)}");
            callstm.setString(2,"INF");
            callstm.registerOutParameter(1, OracleTypes.CURSOR);
            callstm.execute();
            ResultSet resultat=(ResultSet)callstm.getObject(1);

            while(resultat.next())
            {
                String resultatnom = resultat.getString("NOMEMP");
                System.out.println(resultatnom);
            }

        }
        catch (SQLException se){}
        try
        {
            System.out.println();
            System.out.print("Appel de la procedure Insertion");
            System.out.println();
            callstmt = connexion.prepareCall("{call GESTIONEMPLOYES.INSERTION(?, ?, ?, ?)}");
            callstmt.setString(1, "Pablo");
            callstmt.setString(2, "Lievre");
            callstmt.setInt(3, 72000);
            callstmt.setString(4, "INF");
            callstmt.execute();
            System.out.print("Insertion Fonctionnel");
        }
        catch (SQLException se){}
        try
        {
            System.out.println();
            System.out.print("Total:");
            System.out.println();
            callstm1 = connexion.prepareCall("{?=call GESTIONEMPLOYES.Total()}");
            callstm1.registerOutParameter(1, OracleTypes.INTEGER);
            callstm1.execute();
            int total=callstm1.getInt(1);
            System.out.println(total);
        }
        catch (SQLException se){}
        finally
        {
            try {
                stm2.close();
                rst.close();
                rst.close();
                callstm.close();
                callstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try
            {
                if (connexion != null)
                {
                    connexion.close();}
            }
            catch (SQLException se){}
        }
    }
}