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/)感兴趣。