Esecuzione di query SQL di base

Eseguire query su un database PostgreSQL in Java

Miller Trujillo

Staff Software Engineer

Statements in JDBC

Usa executeQuery() per le SELECT
Usa executeUpdate() per INSERT, UPDATE, DELETE

Statement come messaggero

Creare uno Statement

String sql = "SELECT book_id, title, publication_year FROM books";

try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

Leggere i risultati con ResultSet

String sql = "SELECT book_id, title, publication_year FROM books";
try (Connection conn = DriverManager.getConnection(url, user, password);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery(sql)) {

  while (rs.next()) {

    int id = rs.getInt("book_id");
    String title = rs.getString("title");
    int year = rs.getInt("publication_year");
    System.out.println(id + " | " + title + " (" + year + ")");}}

1 | Effective Java (2018)
2 | Clean Code (2008)
...

Gestire le eccezioni

try {
    Statement stmt = conn.createStatement();

    stmt.executeQuery("SELECT * FROM unknown_table");

} catch (SQLException e) {

    System.err.println("Errore: " + e.getMessage());
    System.err.println("SQLState: " + e.getSQLState()); 
}

Errore: ERROR: relation "unknown_table" does not exist
  Position: 15
SQLState: 42P01

Riepilogo

Statement invia comandi SQL al database
ResultSet contiene i risultati, cicla con next()
Metodi getter: getInt(), getString(), getDate()
executeQuery() per SELECT, executeUpdate() per modifiche
Gestisci errori con SQLException

Riepilogo

Facciamo pratica!

Eseguire query su un database PostgreSQL in Java