将 Java和 JDBC 与 Azure Database for PostgreSQL 配合使用Use Java and JDBC with Azure Database for PostgreSQL

本主题演示如何创建示例应用程序,使其使用 Java 和 JDBCAzure Database for PostgreSQL 中存储和检索信息。This topic demonstrates creating a sample application that uses Java and JDBC to store and retrieve information in Azure Database for PostgreSQL.

JDBC 是标准的 Java API,用于连接到传统的关系数据库。JDBC is the standard Java API to connect to traditional relational databases.

先决条件Prerequisites

准备工作环境Prepare the working environment

我们将使用环境变量来减少键入错误,并使你能够更轻松地根据特定需求自定义以下配置。We are going to use environment variables to limit typing mistakes, and to make it easier for you to customize the following configuration for your specific needs.

使用以下命令设置这些环境变量:Set up those environment variables by using the following commands:

AZ_RESOURCE_GROUP=database-workshop
AZ_DATABASE_NAME=<YOUR_DATABASE_NAME>
AZ_LOCATION=<YOUR_AZURE_REGION>
AZ_POSTGRESQL_USERNAME=demo
AZ_POSTGRESQL_PASSWORD=<YOUR_POSTGRESQL_PASSWORD>
AZ_LOCAL_IP_ADDRESS=<YOUR_LOCAL_IP_ADDRESS>

