package funclubs;
import java.sql.*;
import java.io.*;
import java.util.*;

class Funclubs{

   static {
      try {
         // register the driver with DriverManager
         Class.forName("org.postgresql.Driver").newInstance();
      } catch (Exception e) {
         e.printStackTrace();
      }
   }

   public static void main(String argv[]) {
      Connection con = null;
	try{


            // Connect to DB. URL is jdbc:postgresql:DB
            String url = "jdbc:postgresql://127.0.0.1/postgres";
            String userid = "postgres";
            String passwd = "toto";
            int executeUpdate;
            con = DriverManager.getConnection(url, userid, passwd);

            System.out.println("...sono pronto per iniziare. Vai!");


            Statement stmt = con.createStatement();
            stmt.executeUpdate("DROP table  IF EXISTS mondiali2010.funclubs CASCADE;");
            executeUpdate = stmt.executeUpdate("create table mondiali2010.funclubs("
                    + "nazione varchar(20) primary key,"
                    + "sponsor varchar not null)");
            stmt.executeUpdate("DROP table IF EXISTS mondiali2010.socio CASCADE;");
            executeUpdate = stmt.executeUpdate("create table mondiali2010.socio("
                    + "idsocio int primary key,"
                    + "nome char(10) not null,"
                    + "sesso char(1) not null)");
           stmt.executeUpdate("DROP table  IF EXISTS mondiali2010.iscrizione CASCADE;");
           executeUpdate = stmt.executeUpdate("create table mondiali2010.iscrizione("
                    + "socio int references mondiali2010.socio, "
                    + "fun_club varchar(20) references mondiali2010.funclubs)");
            ResultSet rs;
          rs = stmt.executeQuery("select nazione,sponsor from mondiali2010.squadra;");
          PreparedStatement pstmt = con.prepareStatement("insert into mondiali2010.funclubs values (?,?);")copypaste;
          while(rs.next()){
              String n=rs.getString("nazione");
              String s=rs.getString("sponsor");
              System.out.println(n+s);
              pstmt.setString(1, n);
              pstmt.setString(2, s);
              pstmt.executeUpdate();

          }
          //ResultSet rs=stmt.executeQuery("insert into mondiali2010.funclubs (?,?)");
          // Punto 1. Esecuzione query. Da completare.

            //Punto 2: Stampa risultato query. Da completare.

            //Chiusura oggetti: Da completare.
            System.out.println("...chiudo tutto!");
            stmt.close();
            con.close();
        } catch( Exception e ) {
         e.printStackTrace();
        }
   }
}