// Jdbc1.java

/*
drop table t;
create table t(nom varchar2(10) primary key, age integer);
insert into t values('toto', 19);
insert into t values('titi', 21);
insert into t values('tutu', 24);
commit;

create or replace procedure rajeunit(n t.nom%type) is
begin
  update t set age = age - 1 where nom = n;
end;
/

create or replace function f return integer is
  n integer;
begin
  select count(*) into n from t;
  return n;
end;
/
*/

import java.io.*;
import java.sql.*;

class Jdbc1 {

  static void sqldyn(Connection c) throws SQLException {
    Statement stmt = c.createStatement();
    String texteOrdre = "drop table ";
    System.out.print("entrez un nom de table : ");
    texteOrdre += lireClavier();
    stmt.executeUpdate(texteOrdre);
    stmt.close();
  }

  static void curseur1(Connection c)  throws SQLException {
    Statement stmt = c.createStatement();
    ResultSet rset = stmt.executeQuery("select nom from t where age > 20");
    while (rset.next())
	System.out.println(rset.getString(1));
    rset.close();
    stmt.close();
  }

  static void appelFonct1(Connection c) throws SQLException {
    CallableStatement cstmt = c.prepareCall("{? = call f}");
    cstmt.registerOutParameter(1, Types.INTEGER);
    cstmt.execute();
    // cstmt.executeUpdate(); // ca marche aussi...
    int n = cstmt.getInt(1);
    cstmt.close();
    System.out.println("nombre de lignes = "+n);
  }

  static void appelProc1(Connection c) throws SQLException {
    CallableStatement cstmt = c.prepareCall("{call rajeunit(?)}");
    System.out.print("entrez le nom : ");
    String nom = lireClavier();
    cstmt.setString(1, nom);
    int nb = cstmt.executeUpdate();
    cstmt.close();
    System.out.println("nb valeurs traitees = "+nb);
    c.commit();
  }

  static void miseAJour3(Connection c) throws SQLException {
    PreparedStatement pstmt = c.prepareStatement(      
      "update t set age = age + 1 where nom = ? or age <= ?");

    for (int i=0; i<2; i++) {
      System.out.print("entrez le nom : ");
      String nom = lireClavier();
      System.out.print("entrez l'age : ");
      int age = Integer.parseInt(lireClavier());
      pstmt.setString(1, nom);
      pstmt.setInt(2, age);
      int nb = pstmt.executeUpdate();
      System.out.println("nb valeurs traitees = "+nb);
      c.commit();
    }
    pstmt.close();
  }

  static void miseAJour2(Connection c) throws SQLException {
    Statement stmt = c.createStatement();
    System.out.print("entrez le nom : ");
    String nom = lireClavier();
    System.out.print("entrez l'age : ");
    int age = Integer.parseInt(lireClavier());
    int nb = stmt.executeUpdate(
      "update t set age = age + 1 where nom = '"+nom+"' or age <= "+age);
    stmt.close();
    System.out.println("nb valeurs traitees = "+nb);
    c.commit();
  }

  static void miseAJour1(Connection c) throws SQLException {
    Statement stmt = c.createStatement();
    int nb = stmt.executeUpdate(
      "update t set age = age + 1 where nom = 'toto' or age <= 22");
    stmt.close();
    System.out.println("nb valeurs traitees = "+nb);
    c.commit();
  }

  static void deconnexion(Connection c) throws SQLException {
    c.close();
  }

  static Connection connexion() throws SQLException, ClassNotFoundException {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    // System.out.print("entrez le login : ");
    String login = "waller_a"; // lireClavier();
    Connection c = DriverManager.getConnection(
      "jdbc:oracle:thin:"+login+"/AQWzsx34"+"@servora:1521:dbinfo");
    c.setAutoCommit(false);
    return c;
  }

  static void scriptSQL() throws Exception {
      // ne marche pas, y compris depuis la ligne de commande...
    Runtime r = Runtime.getRuntime();
    Process p = r.exec("sqlplus waller/waller @jdbc1.sql");
    p.waitFor();
  }

  static String lireClavier() {
    try {
      BufferedReader clavier =
        new BufferedReader(new InputStreamReader(System.in));
      return clavier.readLine();
    } catch (Exception e) {
      return "erreur dans fonction lireClavier";
    }
  }

  public static void main(String[] args) 
    throws // SQLException, ClassNotFoundException, 
      Exception {

    Connection c = null;
    c = connexion();

    while (true) {
      System.out.println("-----------------------------");
      System.out.println("Bienvenue dans le menu JDBC 1");
      System.out.println("0 : scriptSQL");
      System.out.println("1 : connexion");
      System.out.println("2 : deconnexion");
      System.out.println("3 : terminer");
      System.out.println("4 : miseAJour1");
      System.out.println("5 : miseAJour2");
      System.out.println("6 : miseAJour3");
      System.out.println("7 : appelProc1");
      System.out.println("8 : appelFonct1");
      System.out.println("9 : curseur1");
      System.out.println("10 : sqldyn");
      System.out.print("Entrez votre choix : ");
      int n = Integer.parseInt(lireClavier());
      switch (n) {
        case 0 : scriptSQL(); break;
        case 1 : c = connexion(); break;
        case 2 : deconnexion(c); break;
        case 3 : return;
        case 4 : miseAJour1(c); break;
        case 5 : miseAJour2(c); break;
        case 6 : miseAJour3(c); break;
        case 7 : appelProc1(c); break;
        case 8 : appelFonct1(c); break;
        case 9 : curseur1(c); break;
        case 10 : sqldyn(c); break;
      }
    }
  }
}