使用以下值替换占位符,在本文中将使用这些值:Replace the placeholders with the following values, which are used throughout this article:

  • <YOUR_DATABASE_NAME>:PostgreSQL 服务器的名称。<YOUR_DATABASE_NAME>: The name of your PostgreSQL server. 它在 Azure 中应是唯一的。It should be unique across Azure.
  • <YOUR_AZURE_REGION>:将使用的 Azure 区域。<YOUR_AZURE_REGION>: The Azure region you'll use. 建议配置一个离居住位置更近的区域。We recommend that you configure a region closer to where you live. 可输入 az account list-locations,获取可用区域的完整列表。You can have the full list of available regions by entering az account list-locations.
  • <YOUR_POSTGRESQL_PASSWORD>:PostgreSQL 数据库服务器的密码。<YOUR_POSTGRESQL_PASSWORD>: The password of your PostgreSQL database server. 该密码应该至少有八个字符。That password should have a minimum of eight characters. 这些字符应该属于以下类别中的三个类别:英文大写字母、英文小写字母、数字 (0-9)和非字母数字字符(!, $, #, % 等)。The characters should be from three of the following categories: English uppercase letters, English lowercase letters, numbers (0-9), and non-alphanumeric characters (!, $, #, %, and so on).
  • <YOUR_LOCAL_IP_ADDRESS>:你将在其中运行 Java 应用程序的本地计算机的 IP 地址。<YOUR_LOCAL_IP_ADDRESS>: The IP address of your local computer, from which you'll run your Java application. 若要找到该地址,一种简便方法是使浏览器指向 whatismyip.akamai.comOne convenient way to find it is to point your browser to whatismyip.akamai.com.

接下来,请使用以下命令创建资源组:Next, create a resource group by using the following command:

az group create \
    --name $AZ_RESOURCE_GROUP \
    --location $AZ_LOCATION \

创建 Azure Database for PostgreSQL 实例Create an Azure Database for PostgreSQL instance

首先,我们将创建一个托管 PostgreSQL 服务器。The first thing we'll create is a managed PostgreSQL server.

备注

可以在使用 Azure 门户创建 Azure Database for PostgreSQL 服务器中阅读有关创建 PostgreSQL 服务器的更多详细信息。You can read more detailed information about creating PostgreSQL servers in Create an Azure Database for PostgreSQL server by using the Azure portal.

运行以下命令:Run the following command:

az postgres server create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME \
    --location $AZ_LOCATION \
    --sku-name B_Gen5_1 \
    --storage-size 5120 \
    --admin-user $AZ_POSTGRESQL_USERNAME \
    --admin-password $AZ_POSTGRESQL_PASSWORD \

此命令创建小型 PostgreSQL 服务器。This command creates a small PostgreSQL server.

为 PostgreSQL 服务器配置防火墙规则Configure a firewall rule for your PostgreSQL server

Azure Database for PostgreSQL 实例在默认情况下受保护。Azure Database for PostgreSQL instances are secured by default. 它们有不允许任何传入连接的防火墙。They have a firewall that doesn't allow any incoming connection. 为了能够使用数据库,需要添加一项防火墙规则,允许本地 IP 地址访问数据库服务器。To be able to use your database, you need to add a firewall rule that will allow the local IP address to access the database server.

由于我们已在本文开头配置了本地 IP 地址,因此你可通过运行以下命令来打开服务器的防火墙:Because you configured your local IP address at the beginning of this article, you can open the server's firewall by running the following command:

az postgres server firewall-rule create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME-database-allow-local-ip \
    --server $AZ_DATABASE_NAME \
    --start-ip-address $AZ_LOCAL_IP_ADDRESS \
    --end-ip-address $AZ_LOCAL_IP_ADDRESS \

配置 PostgreSQL 数据库Configure a PostgreSQL database

你在前面创建的 PostgreSQL 服务器为空。The PostgreSQL server that you created earlier is empty. 它没有任何可以与 Java 应用程序配合使用的数据库。It doesn't have any database that you can use with the Java application. 使用以下命令创建名为 demo 的新数据库:Create a new database called demo by using the following command:

az postgres db create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name demo \
    --server-name $AZ_DATABASE_NAME \

新建一个 Java 项目Create a new Java project

使用最喜欢的 IDE 创建一个新的 Java 项目,并在其根目录中添加一个 pom.xml 文件:Using your favorite IDE, create a new Java project, and add a pom.xml file in its root directory:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>

    <properties>
        <java.version>1.8</java.version>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.12</version>
        </dependency>
    </dependencies>
</project>

此文件是一个 Apache Maven,用于配置要使用的项目:This file is an Apache Maven that configures our project to use:

  • Java 8Java 8
  • 最新的用于 Java 的 PostgreSQL 驱动程序A recent PostgreSQL driver for Java

准备用于连接到 Azure Database for PostgreSQL 的配置文件Prepare a configuration file to connect to Azure Database for PostgreSQL

创建 src/main/resources/application.properties 文件,添加以下内容:Create a src/main/resources/application.properties file, and add:

url=jdbc:postgresql://$AZ_DATABASE_NAME.postgres.database.chinacloudapi.cn:5432/demo?ssl=true&sslmode=require
user=demo@$AZ_DATABASE_NAME
password=$AZ_POSTGRESQL_PASSWORD
  • 将两个 $AZ_DATABASE_NAME 变量替换为在本文开头部分配置的值。Replace the two $AZ_DATABASE_NAME variables with the value that you configured at the beginning of this article.
  • $AZ_POSTGRESQL_PASSWORD 变量替换为在本文开头部分配置的值。Replace the $AZ_POSTGRESQL_PASSWORD variable with the value that you configured at the beginning of this article.

备注

我们将 ?ssl=true&sslmode=require 追加到配置属性 url,以命令 JDBC 驱动程序在连接到数据库时使用 TLS(传输层安全性)。We append ?ssl=true&sslmode=require to the configuration property url, to tell the JDBC driver to use TLS (Transport Layer Security) when connecting to the database. 必须结合使用 TLS 与 Azure Database for PostgreSQL,这是一个很好的安全做法。It is mandatory to use TLS with Azure Database for PostgreSQL, and it is a good security practice.

创建 SQL 文件以生成数据库架构Create an SQL file to generate the database schema

我们将使用 src/main/resources/schema.sql 文件来创建数据库架构。We will use a src/main/resources/schema.sql file in order to create a database schema. 创建包含以下内容的文件:Create that file, with the following content:

DROP TABLE IF EXISTS todo;
CREATE TABLE todo (id SERIAL PRIMARY KEY, description VARCHAR(255), details VARCHAR(4096), done BOOLEAN);

编写应用程序代码Code the application

连接到数据库Connect to the database

接下来,添加将使用 JDBC 在 PostgreSQL 服务器中存储和检索数据的 Java 代码。Next, add the Java code that will use JDBC to store and retrieve data from your PostgreSQL server.

创建 src/main/java/DemoApplication.java 文件,其中包含:Create a src/main/java/DemoApplication.java file, that contains:

package com.example.demo;

import java.sql.*;
import java.util.*;
import java.util.logging.Logger;

public class DemoApplication {

    private static final Logger log;

    static {
        System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n");
        log =Logger.getLogger(DemoApplication.class.getName());
    }

    public static void main(String[] args) throws Exception {
        log.info("Loading application properties");
        Properties properties = new Properties();
        properties.load(DemoApplication.class.getClassLoader().getResourceAsStream("application.properties"));

        log.info("Connecting to the database");
        Connection connection = DriverManager.getConnection(properties.getProperty("url"), properties);
        log.info("Database connection test: " + connection.getCatalog());

        log.info("Create database schema");
        Scanner scanner = new Scanner(DemoApplication.class.getClassLoader().getResourceAsStream("schema.sql"));
        Statement statement = connection.createStatement();
        while (scanner.hasNextLine()) {
            statement.execute(scanner.nextLine());
        }

        /*
        Todo todo = new Todo(1L, "configuration", "congratulations, you have set up JDBC correctly!", true);
        insertData(todo, connection);
        todo = readData(connection);
        todo.setDetails("congratulations, you have updated data!");
        updateData(todo, connection);
        deleteData(todo, connection);
        */

        log.info("Closing database connection");
        connection.close();
    }
}

此 Java 代码将使用之前创建的 application.properties 和 schema.sql 文件 ,来连接到 PostgreSQL 服务器并创建用于存储数据的架构。This Java code will use the application.properties and the schema.sql files that we created earlier, in order to connect to the PostgreSQL server and create a schema that will store our data.

在此文件中,你可以看到,我们注释了用于插入、读取、更新和删除数据的方法:我们将在本文的其余部分对这些方法进行编码,并且你将能够逐个取消注释。In this file, you can see that we commented methods to insert, read, update and delete data: we will code those methods in the rest of this article, and you will be able to uncomment them one after each other.

备注

数据库凭据存储在“application.properties”文件的“user”和“password”属性中 。The database credentials are stored in the user and password properties of the application.properties file. 执行 DriverManager.getConnection(properties.getProperty("url"), properties); 时使用这些凭据,因为属性文件是作为参数传递的。Those credentials are used when executing DriverManager.getConnection(properties.getProperty("url"), properties);, as the properties file is passed as an argument.

现在可以通过喜欢的工具执行此主类:You can now execute this main class with your favorite tool:

  • 使用你的 IDE,你应该能够右键单击 DemoApplication 类并执行它。Using your IDE, you should be able to right-click on the DemoApplication class and execute it.
  • 使用 Maven,可以通过执行以下操作来运行应用程序:mvn exec:java -Dexec.mainClass="com.example.demo.DemoApplication"Using Maven, you can run the application by executing: mvn exec:java -Dexec.mainClass="com.example.demo.DemoApplication".

应用程序应连接到 Azure Database for PostgreSQL,创建数据库架构,然后关闭连接,如控制台日志中所示:The application should connect to the Azure Database for PostgreSQL, create a database schema, and then close the connection, as you should see in the console logs:

[INFO   ] Loading application properties 
[INFO   ] Connecting to the database 
[INFO   ] Database connection test: demo 
[INFO   ] Create database schema 
[INFO   ] Closing database connection 

创建域类Create a domain class

DemoApplication 类旁创建新的 Todo Java 类并添加以下代码:Create a new Todo Java class, next to the DemoApplication class, and add the following code:

package com.example.demo;

public class Todo {

    private Long id;
    private String description;
    private String details;
    private boolean done;

    public Todo() {
    }

    public Todo(Long id, String description, String details, boolean done) {
        this.id = id;
        this.description = description;
        this.details = details;
        this.done = done;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public String getDetails() {
        return details;
    }

    public void setDetails(String details) {
        this.details = details;
    }

    public boolean isDone() {
        return done;
    }

    public void setDone(boolean done) {
        this.done = done;
    }

    @Override
    public String toString() {
        return "Todo{" +
                "id=" + id +
                ", description='" + description + '\'' +
                ", details='" + details + '\'' +
                ", done=" + done +
                '}';
    }
}

此类是在执行 schema .sql 脚本时创建的 todo 表上映射的域模型。This class is a domain model mapped on the todo table that you created when executing the schema.sql script.

将数据插入 Azure Database for PostgreSQLInsert data into Azure Database for PostgreSQL

在 src/main/java/DemoApplication.java 文件中,在 main 方法之后添加以下方法,以将数据插入数据库:In the src/main/java/DemoApplication.java file, after the main method, add the following method to insert data into the database:

private static void insertData(Todo todo, Connection connection) throws SQLException {
    log.info("Insert data");
    PreparedStatement insertStatement = connection
            .prepareStatement("INSERT INTO todo (id, description, details, done) VALUES (?, ?, ?, ?);");

    insertStatement.setLong(1, todo.getId());
    insertStatement.setString(2, todo.getDescription());
    insertStatement.setString(3, todo.getDetails());
    insertStatement.setBoolean(4, todo.isDone());
    insertStatement.executeUpdate();
}

你现在可以对 main 方法中的以下两行执行取消注释操作:You can now uncomment the two following lines in the main method:

Todo todo = new Todo(1L, "configuration", "congratulations, you have set up JDBC correctly!", true);
insertData(todo, connection);

现在,执行主类应会生成以下输出:Executing the main class should now produce the following output:

[INFO   ] Loading application properties 
[INFO   ] Connecting to the database 
[INFO   ] Database connection test: demo 
[INFO   ] Create database schema 
[INFO   ] Insert data 
[INFO   ] Closing database connection

从 Azure Database for PostgreSQL 读取数据Reading data from Azure Database for PostgreSQL

接下来,请阅读前面插入的数据,验证代码是否正常工作。Let's read the data previously inserted, to validate that our code works correctly.

在 src/main/java/DemoApplication.java 文件中,在 insertData 方法之后添加以下方法,以从数据库中读取数据:In the src/main/java/DemoApplication.java file, after the insertData method, add the following method to read data from the database:

private static Todo readData(Connection connection) throws SQLException {
    log.info("Read data");
    PreparedStatement readStatement = connection.prepareStatement("SELECT * FROM todo;");
    ResultSet resultSet = readStatement.executeQuery();
    if (!resultSet.next()) {
        log.info("There is no data in the database!");
        return null;
    }
    Todo todo = new Todo();
    todo.setId(resultSet.getLong("id"));
    todo.setDescription(resultSet.getString("description"));
    todo.setDetails(resultSet.getString("details"));
    todo.setDone(resultSet.getBoolean("done"));
    log.info("Data read from the database: " + todo.toString());
    return todo;
}

你现在可以对 main 方法中的以下行执行取消注释操作:You can now uncomment the following line in the main method:

todo = readData(connection);

现在,执行主类应会生成以下输出:Executing the main class should now produce the following output:

[INFO   ] Loading application properties 
[INFO   ] Connecting to the database 
[INFO   ] Database connection test: demo 
[INFO   ] Create database schema 
[INFO   ] Insert data 
[INFO   ] Read data 
[INFO   ] Data read from the database: Todo{id=1, description='configuration', details='congratulations, you have set up JDBC correctly!', done=true} 
[INFO   ] Closing database connection 

更新 Azure Database for PostgreSQL 中的数据Updating data in Azure Database for PostgreSQL

让我们更新之前插入的数据。Let's update the data we previously inserted.

在 src/main/java/DemoApplication.java 文件中,在 readData 方法之后添加以下方法,以更新数据库中的数据:Still in the src/main/java/DemoApplication.java file, after the readData method, add the following method to update data inside the database:

private static void updateData(Todo todo, Connection connection) throws SQLException {
    log.info("Update data");
    PreparedStatement updateStatement = connection
            .prepareStatement("UPDATE todo SET description = ?, details = ?, done = ? WHERE id = ?;");

    updateStatement.setString(1, todo.getDescription());
    updateStatement.setString(2, todo.getDetails());
    updateStatement.setBoolean(3, todo.isDone());
    updateStatement.setLong(4, todo.getId());
    updateStatement.executeUpdate();
    readData(connection);
}

你现在可以对 main 方法中的以下两行执行取消注释操作:You can now uncomment the two following lines in the main method:

todo.setDetails("congratulations, you have updated data!");
updateData(todo, connection);

现在,执行主类应会生成以下输出:Executing the main class should now produce the following output:

[INFO   ] Loading application properties 
[INFO   ] Connecting to the database 
[INFO   ] Database connection test: demo 
[INFO   ] Create database schema 
[INFO   ] Insert data 
[INFO   ] Read data 
[INFO   ] Data read from the database: Todo{id=1, description='configuration', details='congratulations, you have set up JDBC correctly!', done=true} 
[INFO   ] Update data 
[INFO   ] Read data 
[INFO   ] Data read from the database: Todo{id=1, description='configuration', details='congratulations, you have updated data!', done=true} 
[INFO   ] Closing database connection 

删除 Azure Database for PostgreSQL 中的数据Deleting data in Azure Database for PostgreSQL

最后,让我们删除之前插入的数据。Finally, let's delete the data we previously inserted.

在 src/main/java/DemoApplication.java 文件中,在 updateData 方法之后添加以下方法,以删除数据库中的数据:Still in the src/main/java/DemoApplication.java file, after the updateData method, add the following method to delete data inside the database:

private static void deleteData(Todo todo, Connection connection) throws SQLException {
    log.info("Delete data");
    PreparedStatement deleteStatement = connection.prepareStatement("DELETE FROM todo WHERE id = ?;");
    deleteStatement.setLong(1, todo.getId());
    deleteStatement.executeUpdate();
    readData(connection);
}

你现在可以对 main 方法中的以下行执行取消注释操作:You can now uncomment the following line in the main method:

deleteData(todo, connection);

现在,执行主类应会生成以下输出:Executing the main class should now produce the following output:

[INFO   ] Loading application properties 
[INFO   ] Connecting to the database 
[INFO   ] Database connection test: demo 
[INFO   ] Create database schema 
[INFO   ] Insert data 
[INFO   ] Read data 
[INFO   ] Data read from the database: Todo{id=1, description='configuration', details='congratulations, you have set up JDBC correctly!', done=true} 
[INFO   ] Update data 
[INFO   ] Read data 
[INFO   ] Data read from the database: Todo{id=1, description='configuration', details='congratulations, you have updated data!', done=true} 
[INFO   ] Delete data 
[INFO   ] Read data 
[INFO   ] There is no data in the database! 
[INFO   ] Closing database connection 

结论和资源清除Conclusion and resources clean up

祝贺你!Congratulations! 你已创建了一个 Java 应用程序,该应用程序使用 JDBC 在 Azure Database for PostgreSQL 中存储和检索数据。You've created a Java application that uses JDBC to store and retrieve data from Azure Database for PostgreSQL.

若要清理本快速入门中使用的所有资源,请使用以下命令删除该资源组:To clean up all resources used during this quickstart, delete the resource group using the following command:

az group delete \
    --name $AZ_RESOURCE_GROUP \
    --yes

后续步骤Next steps