Ejecutar consultas SQL básicas

Consultas a una base de datos PostgreSQL en Java

Miller Trujillo

Staff Software Engineer

Declaraciones en JDBC

Usa executeQuery() para SELECT
Usa executeUpdate() para INSERT, UPDATE, DELETE

Declaración como mensajero

Crear una declaración

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)) {

Leer resultados 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)
...

Manejo de excepciones

try {
    Statement stmt = conn.createStatement();

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

} catch (SQLException e) {

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

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

Resumen

Statement envía comandos SQL a la base de datos
ResultSet contiene resultados, recorre con next()
Métodos getter: getInt(), getString(), getDate()
executeQuery() para SELECT, executeUpdate() para modificaciones
Maneja errores con SQLException

Resumen

¡Vamos a practicar!

Consultas a una base de datos PostgreSQL en Java