執行基礎 SQL 查詢

在 Java 中查詢 PostgreSQL 資料庫

Miller Trujillo

Staff Software Engineer

JDBC 中的 Statement

SELECT 使用 executeQuery()
INSERT、UPDATE、DELETE 使用 executeUpdate()

訊使般的 Statement

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

使用 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)
...

例外處理

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

重點回顧

Statement 將 SQL 指令送到資料庫
ResultSet 保存查詢結果，用 next() 迴圈讀取
取值方法：getInt()、getString()、getDate()
SELECT 用 executeQuery()，異動用 executeUpdate()
用 SQLException 處理錯誤

重點回顧

一起來練習吧！

在 Java 中查詢 PostgreSQL 資料庫