import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Time;
import java.util.Date;

public class readUser {
	/**
	* The JDBC driver to be used
	*/
	private static final String DRIVER = "org.postgresql.Driver";
	/**
	* The URL of the database to be accessed
	*/
	private static final String DATABASE = "jdbc:postgresql://localhost:5433/db1516";
	/**
	* The username for accessing the database
	*/
	private static final String USER = "db1516";
	/**
	* The password for accessing the database
	*/
	private static final String PASSWORD = "quaiyiva";
	/**
	* The SQL statement to be executed
	*/
	private static final String SQL = "SELECT E.*, TY.Nome_tipo, TH.Nome_tema FROM gallina.evento AS E INNER JOIN gallina.Tipo_evento AS TY  ON E.id_evento=TY.id_evento  INNER JOIN gallina.Tema_evento AS TH ON E.id_evento=TH.id_evento WHERE E.id_evento = '3bc566f8-ea1f-4f41-b822-72b5465d0c8e' UNION SELECT E.*, TY.Nome_tipo, TH.Nome_tema FROM gallina.evento AS E LEFT JOIN gallina.Tipo_evento AS TY  ON E.id_evento=TY.id_evento  LEFT JOIN gallina.Tema_evento AS TH ON E.id_evento=TH.id_evento WHERE E.id_sopra_evento='3bc566f8-ea1f-4f41-b822-72b5465d0c8e';";
	/**
	* List the special need of the participants
	*
	* @param args
	* command-line arguments (not used).
	*/
	public static void main(String[] args) {
	// the connection to the DBMS
		Connection con = null;
	// the statement to be executed
		Statement stmt = null;
	// the results of the statement execution
		ResultSet rs = null;
	// start time of a statement
		long start;
	// end time of a statement
		long end;
	// "data structures" for the data to be read from the database
	// the data about the participant

		String id_evento = null;
		String id_sopra_evento = null;
		String titolo = null, descrizione = null, via = null, cap = null, civico = null, nome = null, email = null, telefono = null;
		Date data_inizio = null, data_fine = null;
		Timestamp data_inizio_prenotazioni = null, data_fine_prenotazioni = null;
		Time orario_apertura = null, orario_chiusura = null;
		int numero_posti_disponibili = 0;

		try {
	// register the JDBC driver
			Class.forName(DRIVER);
			System.out.printf("Driver %s successfully registered.%n", DRIVER);
		} catch (ClassNotFoundException e) {
			System.out.printf("Driver %s not found: %s.%n", DRIVER, e.getMessage());
	// terminate with a generic error code
			System.exit(-1);
		}
		try {
	// connect to the database
			start = System.currentTimeMillis();
			con = DriverManager.getConnection(DATABASE, USER, PASSWORD);
			end = System.currentTimeMillis();
			System.out.printf("Connection to database %s successfully established in%,d milliseconds.%n",DATABASE, end-start);
	// create the statement to execute the query
			start = System.currentTimeMillis();
			stmt = con.createStatement();
			end = System.currentTimeMillis();
			System.out.printf("Statement successfully created in %,d milliseconds.%n",end-start);
	// execute the query
			start = System.currentTimeMillis();
			rs = stmt.executeQuery(SQL);
			end = System.currentTimeMillis();
			System.out.printf("Query %s successfully executed %,d milliseconds.%n",SQL, end - start);
			System.out.printf("Query results:%n");
	// cycle on the query results and print them
			while (rs.next()) {
				// Legge l'id dell'evento
				id_evento = rs.getString("id_evento");
				id_sopra_evento = rs.getString("id_sopra_evento");
				descrizione = rs.getString("descrizione");
				via = rs.getString("via");
				cap = rs.getString("cap");
				civico = rs.getString("civico");
				nome = rs.getString("nome");
				email = rs.getString("email");
				telefono = rs.getString("telefono");
				data_inizio = rs.getDate("data_inizio");
				data_fine = rs.getDate("data_fine");
				data_inizio_prenotazioni = rs.getTimestamp("data_inizio_prenotazioni");
				data_fine_prenotazioni = rs.getTimestamp("data_fine_prenotazioni");
				orario_apertura = rs.getTime("orario_apertura");
				orario_chiusura = rs.getTime("orario_chiusura");
				numero_posti_disponibili = rs.getInt("numero_posti_disponibili");

				// Legge il titolo dell'evento
				titolo = rs.getString("titolo");
				System.out.printf("- %s, %s, %s, %s, %s, %s, %s, %s, %s, %d, "+ data_inizio.toString() + ", " + data_fine.toString() + ", " + data_inizio_prenotazioni.toString() + ", " + data_fine_prenotazioni.toString() + ", " + orario_apertura.toString() + ", " + orario_chiusura.toString() + " %n", id_evento, id_sopra_evento, descrizione, via, cap, civico, nome, email, telefono, numero_posti_disponibili);
			}
		} catch (SQLException e) {
			System.out.printf("Database access error:%n");
	// cycle in the exception chain
			while (e != null) {
				System.out.printf("- Message: %s%n", e.getMessage());
				System.out.printf("- SQL status code: %s%n", e.getSQLState());
				System.out.printf("- SQL error code: %s%n", e.getErrorCode());
				System.out.printf("%n");
				e = e.getNextException();
			}
		} finally {
			try {
	// close the used resources
				if (rs != null) {
					start = System.currentTimeMillis();
					rs.close();
					end = System.currentTimeMillis();
					System.out.printf("Result set successfully closed in %,d milliseconds.%n",end-start);
				}
				if (stmt != null) {
					start = System.currentTimeMillis();
					stmt.close();
					end = System.currentTimeMillis();
					System.out
					.printf("Statement successfully closed in %,d milliseconds.%n",end-start);
				}
				if (con != null) {
					start = System.currentTimeMillis();
					con.close();
					end = System.currentTimeMillis();
					System.out
					.printf("Connection successfully closed in %,d milliseconds.%n",end-start);
				}
				System.out.printf("Resources successfully released.%n");
			} catch (SQLException e) {
				System.out.printf("Error while releasing resources:%n");
	// cycle in the exception chain
				while (e != null) {
					System.out.printf("- Message: %s%n", e.getMessage());
					System.out.printf("- SQL status code: %s%n",e.getSQLState());
					System.out.printf("- SQL error code: %s%n",e.getErrorCode());
					System.out.printf("%n");
					e = e.getNextException();
				}
			} finally {
	// release resources to the garbage collector
				rs = null;
				stmt = null;
				con = null;
				System.out.printf("Resources released to the garbage collector.%n");
			}
		}
		System.out.printf("Program end.%n");
	}
}