PostgreSQL Java 教程|极客教程

这是 PostgreSQL 数据库的 Java 教程。它涵盖了使用 Java 进行 PostgreSQL 编程的基础。

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://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 服务器的一些信息。

String url = "jdbc:postgresql://localhost: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文件。它创建四个数据库表，作者，书籍，测试和图像。我们在 books 表的author_id列上放置一个外键约束。我们用初始数据填充作者和书籍表。

$ 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://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 表中。

String query = "INSERT INTO authors(id, name) VALUES(?, ?)";

try (Connection con = DriverManager.getConnection(url, user, password);
        PreparedStatement pst = con.prepareStatement(query)) {

在这里，我们创建一个预备语句。在编写预备语句时，我们使用占位符，而不是直接将值写入语句中。预备语句更快，并且可以防止 SQL 注入攻击。？是一个占位符，稍后将填充。在我们的例子中，我们将填充两个值：一个整数 id 和一个字符串名。

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://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 表中获取所有作者，并将它们打印到控制台。

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 和名称将打印到控制台。

属性

通常的做法是将配置数据放在程序外部的单独文件中。这样程序员可以更加灵活。我们可以更改用户，密码或连接 URL，而无需重新编译程序。

在 Java 中，Properties是为此经常使用的类。该类用于轻松读取和保存键/值属性。

database.properties

db.url=jdbc:postgresql://localhost: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://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语句来获取三行。

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 循环内完成。通过getResultSet()方法调用检索ResultSet。为了找出是否还有其他结果，我们调用getMoreResults()方法。

$ mvn -q exec:java
1: Jack London
2: Honore de Balzac
3: Lion Feuchtwanger

前三行是从 authors 表中检索的。

元数据

元数据是有关数据库中数据的信息。 PostgreSQL 数据库中的元数据包含有关表和列的信息，我们在其中存储数据。受 SQL 语句影响的行数是元数据。结果集中返回的行数和列数也属于元数据。

可以通过调用结果集对象的getMetaData()方法或从 information_schema 表获得 PostgreSQL 中的元数据。

在下一个示例中，我们使用数据库表中的数据打印列标题。

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://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 表中选择他们的书。我们打印结果集中返回的列的名称。我们格式化输出。

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://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);
        }
    }
}

该代码示例将当前数据库中的所有可用表打印到终端。

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://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 表中。

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://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表中读取了一张图像。

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。但是，在使用驱动程序时，将省略这些语句。它们由驾驶员处理。确切的细节是特定于驱动程序的。例如，psycopg2 Python 驱动程序在第一个 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://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 表的第一行中更改作者的名称。我们还必须更改与该作者相关的书籍。一个需要进行事务的很好的例子。如果我们更改作者但不更改作者的书，则数据已损坏。

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://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);
        }
    }
}

我们有同样的例子。这次没有事务支持。

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://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的新表，并在其中插入五行。

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://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 表复制到一个文件中。

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://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 类。

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 教程

JDBC

关于 PostgreSQL 数据库

PostgreSQL 驱动

Maven Exec 插件

PostgreSQL 设置

从启动脚本中删除 PostgreSQL

Java PostgreSQL 版本

创建和填充表

Java PostgreSQL 预备语句

Java PostgreSQL 检索数据

属性

Java PostgreSQL 多条语句

元数据

写入图像

读取图像

事务支持

批量更新

数据导入导出

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

PostgreSQL 教程

回顶部