PostgreSQL Java
最后修改于 2024 年 1 月 27 日
这是一个针对 PostgreSQL 数据库的 Java 教程。它涵盖了使用 Java 进行 PostgreSQL 编程的基础知识。
ZetCode 上有类似的相关教程，如 MySQL Java 教程、Spring JdbcTemplate 教程 和 Apache Derby 教程。
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 依赖项
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.0</version>
</dependency>
Maven Exec 插件
为了从 Maven 运行命令行应用程序，我们可以使用以下 Maven 插件。
<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 数据库。
$ sudo apt-get install postgresql
此命令安装 PostgreSQL 服务器及相关软件包。
$ /etc/init.d/postgresql status
我们使用 postgresql status 命令检查数据库的状态。
$ sudo -u postgres psql postgres psql (9.5.10) Type "help" for help. postgres=# \password postgres Enter new password: Enter it again:
安装后，系统会创建一个具有管理权限的 postgres 用户，其默认密码为空。第一步，我们需要为 postgres 设置一个密码。
$ 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:
我们创建一个新的数据库用户。
$ sudo -u postgres createdb testdb -O user12
我们使用 createdb 命令创建一个新的 testdb 数据库，该数据库将由 user12 拥有。
$ sudo vi /etc/postgresql/9.5/main/pg_hba.conf
我们编辑 pg_hba.conf 文件。
# "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。
$ sudo service postgresql restart
我们重启 PostgreSQL 以使更改生效。
$ psql -U user12 -d testdb -W Password for user user12: psql (9.5.10) Type "help" for help. testdb=>
现在我们可以使用 psql 工具连接到数据库。
从启动脚本中移除 PostgreSQL
如果我们从软件包安装 PostgreSQL 数据库，它会自动添加到操作系统的启动脚本中。如果我们只是在学习使用数据库，那么每次启动系统时启动数据库是不必要的。
$ 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
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://: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 服务器的一些信息。
String url = "jdbc:postgresql://:5432/testdb";
这是 PostgreSQL 数据库的连接 URL。每个驱动的 URL 语法都不同。在我们的例子中，我们提供了主机（localhost）、端口（5432）和数据库名称（testdb）。
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 语句确保每个资源在语句结束时都会被关闭。
if (rs.next()) {
System.out.println(rs.getString(1));
}
一个 ResultSet 对象维护一个指向其当前数据行的光标。最初，光标位于第一行之前。next 方法将光标移动到下一行。如果没有更多行，该方法返回 false。getString 方法检索指定列的值。第一列的索引为 1。
} catch (SQLException ex) {
Logger lgr = Logger.getLogger(JavaPostgreSqlVersion.class.getName());
lgr.log(Level.SEVERE, ex.getMessage(), ex);
}
如果发生异常，我们记录错误消息。
$ 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
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 文件。它创建了四个数据库表：authors、books、testing 和 images。我们在 books 表的 author_id 列上设置了一个外键约束。我们用初始数据填充 authors 和 books 表。
$ 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 文件。
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
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://: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 表添加一个新作者。
String query = "INSERT INTO authors(id, name) VALUES(?, ?)";
try (Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement pst = con.prepareStatement(query)) {
这里我们创建一个预处理语句。当我们编写预处理语句时，我们使用占位符而不是直接将值写入语句中。预处理语句速度更快，并且可以防止 SQL 注入攻击。? 是一个占位符，稍后将被填充。在我们的例子中，我们将填充两个值：一个整数 id 和一个字符串 name。
pst.setInt(1, id);
一个整数值被绑定到占位符上。
pst.setString(2, author);
一个字符串值被绑定到占位符上。
pst.executeUpdate();
预处理语句被执行。当我们不期望返回任何数据时，我们使用语句对象的 executeUpdate 方法。这适用于创建数据库或执行 INSERT、UPDATE 和 DELETE 语句时。
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
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://: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 表中获取所有作者，并将其打印到控制台。
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 查询返回的数据表。
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 对象当前行中指定列的值。
$ mvn -q exec:java 1: Jack London 2: Honore de Balzac 3: Lion Feuchtwanger 4: Emile Zola 5: Truman Capote 6: Trygve Gulbranssen
作者的 ID 和姓名被打印到控制台。
属性（Properties）
将配置数据放在程序外部的一个单独文件中是一种常见的做法。这样程序员可以更加灵活。我们可以更改用户、密码或连接 URL，而无需重新编译程序。
在 Java 中，Properties 是经常用于此目的的类。该类用于轻松读取和保存键/值属性。
database.properties
db.url=jdbc:postgresql://:5432/testdb db.user=user12 db.passwd=34klq*
我们有一个 database.properties 文件，其中有三个键/值对。它们在程序执行期间被加载。
JavaPostgreSqlRetrieveProperties.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 表的内容打印到控制台。这一次，我们从文件中加载连接属性。它们没有硬编码在程序中。
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 文件中读取。
String url = props.getProperty("db.url");
String user = props.getProperty("db.user");
String passwd = props.getProperty("db.passwd");
值通过 getProperty 方法检索。
Java PostgreSQL 多条语句
可以在一个查询中执行多条 SQL 语句。
JavaPostgreSqlMultipleStatements.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://: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 语句来获取三行。
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";
这里我们有一个包含多条语句的查询。这些语句由分号分隔。
boolean isResult = pst.execute();
我们调用预处理语句对象的 execute 方法。该方法返回一个布尔值，指示第一个结果是否为 ResultSet 对象。后续结果通过调用 getMoreResults 方法来获取。
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 循环内完成。ResultSet 通过调用 getResultSet 方法检索。要查明是否还有其他结果，我们调用 getMoreResults 方法。
$ mvn -q exec:java 1: Jack London 2: Honore de Balzac 3: Lion Feuchtwanger
前三行已从 authors 表中检索。
元数据
元数据是关于数据库中数据的信息。PostgreSQL 数据库中的元数据包含有关我们存储数据的表和列的信息。受 SQL 语句影响的行数是元数据。结果集中返回的行数和列数也属于元数据。
在 PostgreSQL 中，可以通过调用结果集对象的 getMetaData 方法或从 information_schema 表中获取元数据。
在下一个示例中，我们打印列标题以及来自数据库表的数据。
JavaPostgreSqlColumnHeaders.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://: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);
}
}
}
在这个程序中，我们从 authors 表中选择作者，并从 books 表中选择他们的书籍。我们打印结果集中返回的列名。我们格式化输出。
String query = "SELECT name, title From authors, " +
"books WHERE authors.id=books.author_id";
这是将作者与其书籍连接起来的 SQL 语句。
ResultSetMetaData meta = rs.getMetaData();
为了获取列名，我们需要得到 ResultSetMetaData。它是一个可用于获取有关 ResultSet 对象中列的类型和属性信息的对象。
String colname1 = meta.getColumnName(1); String colname2 = meta.getColumnName(2);
从获取的元数据中，我们得到列名。
Formatter fmt1 = new Formatter();
fmt1.format("%-21s%s", colname1, colname2);
System.out.println(fmt1)
我们将列名打印到控制台。我们使用 Formatter 对象来格式化数据。
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 个字符，并向左对齐。
$ 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
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://: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);
}
}
}
该代码示例将当前数据库中所有可用的表打印到终端。
String query = "SELECT table_name FROM information_schema.tables "
+ "WHERE table_schema = 'public'";
表名存储在系统表 information_schema 中。
$ mvn -q exec:java authors books images testing cities
列出 testdb 数据库中可用的表。
写入图像
有些人喜欢将他们的图片放入数据库，有些人则喜欢为他们的应用程序将图片保存在文件系统上。当我们处理大量图片时，会出现技术难题。图片是二进制数据。PostgreSQL 数据库有一个特殊的数据类型来存储二进制数据，称为 bytea。这是一种非标准数据类型。数据库中的标准数据类型是 BLOB。
对于这个例子，我们使用 images 表。
JavaPostgreSqlWriteImage.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:///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 表中。
String query = "INSERT INTO images(data) VALUES(?)";
这是插入图片的 SQL。
File img = new File("src/main/resources/sid.jpg");
try (FileInputStream fin = new FileInputStream(img)) {
我们为图片文件创建一个 File 对象。为了从这个文件中读取字节，我们创建一个 FileInputStream 对象。
pst.setBinaryStream(1, fin, (int) img.length());
二进制流被设置到预处理语句中。setBinaryStream 方法的参数是绑定的参数索引、输入流和流中的字节数。
pst.executeUpdate();
我们执行该语句。
testdb=> select count(id) from images;
count
-------
1
(1 row)
我们查看表中是否已写入图片。
读取图像
在前面的例子中，我们已经将一张图片插入到数据库表中。现在我们将从表中把图片读回来。
警告：如果我们使用的是 PostgreSQL 9 及更高版本，我们也必须使用最新的 JDBC 驱动程序。PostgreSQL 改变了 bytea 数据的存储方式。因此，当使用旧驱动程序与 PostgreSQL 9.x 一起工作时，我们会遇到麻烦。请注意，NetBeans 或其他应用程序可能使用旧的驱动程序。
JavaPostgreSqlReadImage.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://: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 表中读取一张图片。
String query = "SELECT data, LENGTH(data) FROM images WHERE id = 1";
我们从数据库表中选择图片的数据和大小。
File myFile = new File("src/main/resources/sid.jpg");
try (FileOutputStream fos = new FileOutputStream(myFile)) {
创建 FileOutputStream 对象以写入文件。它用于写入原始字节流，例如图像数据。
int len = result.getInt(2);
我们获取图像数据的长度（以字节为单位）。
byte[] buf = result.getBytes("data");
getBytes 方法以字节数组的形式从结果集中检索所有字节。
fos.write(buf, 0, len);
字节被写入输出流。图片在文件系统上被创建。
事务支持
事务是针对一个或多个数据库中的数据执行的数据库操作的原子单元。事务中所有 SQL 语句的效果可以要么全部提交到数据库，要么全部回滚。
当创建连接时，它处于自动提交模式。这意味着每个单独的 SQL 语句都被视为一个事务，并在执行后立即自动提交。这适用于所有 JDBC 驱动程序，包括 PostgreSQL 的驱动程序。要开始一个新事务，我们需要关闭自动提交。
在直接 SQL 中，事务以 BEGIN TRANSACTION 语句开始，并以 END TRANSACTION 或 COMMIT 语句结束。在 PostgreSQL 中，这些语句是 BEGIN 和 COMMIT。然而，在使用驱动程序时，这些语句被省略了。它们由驱动程序处理。具体细节因驱动程序而异。例如，Python 的 psycopg2 驱动程序在第一个 SQL 语句后开始一个事务。自动提交模式必须通过将 autocommit 属性设置为 True 来设置。相比之下，JDBC 驱动程序默认处于自动提交模式。要开始一个新事务，必须关闭自动提交。
JavaPostgreSqlTransactionEx.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://: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 表第一行中作者的姓名。我们还必须更改与该作者相关的书籍。这是一个需要事务的好例子。如果我们更改了作者而没有更改作者的书籍，数据就会被损坏。
con.setAutoCommit(false);
要使用事务，我们必须将 autocommit 设置为 false。默认情况下，数据库连接处于自动提交模式。在这种模式下，每个语句在执行后都会立即提交到数据库。语句无法撤销。当自动提交关闭时，我们通过调用 commit 方法提交更改，或通过调用 rollback 方法回滚它。
st.executeUpdate("UPDATE books SET titl = 'Anna Karenina' "
+ "WHERE Id = 2");
第三条 SQL 语句有错误。表中没有 titl 列。
con.commit();
如果没有异常，事务就被提交。如果自动提交已关闭，我们必须显式调用 commit 方法。
if (con != null) {
try {
con.rollback();
} catch (SQLException ex1) {
Logger lgr = Logger.getLogger(JavaPostgreSqlTransactionEx.class.getName());
lgr.log(Level.WARNING, ex1.getMessage(), ex1);
}
}
如果发生异常，事务将被回滚。没有更改被提交到数据库。
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
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://: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);
}
}
}
我们有相同的例子。这次没有事务支持。
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
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://: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 的新表，并向其中插入五行。
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')");
...
我们使用 addBatch 方法向语句中添加一个新命令。
int counts[] = st.executeBatch();
添加所有命令后，我们调用 executeBatch 来执行批量更新。该方法返回一个已提交更改的数组。
con.commit();
批量更新在事务中提交。
$ mvn -q exec:java Committed 7 updates
我们执行 BatchUpdate 程序。我们创建了一个新的 friends 表并成功插入了 5 行。DROP TABLE 和 CREATE TABLE 语句也包含在更新计数中。
数据的导出和导入
PostgreSQL 有一个 COPY 语句，可用于在表和文件之间复制数据。从 JDBC 的角度来看，这是对标准的扩展。
JavaPostgreSqlCopyToTextFile.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://: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 表复制到一个文件。
CopyManager cm = new CopyManager((BaseConnection) con);
这里我们创建了一个 CopyManager 的实例。CopyManager 是用于 PostgreSQL COPY 批量数据传输的 API。
String fileName = "src/main/resources/friends.txt";
try (FileOutputStream fos = new FileOutputStream(fileName);
OutputStreamWriter osw = new OutputStreamWriter(fos,
StandardCharsets.UTF_8)) {
我们为 friends.txt 文件创建一个 OutputStreamWriter。
cm.copyOut("COPY friends TO STDOUT WITH DELIMITER AS '|'", fw);
我们使用 copyOut 方法将来自数据库的 COPY TO STDOUT 查询结果传递给一个写入器。列将用 | 字符分隔。
$ cat src/main/resources/friends.txt 1|Jane 2|Tom 3|Rebecca 4|Jim 5|Robert
这是创建的文件。
在第二个例子中，我们执行相反的操作。我们将数据从文件复制到数据库表中。
JavaPostgreSqlCopyFromTextFile.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://: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 类。
String fileName = "src/main/resources/friends.txt";
try (FileInputStream fis = new FileInputStream(fileName);
InputStreamReader isr = new InputStreamReader(fis,
StandardCharsets.UTF_8)) {
我们将从 friends.txt 文件中读取。
cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'", fr);
我们使用 COPY 语句从文件中复制数据。
testdb=> delete from friends; DELETE 5
我们从 friends 表中删除数据。
$ mvn -q exec:java
我们运行该程序。
testdb=> select * from friends; id | name ----+--------- 1 | Jane 2 | Tom 3 | Rebecca 4 | Jim 5 | Robert (5 rows)
我们检查 friends 表的内容。
来源
这是 PostgreSQL Java 教程。
作者
列出所有Java教程。