package ex3;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Main {
   public static void main(String args[]) {
      Connection c = null;
      Statement statement = null;


      try {//Activitat 5 (connexió a la base de dades)
         Class.forName("org.postgresql.Driver");
         c = DriverManager
            .getConnection("jdbc:postgresql://localhost:5432/escola",
            "postgres", "postgres");

      } catch (Exception e) {
         e.printStackTrace();
         System.err.println(e.getClass().getName()+": "+e.getMessage());
         System.exit(0);
      }
      System.out.println("Base de dades correctament.\n");


    try{//Activitat 5 (mostrar elements taula)
      statement = c.createStatement();

      ResultSet rs = statement.executeQuery("select * from persona;");
      while (rs.next()){
    	  String dni = rs.getString("dni");
          String nom = rs.getString("nom");
          String cognom  = rs.getString("cognom");
          String carrer = rs.getString("carrer");
          String ciutat = rs.getString("ciutat");
          int cod_pos = rs.getInt("cod_pos");
          int tel = rs.getInt("tel");
          System.out.println( "DNI = " + dni );
          System.out.println( "NOM = " + nom );
          System.out.println( "COGNOM = " + cognom );
          System.out.println( "CARRER = " + carrer );
          System.out.println( "CIUTAT = " + ciutat );
          System.out.println( "CODI POSTAL = " + cod_pos );
          System.out.println( "TELÈFON = " + tel );
          System.out.println();
      }

      statement.close();
      c.close();

    } catch ( Exception e ) {
      System.err.println( e.getClass().getName()+": "+ e.getMessage() );
      System.exit(0);
    }
    System.out.println("Consulta feta correctament.\n");


    try{//Exercici 6 (creació nova taula)
        statement = c.createStatement();

        String sql = "create table materials("
        		+ "codi varchar(3),"
        		+ "descripcio varchar(100),"
        		+ "stock float(3),"
        		+ "preu float(6),"
        		+ "primary key (codi));";
        statement.executeUpdate(sql);

        statement.close();
        c.close();

      } catch ( Exception e ) {
        System.err.println( e.getClass().getName()+": "+ e.getMessage() );
        System.exit(0);
      }
      System.out.println("Taula creada correctament.\n");


     try{//Exercici 7 (insercions)
       statement = c.createStatement();

       String sql = "insert into alumne (dni, nom, cognom, carrer, ciutat, cod_pos, tel, anymatriculacio)"
    		   + "values ('12345678A', 'Ian', 'Talamantes', 'C/ All 5', 'Barcelona', 12345, 123456789, 1);";
       statement.executeUpdate(sql);

       sql = "insert into alumne (dni, nom, cognom, carrer, ciutat, cod_pos, tel, anymatriculacio)"
    		   + "values ('23456789B', 'Jose', 'Fernandez', 'C/ All 5', 'Barcelona', 12345, 234567890, 2);";
       statement.executeUpdate(sql);

       sql = "insert into professor (dni, nom, cognom, carrer, ciutat, cod_pos, tel, departament)"
    		   + "values ('34567890C', 'Tipo', 'de Incognito', 'C/ Pebrot 6', 'Barcelona', 12345, 2345678901, 3);";
       statement.executeUpdate(sql);

       sql = "insert into professor (dni, nom, cognom, carrer, ciutat, cod_pos, tel, departament)"
    		   + "values ('45678901D', 'Cosme', 'Fulanito', 'C/ Pebrot 7', 'Barcelona', 12345, 3456789012, 4);";
       statement.executeUpdate(sql);

       statement.close();
       c.close();

     } catch ( Exception e ) {
       System.err.println( e.getClass().getName()+": "+ e.getMessage() );
       System.exit(0);
     }
     System.out.println("Insercions creades correctament.\n");


     try{//Exercici 8 (consulta alumne)
         statement = c.createStatement();
         String sql = "select * from assignatura where idalumne='12345678A';";
         statement.executeUpdate(sql);

         statement.close();
         c.close();

       } catch ( Exception e ) {
         System.err.println( e.getClass().getName()+": "+ e.getMessage() );
         System.exit(0);
       }
       System.out.println("Consulta feta correctament.\n");


       try{//Exercici 9 (consulta 2 taules)
           statement = c.createStatement();

           ResultSet rs = statement.executeQuery("select * from alumne;");
           while (rs.next()){
         	  String dni = rs.getString("dni");
               String nom = rs.getString("nom");
               String cognom  = rs.getString("cognom");
               String carrer = rs.getString("carrer");
               String ciutat = rs.getString("ciutat");
               int cod_pos = rs.getInt("cod_pos");
               int tel = rs.getInt("tel");
               int anymatriculacio = rs.getInt("anymatriculacio");
               System.out.println( "DNI = " + dni );
               System.out.println( "NOM = " + nom );
               System.out.println( "COGNOM = " + cognom );
               System.out.println( "CARRER = " + carrer );
               System.out.println( "CIUTAT = " + ciutat );
               System.out.println( "CODI POSTAL = " + cod_pos );
               System.out.println( "TELÈFON = " + tel );
               System.out.println( "ANY MATRICULACIÓ = " + anymatriculacio );
               System.out.println();
           }

           rs = statement.executeQuery("select * from professor;");
           while (rs.next()){
         	  String dni = rs.getString("dni");
               String nom = rs.getString("nom");
               String cognom  = rs.getString("cognom");
               String carrer = rs.getString("carrer");
               String ciutat = rs.getString("ciutat");
               int cod_pos = rs.getInt("cod_pos");
               int tel = rs.getInt("tel");
               int departament = rs.getInt("departament");
               System.out.println( "DNI = " + dni );
               System.out.println( "NOM = " + nom );
               System.out.println( "COGNOM = " + cognom );
               System.out.println( "CARRER = " + carrer );
               System.out.println( "CIUTAT = " + ciutat );
               System.out.println( "CODI POSTAL = " + cod_pos );
               System.out.println( "TELÈFON = " + tel );
               System.out.println( "DEPARTAMENT = " + departament );
               System.out.println();
           }

           statement.close();
           c.close();

         } catch ( Exception e ) {
           System.err.println( e.getClass().getName()+": "+ e.getMessage() );
           System.exit(0);
         }
         System.out.println("Consulta feta correctament.");


   }
}