jdbc mysql sql model_快速入门：将 Java 和 JDBC 与 Azure Database for MySQL 配合使用

您现在访问的是微软AZURE全球版技术文档网站，若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站，请访问 https://docs.azure.cn.

快速入门：将 Java 和 JDBC 与 Azure Database for MySQL 配合使用Quickstart: Use Java and JDBC with Azure Database for MySQL

08/17/2020

本文内容

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

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

先决条件Prerequisites

一个 Azure 帐户。An Azure account. If you don't have one, get a free trial.

建议使用 Azure Cloud Shell，这样你便可自动登录且有权访问所需的所有工具。We recommend Azure Cloud Shell so you'll be logged in automatically and have access to all the tools you'll need.

受支持的 Java 开发工具包，版本 8(已在 Azure Cloud Shell 中包括)。A supported Java Development Kit, version 8 (included in Azure Cloud Shell).

准备工作环境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=

AZ_LOCATION=

AZ_MYSQL_USERNAME=demo

AZ_MYSQL_PASSWORD=

AZ_LOCAL_IP_ADDRESS=

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

：MySQL 服务器的名称。: The name of your MySQL server. 它在 Azure 中应是唯一的。It should be unique across Azure.

：将使用的 Azure 区域。: The Azure region you'll use. 默认情况下可以使用 eastus，但我们建议你配置一个离居住位置更近的区域。You can use eastus by default, but 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.

：MySQL 数据库服务器的密码。: The password of your MySQL 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).

：你将在其中运行 Java 应用程序的本地计算机的 IP 地址。: The IP address of your local computer, from which you'll run your Java application. 若要找到该地址，一种简便方法是使浏览器指向 whatismyip.akamai.com。One convenient way to find it is to point your browser to whatismyip.akamai.com.

接下来，创建一个资源组：Next, create a resource group:

az group create \

--name $AZ_RESOURCE_GROUP \

--location $AZ_LOCATION \

| jq

备注

我们使用默认安装在 Azure Cloud Shell 上的 jq 实用工具来显示 JSON 数据，提高其可读性。We use the jq utility, which is installed by default on Azure Cloud Shell to display JSON data and make it more readable.

如果你不喜欢该实用工具，可安全地删除要使用的所有命令的 | jq 部分。If you don't like that utility, you can safely remove the | jq part of all the commands we'll use.

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

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

In Azure Cloud Shell, run the following script:

az mysql 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_MYSQL_USERNAME \

--admin-password $AZ_MYSQL_PASSWORD \

| jq

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

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

Azure Database for MySQL 实例在默认情况下受保护。Azure Database for MySQL 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:

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

| jq

配置 MySQL 数据库Configure a MySQL database

之前创建的 MySQL 服务器为空。The MySQL 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:

az mysql db create \

--resource-group $AZ_RESOURCE_GROUP \

--name demo \

--server-name $AZ_DATABASE_NAME \

| jq

新建一个 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:

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

com.example

demo

0.0.1-SNAPSHOT

demo

1.8

1.8

1.8

mysql

mysql-connector-java

8.0.20

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

Java 8Java 8

最新的用于 Java 的 MySQL 驱动程序A recent MySQL driver for Java

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

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

url=jdbc:mysql://$AZ_DATABASE_NAME.mysql.database.azure.com:3306/demo?serverTimezone=UTC

user=demo@$AZ_DATABASE_NAME

password=$AZ_MYSQL_PASSWORD

将两个 $AZ_DATABASE_NAME 变量替换为在本文开头部分配置的值。Replace the two $AZ_DATABASE_NAME variables with the value that you configured at the beginning of this article.

将 $AZ_MYSQL_PASSWORD 变量替换为在本文开头部分配置的值。Replace the $AZ_MYSQL_PASSWORD variable with the value that you configured at the beginning of this article.

备注

我们将 ?serverTimezone=UTC 追加到配置属性 url 中，以指示 JDBC 驱动程序在连接到数据库时使用 UTC 日期格式(或协调世界时)。We append ?serverTimezone=UTC to the configuration property url, to tell the JDBC driver to use the UTC date format (or Coordinated Universal Time) when connecting to the database. 否则，Java 服务器将不使用与数据库相同的日期格式，这将导致错误。Otherwise, our Java server would not use the same date format as the database, which would result in an error.

创建 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

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

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

package com.example.demo;

import com.mysql.cj.jdbc.AbandonedConnectionCleanupThread;

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();

AbandonedConnectionCleanupThread.uncheckedShutdown();

}

}

此 Java 代码将使用之前创建的“application.properties”和“schema.sql”文件，来连接到 MySQL 服务器并创建用于存储数据的架构 。This Java code will use the application.properties and the schema.sql files that we created earlier, in order to connect to the MySQL 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.

备注

结尾处的 AbandonedConnectionCleanupThread.uncheckedShutdown(); 行是 MySQL 驱动程序特定的命令，用于在关闭应用程序时销毁内部线程。The AbandonedConnectionCleanupThread.uncheckedShutdown(); line at the end is a MySQL driver specific command to destroy an internal thread when shutting down the application.

可放心忽略此警告。It can be safely ignored.

现在可以通过喜欢的工具执行此主类：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 MySQL，创建数据库架构，然后关闭连接，如控制台日志中所示：The application should connect to the Azure Database for MySQL, 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 MySQLInsert data into Azure Database for MySQL

在 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 MySQL 读取数据Reading data from Azure Database for MySQL

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

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

最后，让我们删除之前插入的数据。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

清理资源Clean up resources

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

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