PostgreSQL Java 教程 · ZetCode 中文系列教程

# PostgreSQL Java 教程 > 原文： [http://zetcode.com/db/postgresqljavatutorial/](http://zetcode.com/db/postgresqljavatutorial/) 這是 PostgreSQL 數據庫的 Java 教程。它涵蓋了使用 Java 進行 PostgreSQL 編程的基礎。 [Tweet](https://twitter.com/share) 在 ZetCode 上，有類似的相關教程[，MySQL Java 教程](/db/mysqljava/)， [Spring JdbcTemplate 教程](/db/jdbctemplate/)和 [Apache Derby 教程](/db/apachederbytutorial/)。 ## JDBC _JDBC_ 是 Java 編程語言的 API，用于定義客戶端如何訪問數據庫。它提供了查詢和更新數據庫中數據的方法。 JDBC 面向關系數據庫。從技術角度來看，API 是`java.sql`包中的一組類。要將 JDBC 與特定數據庫一起使用，我們需要該數據庫的 JDBC 驅動程序。 ## 關于 PostgreSQL 數據庫 PostgreSQL 是一個功能強大的開源對象關系數據庫系統。它是一個多用戶數據庫管理系統。它可以在包括 Linux，FreeBSD，Solaris，Microsoft Windows 和 Mac OS X 在內的多個平臺上運行。PostgreSQL 由 PostgreSQL 全球開發小組開發。 ## PostgreSQL 驅動要包含 PostgreSQL Java 驅動程序，我們需要添加以下 Maven 依賴項： ```java <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>42.2.0</version> </dependency> ``` ## Maven Exec 插件為了從 Maven 運行命令行應用，我們可以使用以下 Maven 插件。 ```java <build> <plugins> <plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>exec-maven-plugin</artifactId> <version>1.6.0</version> <configuration> <mainClass>com.zetcode.AppName</mainClass> </configuration> </plugin> </plugins> </build> ``` 我們可以使用`mvn -q exec:java`命令運行該應用。在`mainClass`標記中，編寫主類的完全限定名稱，例如`com.zetcode.JavaPostgreSqlVersion`。 `-q`以完全模式運行 Maven；它禁用除錯誤消息外的所有 Maven 消息。 ## PostgreSQL 設置我們將展示如何在 Debian Linux 系統上安裝 PostgreSQL 數據庫。 ```java $ sudo apt-get install postgresql ``` 此命令將安裝 PostgreSQL 服務器和相關包。 ```java $ /etc/init.d/postgresql status ``` 我們使用`postgresql status`命令檢查數據庫的狀態。 ```java $ sudo -u postgres psql postgres psql (9.5.10) Type "help" for help. postgres=# \password postgres Enter new password: Enter it again: ``` 安裝后，將使用空的默認密碼創建一個具有管理權限的`postgres`用戶。第一步，我們需要為`postgres`設置密碼。 ```java $ sudo -u postgres createuser --interactive --password user12 Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) n Password: ``` 我們創建一個新的數據庫用戶。 ```java $ sudo -u postgres createdb testdb -O user12 ``` 我們使用`createdb`命令創建一個新的`testdb`數據庫，該數據庫將由`user12`擁有。 ```java $ sudo vi /etc/postgresql/9.5/main/pg_hba.conf ``` 我們編輯`pg_hba.conf`文件。 ```java # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust ``` 為了能夠在本地 PostgreSQL 安裝中運行 Spring Boot 應用，我們將 Unix 域套接字和本地連接的認證方法更改為`trust`。 ```java $ sudo service postgresql restart ``` 我們重新啟動 PostgreSQL 以啟用更改。 ```java $ psql -U user12 -d testdb -W Password for user user12: psql (9.5.10) Type "help" for help. testdb=> ``` 現在我們可以使用`psql`工具連接到數據庫。 ## 從啟動腳本中刪除 PostgreSQL 如果我們從包中安裝 PostgreSQL 數據庫，它將自動添加到操作系統的啟動腳本中。如果我們僅學習使用數據庫，則不必在每次引導系統時都啟動數據庫。 ```java $ sudo update-rc.d -f postgresql remove Removing any system startup links for /etc/init.d/postgresql ... /etc/rc0.d/K21postgresql /etc/rc1.d/K21postgresql /etc/rc2.d/S19postgresql /etc/rc3.d/S19postgresql /etc/rc4.d/S19postgresql /etc/rc5.d/S19postgresql /etc/rc6.d/K21postgresql ``` 上面的命令刪除 PostgreSQL 數據庫的所有系統啟動鏈接。 ## Java PostgreSQL 版本如果以下程序運行正常，則我們已安裝一切正常。我們檢查 PostgreSQL 服務器的版本。 `JavaPostgreSqlVersion.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlVersion { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try (Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); ResultSet rs = st.executeQuery("SELECT VERSION()")) { if (rs.next()) { System.out.println(rs.getString(1)); } } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlVersion.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 我們連接到數據庫，并獲取有關 PostgreSQL 服務器的一些信息。 ```java String url = "jdbc:postgresql://localhost:5432/testdb"; ``` 這是 PostgreSQL 數據庫的連接 URL。每個驅動程序對于 URL 都有不同的語法。在本例中，我們提供一個主機（localhost），一個端口（5432）和一個數據庫名稱（`testdb`）。 ```java try (Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); ResultSet rs = st.executeQuery("SELECT VERSION()")) { ``` 使用`getConnection()`建立與數據庫的連接。連接對象的`createStatement()`方法創建一個`Statement`對象，用于將 SQL 語句發送到數據庫。 `executeQuery()`執行給定的 SQL 語句，該語句返回單個`ResultSet`對象。 `ResultSet`是由特定 SQL 語句返回的數據表。 `try-with-resources`語句可確保在語句末尾關閉每個資源。 ```java if (rs.next()) { System.out.println(rs.getString(1)); } ``` `ResultSet`對象維護一個游標，該游標指向其當前數據行。最初，光標位于第一行之前。 `next()`方法將光標移動到下一行。如果沒有剩余的行，則該方法返回`false`。 `getString()`方法檢索指定列的值。第一列的索引為 1。 ```java } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlVersion.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } ``` 如果發生異常，我們將記錄錯誤消息。 ```java $ mvn -q exec:java PostgreSQL 9.5.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit ``` 這是程序的示例輸出。 ## 創建和填充表接下來，我們將創建數據庫表并用數據填充它們。這些表將在本教程中使用。 `authors_books_postgresql.sql` ```java DROP TABLE IF EXISTS books, authors, testing, images; CREATE TABLE IF NOT EXISTS authors ( id serial PRIMARY KEY, name VARCHAR(25) ); CREATE TABLE IF NOT EXISTS books ( id serial PRIMARY KEY, author_id INT references authors(id), title VARCHAR(100) ); CREATE TABLE IF NOT EXISTS testing(id INT); CREATE TABLE IF NOT EXISTS images(id serial, data bytea); INSERT INTO authors(id, name) VALUES(1, 'Jack London'); INSERT INTO authors(id, name) VALUES(2, 'Honore de Balzac'); INSERT INTO authors(id, name) VALUES(3, 'Lion Feuchtwanger'); INSERT INTO authors(id, name) VALUES(4, 'Emile Zola'); INSERT INTO authors(id, name) VALUES(5, 'Truman Capote'); INSERT INTO books(id, author_id, title) VALUES(1, 1, 'Call of the Wild'); INSERT INTO books(id, author_id, title) VALUES(2, 1, 'Martin Eden'); INSERT INTO books(id, author_id, title) VALUES(3, 2, 'Old Goriot'); INSERT INTO books(id, author_id, title) VALUES(4, 2, 'Cousin Bette'); INSERT INTO books(id, author_id, title) VALUES(5, 3, 'Jew Suess'); INSERT INTO books(id, author_id, title) VALUES(6, 4, 'Nana'); INSERT INTO books(id, author_id, title) VALUES(7, 4, 'The Belly of Paris'); INSERT INTO books(id, author_id, title) VALUES(8, 5, 'In Cold blood'); INSERT INTO books(id, author_id, title) VALUES(9, 5, 'Breakfast at Tiffany'); ``` 我們有一個`authors_books_postgresql.sql`文件。它創建四個數據庫表，作者，書籍，測試和圖像。我們在`books`表的`author_id`列上放置一個外鍵約束。我們用初始數據填充作者和書籍表。 ```java $ psql -U user12 -d testdb -W Password for user user12: psql (9.5.10) Type "help" for help. testdb=> \i authors_books_postgresql.sql psql:authors_books_postgresql.sql:1: NOTICE: table "books" does not exist, skipping psql:authors_books_postgresql.sql:1: NOTICE: table "authors" does not exist, skipping psql:authors_books_postgresql.sql:1: NOTICE: table "testing" does not exist, skipping psql:authors_books_postgresql.sql:1: NOTICE: table "images" does not exist, skipping DROP TABLE CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE INSERT 0 1 INSERT 0 1 ... ``` _psql_ 是 PostgreSQL 的基于終端的前端。我們可以使用它來執行交互式查詢，將其發布到 PostgreSQL，并查看查詢結果。在`psql`工具內部，我們導入并執行`authors_books_postgresql.sql`文件。 ```java testdb=> \dt List of relations Schema | Name | Type | Owner --------+---------+-------+-------- public | authors | table | user12 public | books | table | user12 public | cities | table | user12 public | images | table | user12 public | testing | table | user12 (5 rows) ``` 我們檢查創建的表。 ## Java PostgreSQL 預備語句現在，我們將以預備語句來關注自己。在編寫預備語句時，我們使用占位符，而不是直接將值寫入語句中。預準備的語句可提高安全性和性能。在 Java 中，`PreparedStatement`是代表預編譯的 SQL 語句的對象。 `JavaPostgreSqlPrepared.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlPrepared { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; int id = 6; String author = "Trygve Gulbranssen"; String query = "INSERT INTO authors(id, name) VALUES(?, ?)"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query)) { pst.setInt(1, id); pst.setString(2, author); pst.executeUpdate(); } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlPrepared.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 我們將新作者添加到`authors`表中。 ```java String query = "INSERT INTO authors(id, name) VALUES(?, ?)"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query)) { ``` 在這里，我們創建一個預備語句。在編寫預備語句時，我們使用占位符，而不是直接將值寫入語句中。預備語句更快，并且可以防止 SQL 注入攻擊。 `?`是一個占位符，稍后將填充。在我們的例子中，我們將填充兩個值：一個整數 id 和一個字符串名。 ```java pst.setInt(1, id); ``` 整數值綁定到占位符。 ```java pst.setString(2, author); ``` 字符串值綁定到占位符。 ```java pst.executeUpdate(); ``` 執行預備語句。當我們不希望返回任何數據時，我們使用語句對象的`executeUpdate()`方法。這是當我們創建數據庫或執行`INSERT`，`UPDATE`和`DELETE`語句時。 ```java testdb=> SELECT * FROM Authors; id | name ----+-------------------- 1 | Jack London 2 | Honore de Balzac 3 | Lion Feuchtwanger 4 | Emile Zola 5 | Truman Capote 6 | Trygve Gulbranssen (6 rows) ``` 我們在表中插入了一位新作者。 ## Java PostgreSQL 檢索數據接下來，我們將展示如何從數據庫表中檢索數據。我們從`authors`表中獲取所有數據。 `JavaPostgreSqlRetrieve.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlRetrieve { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement("SELECT * FROM authors"); ResultSet rs = pst.executeQuery()) { while (rs.next()) { System.out.print(rs.getInt(1)); System.out.print(": "); System.out.println(rs.getString(2)); } } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlRetrieve.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 我們從`authors`表中獲取所有作者，并將它們打印到控制臺。 ```java try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement("SELECT * FROM authors"); ResultSet rs = pst.executeQuery()) { ``` 我們執行一個查詢，該查詢從`authors`表中選擇所有列。我們使用`executeQuery()`方法。該方法執行給定的 SQL 語句，該語句返回單個`ResultSet`對象。 `ResultSet`是 SQL 查詢返回的數據表。 ```java while (rs.next()) { System.out.print(rs.getInt(1)); System.out.print(": "); System.out.println(rs.getString(2)); } ``` `next()`方法將光標移至下一條記錄。當結果集中沒有更多行時，它將返回`false`。 `getInt()`和`getString()`方法以 Java 編程語言中`int`和`String`的形式檢索此`ResultSet`對象當前行中指定列的值。 ```java $ mvn -q exec:java 1: Jack London 2: Honore de Balzac 3: Lion Feuchtwanger 4: Emile Zola 5: Truman Capote 6: Trygve Gulbranssen ``` 作者的 ID 和名稱將打印到控制臺。 ## 屬性通常的做法是將配置數據放在程序外部的單獨文件中。這樣程序員可以更加靈活。我們可以更改用戶，密碼或連接 URL，而無需重新編譯程序。在 Java 中，`Properties`是為此經常使用的類。該類用于輕松讀取和保存鍵/值屬性。 `database.properties` ```java db.url=jdbc:postgresql://localhost:5432/testdb db.user=user12 db.passwd=34klq* ``` 我們有一個`database.properties`文件，其中有三個鍵/值對。它們在程序執行期間被加載。 `JavaPostgreSqlRetrieveProperties.java` ```java package com.zetcode; import java.io.BufferedReader; import java.io.FileInputStream; import java.io.IOException; import java.nio.charset.StandardCharsets; import java.nio.file.Files; import java.nio.file.Path; import java.nio.file.Paths; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Properties; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlRetrieveProperties { public static Properties readProperties() { Properties props = new Properties(); Path myPath = Paths.get("src/main/resources/database.properties"); try { BufferedReader bf = Files.newBufferedReader(myPath, StandardCharsets.UTF_8); props.load(bf); } catch (IOException ex) { Logger.getLogger(JavaPostgreSqlRetrieveProperties.class.getName()).log( Level.SEVERE, null, ex); } return props; } public static void main(String[] args) { Properties props = readProperties(); String url = props.getProperty("db.url"); String user = props.getProperty("db.user"); String passwd = props.getProperty("db.passwd"); try (Connection con = DriverManager.getConnection(url, user, passwd); PreparedStatement pst = con.prepareStatement("SELECT * FROM Authors"); ResultSet rs = pst.executeQuery()) { while (rs.next()) { System.out.print(rs.getInt(1)); System.out.print(": "); System.out.println(rs.getString(2)); } } catch (SQLException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlRetrieveProperties.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 我們連接到`testdb`數據庫，并將`authors`表的內容打印到控制臺。這次，我們從文件加載連接屬性。他們沒有在程序中硬編碼。 ```java public static Properties readProperties() { Properties props = new Properties(); Path myPath = Paths.get("src/main/resources/database.properties"); try { BufferedReader bf = Files.newBufferedReader(myPath, StandardCharsets.UTF_8); props.load(bf); } catch (IOException ex) { Logger.getLogger(JavaPostgreSqlRetrieveProperties.class.getName()).log( Level.SEVERE, null, ex); } return props; } ``` 這些屬性通過`load()`加載到`Properties`類中。從位于`src/main/resources`中的`database.properties`文件讀取數據。 ```java String url = props.getProperty("db.url"); String user = props.getProperty("db.user"); String passwd = props.getProperty("db.passwd"); ``` 使用`getProperty()`方法檢索這些值。 ## Java PostgreSQL 多條語句可以在一個查詢中執行多個 SQL 語句。 `JavaPostgreSqlMultipleStatements.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlMultipleStatements { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; String query = "SELECT id, name FROM authors WHERE Id=1;" + "SELECT id, name FROM authors WHERE Id=2;" + "SELECT id, name FROM authors WHERE Id=3"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query)) { boolean isResult = pst.execute(); do { try (ResultSet rs = pst.getResultSet()) { while (rs.next()) { System.out.print(rs.getInt(1)); System.out.print(": "); System.out.println(rs.getString(2)); } isResult = pst.getMoreResults(); } } while (isResult); } catch (SQLException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlMultipleStatements.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 在代碼示例中，我們從`authors`表中檢索三行。我們使用三個`SELECT`語句來獲取三行。 ```java String query = "SELECT id, name FROM authors WHERE Id=1;" + "SELECT id, name FROM authors WHERE Id=2;" + "SELECT id, name FROM authors WHERE Id=3"; ``` 在這里，我們有一個包含多個語句的查詢。語句用分號分隔。 ```java boolean isResult = pst.execute(); ``` 我們調用已預備語句對象的`execute()`方法。該方法返回一個布爾值，該布爾值指示第一個結果是否為`ResultSet`對象。使用`getMoreResults()`方法調用后續結果。 ```java do { try (ResultSet rs = pst.getResultSet()) { while (rs.next()) { System.out.print(rs.getInt(1)); System.out.print(": "); System.out.println(rs.getString(2)); } isResult = pst.getMoreResults(); } } while (isResult); ``` 結果的處理在`do/while`循環內完成。通過`getResultSet()`方法調用檢索`ResultSet`。為了找出是否還有其他結果，我們調用`getMoreResults()`方法。 ```java $ mvn -q exec:java 1: Jack London 2: Honore de Balzac 3: Lion Feuchtwanger ``` 前三行是從`authors`表中檢索的。 ## 元數據元數據是有關數據庫中數據的信息。 PostgreSQL 數據庫中的元數據包含有關表和列的信息，我們在其中存儲數據。受 SQL 語句影響的行數是元數據。結果集中返回的行數和列數也屬于元數據。可以通過調用結果集對象的`getMetaData()`方法或從`information_schema`表獲得 PostgreSQL 中的元數據。在下一個示例中，我們使用數據庫表中的數據打印列標題。 `JavaPostgreSqlColumnHeaders.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.Formatter; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlColumnHeaders { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; String query = "SELECT name, title From authors, " + "books WHERE authors.id=books.author_id"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query); ResultSet rs = pst.executeQuery()) { ResultSetMetaData meta = rs.getMetaData(); String colname1 = meta.getColumnName(1); String colname2 = meta.getColumnName(2); Formatter fmt1 = new Formatter(); fmt1.format("%-21s%s", colname1, colname2); System.out.println(fmt1); while (rs.next()) { Formatter fmt2 = new Formatter(); fmt2.format("%-21s", rs.getString(1)); System.out.print(fmt2); System.out.println(rs.getString(2)); } } catch (SQLException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlColumnHeaders.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 在此程序中，我們從作者表中選擇作者，并從`books`表中選擇他們的書。我們打印結果集中返回的列的名稱。我們格式化輸出。 ```java String query = "SELECT name, title From authors, " + "books WHERE authors.id=books.author_id"; ``` 這是將作者與他們的書聯系在一起的 SQL 語句。 ```java ResultSetMetaData meta = rs.getMetaData(); ``` 要獲取列名，我們需要獲取`ResultSetMetaData`。它是一個對象，可用于獲取有關`ResultSet`對象中列的類型和屬性的信息。 ```java String colname1 = meta.getColumnName(1); String colname2 = meta.getColumnName(2); ``` 從獲得的元數據中，我們獲得列名。 ```java Formatter fmt1 = new Formatter(); fmt1.format("%-21s%s", colname1, colname2); System.out.println(fmt1) ``` 我們將列名稱打印到控制臺。我們使用`Formatter`對象格式化數據。 ```java while (rs.next()) { Formatter fmt2 = new Formatter(); fmt2.format("%-21s", rs.getString(1)); System.out.print(fmt2); System.out.println(rs.getString(2)); } ``` 我們將數據打印到控制臺。我們再次使用`Formatter`對象來格式化數據。第一列為 21 個字符，并在左側對齊。 ```java $ mvn exec:java -q name title Jack London Call of the Wild Jack London Martin Eden Honore de Balzac Old Goriot Honore de Balzac Cousin Bette Lion Feuchtwanger Jew Suess Emile Zola Nana Emile Zola The Belly of Paris Truman Capote In Cold blood Truman Capote Breakfast at Tiffany ``` 這是程序的輸出。在下面的示例中，我們將列出`testdb`數據庫中的所有表。 `JavaPostgreSqlListTables.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlListTables { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; String query = "SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public'"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query); ResultSet rs = pst.executeQuery()) { while (rs.next()) { System.out.println(rs.getString(1)); } } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlListTables.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 該代碼示例將當前數據庫中的所有可用表打印到終端。 ```java String query = "SELECT table_name FROM information_schema.tables " + "WHERE table_schema = 'public'"; ``` 表名存儲在系統`information_schema`表中。 ```java $ mvn -q exec:java authors books images testing cities ``` 列出`testdb`數據庫中的可用表。 ## 寫入圖像有些人喜歡將其圖像放入數據庫中，有些人則希望將其保留在文件系統中以供其應用使用。當我們處理大量圖像時，會出現技術難題。圖像是二進制數據。 PostgreSQL 數據庫具有一種特殊的數據類型來存儲稱為`bytea`的二進制數據。這是非標準數據類型。數據庫中的標準數據類型為`BLOB`。在此示例中，我們使用`images`表。 `JavaPostgreSqlWriteImage.java` ```java package com.zetcode; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlWriteImage { public static void main(String[] args) { String url = "jdbc:postgresql://localhost/testdb"; String user = "user12"; String password = "34klq*"; String query = "INSERT INTO images(data) VALUES(?)"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query)) { File img = new File("src/main/resources/sid.jpg"); try (FileInputStream fin = new FileInputStream(img)) { pst.setBinaryStream(1, fin, (int) img.length()); pst.executeUpdate(); } catch (IOException ex) { Logger.getLogger(JavaPostgreSqlWriteImage.class.getName()).log( Level.SEVERE, ex.getMessage(), ex); } } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlWriteImage.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 在前面的示例中，我們從當前工作目錄中讀取 JPG 圖像，并將其插入到`images`表中。 ```java String query = "INSERT INTO images(data) VALUES(?)"; ``` 這是插入圖像的 SQL。 ```java File img = new File("src/main/resources/sid.jpg"); try (FileInputStream fin = new FileInputStream(img)) { ``` 我們為圖像文件創建一個`File`對象。要從該文件讀取字節，我們創建一個`FileInputStream`對象。 ```java pst.setBinaryStream(1, fin, (int) img.length()); ``` 二進制流設置為預備語句。 `setBinaryStream()`方法的參數是要綁定的參數索引，輸入流和流中的字節數。 ```java pst.executeUpdate(); ``` 我們執行該語句。 ```java testdb=> select count(id) from images; count ------- 1 (1 row) ``` 如果我們已經寫了圖像，我們在表中查找。 ## 讀取圖像在前面的示例中，我們已將圖像插入數據庫表中。現在，我們將從表中讀取圖像。警告：如果我們使用的是 PostgreSQL 9 和更高版本，則還必須使用最新的 JDBC 驅動程序。 PostgreSQL 改變了 bytea 數據的存儲方式。因此，當將舊版驅動程序與 PostgreSQL 9.x 一起使用時，我們會遇到麻煩。請注意，NetBeans 或其他應用可能使用較舊的驅動程序。 `JavaPostgreSqlReadImage.java` ```java package com.zetcode; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlReadImage { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; String query = "SELECT data, LENGTH(data) FROM images WHERE id = 1"; try (Connection con = DriverManager.getConnection(url, user, password); PreparedStatement pst = con.prepareStatement(query); ResultSet rs = pst.executeQuery()) { rs.next(); File myFile = new File("src/main/resources/sid.jpg"); try (FileOutputStream fos = new FileOutputStream(myFile)) { int len = rs.getInt(2); byte[] buf = rs.getBytes("data"); fos.write(buf, 0, len); } } catch (IOException | SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlReadImage.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 我們從`images`表中讀取了一張圖像。 ```java String query = "SELECT data, LENGTH(data) FROM images WHERE id = 1"; ``` 我們從數據庫表中選擇數據和圖像大小。 ```java File myFile = new File("src/main/resources/sid.jpg"); try (FileOutputStream fos = new FileOutputStream(myFile)) { ``` 創建`FileOutputStream`對象以寫入文件。它旨在寫入原始字節流，例如圖像數據。 ```java int len = result.getInt(2); ``` 我們以字節為單位獲取圖像數據的長度。 ```java byte[] buf = result.getBytes("data"); ``` `getBytes()`方法從結果集中以字節數組的形式檢索所有字節。 ```java fos.write(buf, 0, len); ``` 字節被寫入輸出流。該映像是在文件系統上創建的。 ## 事務支持事務是針對一個或多個數據庫中數據的數據庫操作的基本單位。事務中所有 SQL 語句的影響可以全部提交給數據庫，也可以全部回滾。創建連接后，它處于自動提交模式。這意味著每個單獨的 SQL 語句都被視為事務，并在執行后立即自動提交。對于所有 JDBC 驅動程序（包括 PostgreSQL 的驅動程序）都是如此。要開始新的事務，我們關閉自動提交。在直接 SQL 中，事務以`BEGIN TRANSACTION`語句開始，并以`END TRANSACTION`和`COMMIT`語句結束。在 PostgreSQL 中，這些語句是`BEGIN`和`COMMIT`。但是，在使用驅動程序時，將省略這些語句。它們由驅動處理。確切的細節是特定于驅動程序的。例如，`psycopg2` Python 驅動程序在第一個 SQL 語句之后啟動事務。必須通過將`autocommit`屬性設置為`True`來設置自動提交模式。相反，默認情況下，JDBC 驅動程序處于自動提交模式。并且要開始新事務，必須關閉自動提交。 `JavaPostgreSqlTransactionEx.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlTransactionEx { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try (Connection con = DriverManager.getConnection(url, user, password)) { try (Statement st = con.createStatement()) { con.setAutoCommit(false); st.executeUpdate("UPDATE authors SET name = 'Leo Tolstoy' " + "WHERE Id = 1"); st.executeUpdate("UPDATE books SET title = 'War and Peace' " + "WHERE Id = 1"); st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' " + "WHERE Id = 2"); con.commit(); } catch (SQLException ex) { if (con != null) { try { con.rollback(); } catch (SQLException ex1) { Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName()); lgr.log(Level.WARNING, ex1.getMessage(), ex1); } } Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } catch (SQLException ex) { Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 在此程序中，我們要在`authors`表的第一行中更改作者的名稱。我們還必須更改與該作者相關的書籍。一個需要進行事務的很好的例子。如果我們更改作者但不更改作者的書，則數據已損壞。 ```java con.setAutoCommit(false); ``` 要進行事務，我們必須將`autocommit`設置為`false`。默認情況下，數據庫連接處于自動提交模式。在這種模式下，每條語句在執行后都會立即提交給數據庫。聲明無法撤消。當自動提交關閉時，我們通過調用`commit()`提交更改，或通過調用`rollback()`方法將其回滾。 ```java st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' " + "WHERE Id = 2"); ``` 第三個 SQL 語句有一個錯誤。表中沒有`titl`欄。 ```java con.commit(); ``` 如果沒有異常，則提交事務。如果自動提交關閉，則必須顯式調用`commit()`方法。 ```java if (con != null) { try { con.rollback(); } catch (SQLException ex1) { Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName()); lgr.log(Level.WARNING, ex1.getMessage(), ex1); } } ``` 發生異常時，事務將回滾。沒有更改提交到數據庫。 ```java testdb=> SELECT name, title FROM authors, books WHERE authors.id=books.author_id; name | title -------------------+---------------------- Jack London | Call of the Wild Jack London | Martin Eden Honore de Balzac | Old Goriot Honore de Balzac | Cousin Bette Lion Feuchtwanger | Jew Suess Emile Zola | Nana Emile Zola | The Belly of Paris Truman Capote | In Cold blood Truman Capote | Breakfast at Tiffany (9 rows) ``` 運行應用后，我們將驗證數據。事務已回滾，并且未進行任何更改。但是，如果沒有事務，數據是不安全的。 `JavaPostgreSqlNoTransactionEx.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlNoTransactionEx { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try (Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement()) { st.executeUpdate("UPDATE authors SET name = 'Leo Tolstoy' " + "WHERE Id = 1"); st.executeUpdate("UPDATE books SET title = 'War and Peace' " + "WHERE Id = 1"); st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' " + "WHERE Id = 2"); } catch (SQLException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlNoTransactionEx.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 我們有同樣的例子。這次沒有事務支持。 ```java testdb=> SELECT name, title FROM authors, books WHERE authors.id=books.author_id; name | title -------------------+---------------------- Leo Tolstoy | Martin Eden Honore de Balzac | Old Goriot Honore de Balzac | Cousin Bette Lion Feuchtwanger | Jew Suess Emile Zola | Nana Emile Zola | The Belly of Paris Truman Capote | In Cold blood Truman Capote | Breakfast at Tiffany Leo Tolstoy | War and Peace (9 rows) ``` 列夫·托爾斯泰沒有寫馬丁·伊甸園。數據已損壞。 ## 批量更新當我們需要使用多個語句更新數據時，可以使用批處理更新。批量更新可用于`INSERT`，`UPDATE`和`DELETE`語句以及`CREATE TABLE`和`DROP TABLE`語句。 `JavaPostgreSqlBatchUpdates.java` ```java package com.zetcode; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.logging.Level; import java.util.logging.Logger; public class JavaPostgreSqlBatchUpdates { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try (Connection con = DriverManager.getConnection(url, user, password)) { try (Statement st = con.createStatement()) { con.setAutoCommit(false); st.addBatch("DROP TABLE IF EXISTS friends"); st.addBatch("CREATE TABLE friends(id serial, name VARCHAR(10))"); st.addBatch("INSERT INTO friends(name) VALUES ('Jane')"); st.addBatch("INSERT INTO friends(name) VALUES ('Tom')"); st.addBatch("INSERT INTO friends(name) VALUES ('Rebecca')"); st.addBatch("INSERT INTO friends(name) VALUES ('Jim')"); st.addBatch("INSERT INTO friends(name) VALUES ('Robert')"); int counts[] = st.executeBatch(); con.commit(); System.out.println("Committed " + counts.length + " updates"); } catch (SQLException ex) { if (con != null) { try { con.rollback(); } catch (SQLException ex1) { Logger lgr = Logger.getLogger( JavaPostgreSqlBatchUpdates.class.getName()); lgr.log(Level.WARNING, ex1.getMessage(), ex1); } } Logger lgr = Logger.getLogger( JavaPostgreSqlBatchUpdates.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } catch (SQLException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlBatchUpdates.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 這是用于批處理更新的示例程序。我們創建一個名為`friends`的新表，并在其中插入五行。 ```java con.setAutoCommit(false); ``` 進行批處理更新時，應始終關閉自動提交。 ```java st.addBatch("DROP TABLE IF EXISTS friends"); st.addBatch("CREATE TABLE friends(id serial, name VARCHAR(10))"); st.addBatch("INSERT INTO friends(name) VALUES ('Jane')"); st.addBatch("INSERT INTO friends(name) VALUES ('Tom')"); ... ``` 我們使用`addBatch()`方法向該語句添加新命令。 ```java int counts[] = st.executeBatch(); ``` 添加所有命令后，我們調用`executeBatch()`進行批量更新。該方法返回已提交更改的數組。 ```java con.commit(); ``` 批處理更新在事務中提交。 ```java $ mvn -q exec:java Committed 7 updates ``` 我們執行`BatchUpdate`程序。我們創建了一個新的`friends`表，并成功插入了 5 行。 `DROP TABLE`和`CREATE TABLE`語句也包含在更新計數中。 ## 數據導入導出 PostgreSQL 有一個`COPY`語句，可用于在表和文件之間復制數據。從 JDBC 的角度來看，它是對標準的擴展。 `JavaPostgreSqlCopyToTextFile.java` ```java package com.zetcode; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStreamWriter; import java.nio.charset.StandardCharsets; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; import org.postgresql.copy.CopyManager; import org.postgresql.core.BaseConnection; public class JavaPostgreSqlCopyToTextFile { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try { Connection con = DriverManager.getConnection(url, user, password); CopyManager cm = new CopyManager((BaseConnection) con); String fileName = "src/main/resources/friends.txt"; try (FileOutputStream fos = new FileOutputStream(fileName); OutputStreamWriter osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8)) { cm.copyOut("COPY friends TO STDOUT WITH DELIMITER AS '|'", osw); } } catch (SQLException | IOException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlCopyToTextFile.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 在前面的示例中，創建了一個簡單的`friends`表。在上面的代碼中，我們將把`friends`表復制到一個文件中。 ```java CopyManager cm = new CopyManager((BaseConnection) con); ``` 在這里，我們創建`CopyManager`的實例。 `CopyManager`是用于 PostgreSQL 復制的批量數據傳輸的 API。 ```java String fileName = "src/main/resources/friends.txt"; try (FileOutputStream fos = new FileOutputStream(fileName); OutputStreamWriter osw = new OutputStreamWriter(fos, StandardCharsets.UTF_8)) { ``` 我們為`friends.txt`文件創建一個`OutputStreamWriter`。 ```java cm.copyOut("COPY friends TO STDOUT WITH DELIMITER AS '|'", fw); ``` 我們使用`copyOut()`方法將`COPY TO STDOUT`查詢的結果從數據庫傳遞給編寫器。列將以`|`字符分隔。 ```java $ cat src/main/resources/friends.txt 1|Jane 2|Tom 3|Rebecca 4|Jim 5|Robert ``` 這是創建的文件。在第二個示例中，我們執行相反的操作。我們將數據從文件復制到數據庫表中。 `JavaPostgreSqlCopyFromTextFile.java` ```java package com.zetcode; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStreamReader; import java.nio.charset.StandardCharsets; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.util.logging.Level; import java.util.logging.Logger; import org.postgresql.copy.CopyManager; import org.postgresql.core.BaseConnection; public class JavaPostgreSqlCopyFromTextFile { public static void main(String[] args) { String url = "jdbc:postgresql://localhost:5432/testdb"; String user = "user12"; String password = "34klq*"; try (Connection con = DriverManager.getConnection(url, user, password)) { CopyManager cm = new CopyManager((BaseConnection) con); String fileName = "src/main/resources/friends.txt"; try (FileInputStream fis = new FileInputStream(fileName); InputStreamReader isr = new InputStreamReader(fis, StandardCharsets.UTF_8)) { cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'", isr); } } catch (SQLException | IOException ex) { Logger lgr = Logger.getLogger( JavaPostgreSqlCopyFromTextFile.class.getName()); lgr.log(Level.SEVERE, ex.getMessage(), ex); } } } ``` 該示例使用`FileReader`類讀取`friends.txt`表的內容，并使用`COPY`語句將數據傳輸到`friends`類。 ```java String fileName = "src/main/resources/friends.txt"; try (FileInputStream fis = new FileInputStream(fileName); InputStreamReader isr = new InputStreamReader(fis, StandardCharsets.UTF_8)) { ``` 我們將從`friends.txt`文件中讀取。 ```java cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'", fr); ``` 我們使用`COPY`語句從文件中復制數據。 ```java testdb=> delete from friends; DELETE 5 ``` 我們從`friends`表中刪除數據。 ```java $ mvn -q exec:java ``` 我們運行程序。 ```java testdb=> select * from friends; id | name ----+--------- 1 | Jane 2 | Tom 3 | Rebecca 4 | Jim 5 | Robert (5 rows) ``` 我們檢查`friends`表的內容。這是 PostgreSQL Java 教程。您可能也對 [MySQL Java 教程](/db/mysqljava/)， [Java H2 教程](/java/h2database/)， [MongoDB Java 教程](/db/mongodbjava/)或 [PostgreSQL Python 教程](/db/postgresqlpythontutorial/)感興趣。