在 Azure SQL 数据库中使用 Java 和 JDBCUse Java and JDBC with Azure SQL Database

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

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_SQL_SERVER_USERNAME=demo
AZ_SQL_SERVER_PASSWORD=<YOUR_AZURE_SQL_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>:Azure SQL 数据库服务器的名称。<YOUR_DATABASE_NAME>: The name of your Azure SQL Database server. 它在 Azure 中应是唯一的。It should be unique across Azure.
  • <YOUR_AZURE_REGION>:将使用的 Azure 区域。<YOUR_AZURE_REGION>: The Azure region you'll use. 可输入 az account list-locations,获取可用区域的完整列表。You can have the full list of available regions by entering az account list-locations.
  • <AZ_SQL_SERVER_PASSWORD>:Azure SQL 数据库服务器的密码。<AZ_SQL_SERVER_PASSWORD>: The password of your Azure SQL 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 using the following command:

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

创建 Azure SQL 数据库实例Create an Azure SQL Database instance

首先,我们将创建一个托管的 Azure SQL 数据库服务器。The first thing we'll create is a managed Azure SQL Database server.

备注

可以在快速入门:创建 Azure SQL 数据库单一数据库中阅读有关创建 Azure SQL 数据库服务器的更多详细信息。You can read more detailed information about creating Azure SQL Database servers in Quickstart: Create an Azure SQL Database single database.

运行下面的命令:Run the following command:

az sql server create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME \
    --location $AZ_LOCATION \
    --admin-user $AZ_SQL_SERVER_USERNAME \
    --admin-password $AZ_SQL_SERVER_PASSWORD \

此命令创建 Azure SQL 数据库服务器。This command creates an Azure SQL Database server.

为 Azure SQL 数据库服务器配置防火墙规则Configure a firewall rule for your Azure SQL Database server

Azure SQL 数据库实例在默认情况下受保护。Azure SQL Database 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 our local IP address at the beginning of this article, you can open the server's firewall by running the following command:

az sql 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 \

配置 Azure SQL 数据库Configure a Azure SQL database

你在前面创建的 Azure SQL 数据库服务器为空。The Azure SQL Database 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 running the following command:

az sql db create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name demo \
    --server $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>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>8.2.2.jre8</version>
        </dependency>
    </dependencies>
</project>

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

  • Java 8Java 8
  • 适用于 Java 的最近的 SQL Server 驱动程序A recent SQL Server driver for Java

准备用于连接到 Azure SQL 数据库的配置文件Prepare a configuration file to connect to Azure SQL database

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

url=jdbc:sqlserver://$AZ_DATABASE_NAME.database.chinacloudapi.cn:1433;database=demo;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.chinacloudapi.cn;loginTimeout=30;
user=demo@$AZ_DATABASE_NAME
password=$AZ_SQL_SERVER_PASSWORD
  • 将两个 $AZ_DATABASE_NAME 变量替换为在本文开头部分配置的值。Replace the two $AZ_DATABASE_NAME variables with the value that you configured at the beginning of this article.
  • $AZ_SQL_SERVER_PASSWORD 变量替换为在本文开头部分配置的值。Replace the $AZ_SQL_SERVER_PASSWORD variable with the value that you configured at the beginning of this article.

创建 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 INT PRIMARY KEY, description VARCHAR(255), details VARCHAR(4096), done BIT);

编写应用程序代码Code the application

连接到数据库Connect to the database

接下来添加 Java 代码,该代码使用 JDBC 在 Azure SQL 数据库中存储和检索数据。Next, add the Java code that will use JDBC to store and retrieve data from your Azure SQL database.

创建 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 文件 ,来连接到 SQL Server 数据库并创建用于存储数据的架构。This Java code will use the application.properties and the schema.sql files that we created earlier, in order to connect to the SQL Server database 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 SQL 数据库,创建数据库架构,然后关闭连接,如控制台日志中所示:The application should connect to the Azure SQL Database, 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 SQL 数据库Insert data into Azure SQL database

在 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 SQL 数据库读取数据Reading data from Azure SQL database

接下来,请阅读前面插入的数据,验证代码是否正常工作。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 SQL 数据库中的数据Updating data in Azure SQL Database

让我们更新之前插入的数据。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 SQL 数据库中的数据Deleting data in Azure SQL database

最后,让我们删除之前插入的数据。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 SQL Database 中存储和检索数据。You've created a Java application that uses JDBC to store and retrieve data from Azure SQL database.

若要清理本快速入门中使用的所有资源,请使用以下命令删除该资源组: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