Use Spring Data JDBC with Azure Database for MySQL

Artikkeli
08/28/2024

This tutorial demonstrates how to store data in Azure Database for MySQL database using Spring Data JDBC.

JDBC is the standard Java API to connect to traditional relational databases.

In this tutorial, we include two authentication methods: Microsoft Entra authentication and MySQL authentication. The Passwordless tab shows the Microsoft Entra authentication and the Password tab shows the MySQL authentication.

Microsoft Entra authentication is a mechanism for connecting to Azure Database for MySQL using identities defined in Microsoft Entra ID. With Microsoft Entra authentication, you can manage database user identities and other Microsoft services in a central location, which simplifies permission management.

MySQL authentication uses accounts stored in MySQL. If you choose to use passwords as credentials for the accounts, these credentials will be stored in the user table. Because these passwords are stored in MySQL, you need to manage the rotation of the passwords by yourself.

Prerequisites

An Azure subscription - create one for free.
Java Development Kit (JDK), version 8 or higher.
Apache Maven.
Azure CLI.

MySQL command line client.
If you don't have a Spring Boot application, create a Maven project with the Spring Initializr. Be sure to select Maven Project and, under Dependencies, add the Spring Web, Spring Data JDBC, and MySQL Driver dependencies, and then select Java version 8 or higher.

If you don't have one, create an Azure Database for MySQL Flexible Server instance named mysqlflexibletest. For instructions, see Quickstart: Use the Azure portal to create an Azure Database for MySQL Flexible Server. Then, create a database named demo. For instructions, see Create and manage databases for Azure Database for MySQL Flexible Server.

See the sample application

In this tutorial, you'll code a sample application. If you want to go faster, this application is already coded and available at https://github.com/Azure-Samples/quickstart-spring-data-jdbc-mysql.

Configure a firewall rule for your MySQL server

Azure Database for MySQL instances are secured by default. They have a firewall that doesn't allow any incoming connection.

To be able to use your database, open the server's firewall to allow the local IP address to access the database server. For more information, see Manage firewall rules for Azure Database for MySQL - Flexible Server using the Azure portal.

If you're connecting to your MySQL server from Windows Subsystem for Linux (WSL) on a Windows computer, you need to add the WSL host IP address to your firewall.

Create a MySQL non-admin user and grant permission

This step will create a non-admin user and grant all permissions on the demo database to it.

Passwordless (Recommended)
Password

You can use the following method to create a non-admin user that uses a passwordless connection.

Service Connector (Recommended)
Manual configuration

Use the following command to install the Service Connector passwordless extension for the Azure CLI:
```
 az extension add --name serviceconnector-passwordless --upgrade
```

Use the following command to create the Microsoft Entra non-admin user:

  az connection create mysql-flexible \
       --resource-group <your_resource_group_name> \
       --connection mysql_conn \
       --target-resource-group <your_resource_group_name> \
       --server mysqlflexibletest \
       --database demo \
       --user-account mysql-identity-id=/subscriptions/<your_subscription_id>/resourcegroups/<your_resource_group_name>/providers/Microsoft.ManagedIdentity/userAssignedIdentities/<your_user_assigned_managed_identity_name> \
       --query authInfo.userName \
       --output tsv

When the command completes, take note of the username in the console output.

Important

To use passwordless connections, create a Microsoft Entra admin user for your Azure Database for MySQL instance. For more information, see the Configure the Microsoft Entra Admin section of Set up Microsoft Entra authentication for Azure Database for MySQL - Flexible Server.

Create a SQL script called create_ad_user.sql for creating a non-admin user. Add the following contents and save it locally:

export AZ_MYSQL_AD_NON_ADMIN_USERID=$(az ad signed-in-user show --query id --output tsv)

cat << EOF > create_ad_user.sql
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '<your_mysql_ad_non_admin_username>' IDENTIFIED BY '$AZ_MYSQL_AD_NON_ADMIN_USERID';
GRANT ALL PRIVILEGES ON demo.* TO '<your_mysql_ad_non_admin_username>'@'%';
FLUSH privileges;
EOF

Then, use the following command to run the SQL script to create the Microsoft Entra non-admin user:

mysql -h mysqlflexibletest.mysql.database.azure.com --user <your_mysql_ad_admin_username> --enable-cleartext-plugin --password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken) < create_ad_user.sql

Tip

To use Microsoft Entra authentication to connect to Azure Database for MySQL, you need to sign in with the Microsoft Entra admin user you set up, and then get the access token as the password. For more information, see Set up Microsoft Entra authentication for Azure Database for MySQL - Flexible Server.

Create a SQL script called create_user.sql for creating a non-admin user. Add the following contents and save it locally:

cat << EOF > create_user.sql
CREATE USER '<your_mysql_non_admin_username>'@'%' IDENTIFIED BY '<your_mysql_non_admin_password>';
GRANT ALL PRIVILEGES ON demo.* TO '<your_mysql_non_admin_username>'@'%';
FLUSH PRIVILEGES;
EOF

Then, use the following command to run the SQL script to create the non-admin user:

mysql -h mysqlflexibletest.mysql.database.azure.com --user <your_mysql_admin_username> --enable-cleartext-plugin --password=<your_mysql_admin_password> < create_user.sql

Tip

You can read more detailed information about creating MySQL users in Create users in Azure Database for MySQL.

Store data from Azure Database for MySQL

Now that you have an Azure Database for MySQL Flexible server instance, you can store data by using Spring Cloud Azure.

To install the Spring Cloud Azure Starter JDBC MySQL module, add the following dependencies to your pom.xml file:

The Spring Cloud Azure Bill of Materials (BOM):
```
<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>com.azure.spring</groupId>
      <artifactId>spring-cloud-azure-dependencies</artifactId>
      <version>5.19.0</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>
```
Note

If you're using Spring Boot 2.x, be sure to set the spring-cloud-azure-dependencies version to 4.19.0. This Bill of Material (BOM) should be configured in the <dependencyManagement> section of your pom.xml file. This ensures that all Spring Cloud Azure dependencies are using the same version. For more information about the version used for this BOM, see Which Version of Spring Cloud Azure Should I Use.

The Spring Cloud Azure Starter JDBC MySQL artifact:

<dependency>
  <groupId>com.azure.spring</groupId>
  <artifactId>spring-cloud-azure-starter-jdbc-mysql</artifactId>
</dependency>

Note

Passwordless connections have been supported since version 4.5.0.

Configure Spring Boot to use Azure Database for MySQL

To store data from Azure Database for MySQL using Spring Data JDBC, follow these steps to configure the application:

Configure Azure Database for MySQL credentials by adding the following properties to your application.properties configuration file.
- Passwordless (Recommended)
- Password
```
logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:mysql://mysqlflexibletest.mysql.database.azure.com:3306/demo?serverTimezone=UTC
spring.datasource.username=<your_mysql_ad_non_admin_username>
spring.datasource.azure.passwordless-enabled=true

spring.sql.init.mode=always
```
```
logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:mysql://mysqlflexibletest.mysql.database.azure.com:3306/demo?serverTimezone=UTC
spring.datasource.username=<your_mysql_non_admin_username>
spring.datasource.password=<your_mysql_non_admin_password>

spring.sql.init.mode=always
```
Warning

The configuration property spring.sql.init.mode=always means that Spring Boot will automatically generate a database schema, using the schema.sql file that you'll create next, each time the server is started. This feature is great for testing, but remember that it will delete your data at each restart, so you shouldn't use it in production.

The configuration property spring.datasource.url has ?serverTimezone=UTC appended to tell the JDBC driver to use the UTC date format (or Coordinated Universal Time) when connecting to the database. Without this parameter, your Java server wouldn't use the same date format as the database, which would result in an error.

If you don't have one, create an Azure Database for MySQL Single server instance named mysqlsingletest. For instructions, see Quickstart: Create an Azure Database for MySQL server by using the Azure portal. Then, create a database named demo. For instructions, see the Create a database section of Create users in Azure Database for MySQL.

See the sample application

In this article, you'll code a sample application. If you want to go faster, this application is already coded and available at https://github.com/Azure-Samples/quickstart-spring-data-jdbc-mysql.

Configure a firewall rule for your MySQL server

Azure Database for MySQL instances are secured by default. They have a firewall that doesn't allow any incoming connection.

To be able to use your database, open the server's firewall to allow the local IP address to access the database server. For more information, see Create and manage Azure Database for MySQL firewall rules by using the Azure portal.

If you're connecting to your MySQL server from Windows Subsystem for Linux (WSL) on a Windows computer, you need to add the WSL host IP address to your firewall.

Create a MySQL non-admin user and grant permission

This step will create a non-admin user and grant all permissions on the demo database to it.

Passwordless (Recommended)
Password

Important

To use passwordless connections, create a Microsoft Entra admin user for your Azure Database for MySQL instance. For more information, see the Setting the Microsoft Entra Admin user section of Use Microsoft Entra ID for authentication with MySQL.

Create a SQL script called create_ad_user.sql for creating a non-admin user. Add the following contents and save it locally:

export AZ_MYSQL_AD_NON_ADMIN_USERID=$(az ad signed-in-user show --query id --output tsv)

cat << EOF > create_ad_user.sql
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '<your_mysql_ad_non_admin_username>' IDENTIFIED BY '$AZ_MYSQL_AD_NON_ADMIN_USERID';
GRANT ALL PRIVILEGES ON demo.* TO '<your_mysql_ad_non_admin_username>'@'%';
FLUSH privileges;
EOF

Then, use the following command to run the SQL script to create the Microsoft Entra non-admin user:

mysql -h mysqlsingletest.mysql.database.azure.com --user <your_mysql_ad_admin_username>@mysqlsingletest --enable-cleartext-plugin --password=$(az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken) < create_ad_user.sql

Tip

To use Microsoft Entra authentication to connect to Azure Database for MySQL, you need to sign in with the Microsoft Entra admin user you set up, and then get the access token as the password. For more information, see Use Microsoft Entra ID for authentication with MySQL.

Create a SQL script called create_user.sql for creating a non-admin user. Add the following contents and save it locally:

cat << EOF > create_user.sql
CREATE USER '<your_mysql_non_admin_username>'@'%' IDENTIFIED BY '<your_mysql_non_admin_password>';
GRANT ALL PRIVILEGES ON demo.* TO '<your_mysql_non_admin_username>'@'%';
FLUSH PRIVILEGES;
EOF

Then, use the following command to run the SQL script to create the non-admin user:

mysql -h mysqlsingletest.mysql.database.azure.com --user <your_mysql_admin_username>@mysqlsingletest --enable-cleartext-plugin --password=<your_mysql_admin_password> < create_user.sql

Note

For more information, see Create users in Azure Database for MySQL.

Store data from Azure Database for MySQL

Now that you have an Azure Database for MySQL Single Server instance, you can store data by using Spring Cloud Azure.

To install the Spring Cloud Azure Starter JDBC MySQL module, add the following dependencies to your pom.xml file:

The Spring Cloud Azure Bill of Materials (BOM):
```
<dependencyManagement>
  <dependencies>
    <dependency>
      <groupId>com.azure.spring</groupId>
      <artifactId>spring-cloud-azure-dependencies</artifactId>
      <version>5.19.0</version>
      <type>pom</type>
      <scope>import</scope>
    </dependency>
  </dependencies>
</dependencyManagement>
```
Note

If you're using Spring Boot 2.x, be sure to set the spring-cloud-azure-dependencies version to 4.19.0. This Bill of Material (BOM) should be configured in the <dependencyManagement> section of your pom.xml file. This ensures that all Spring Cloud Azure dependencies are using the same version. For more information about the version used for this BOM, see Which Version of Spring Cloud Azure Should I Use.

The Spring Cloud Azure Starter JDBC MySQL artifact:

<dependency>
  <groupId>com.azure.spring</groupId>
  <artifactId>spring-cloud-azure-starter-jdbc-mysql</artifactId>
</dependency>

Note

Passwordless connections have been supported since version 4.5.0.

Configure Spring Boot to use Azure Database for MySQL

To store data from Azure Database for MySQL using Spring Data JDBC, follow these steps to configure the application:

Configure Azure Database for MySQL credentials by adding the following properties to your application.properties configuration file.
- Passwordless (Recommended)
- Password
```
logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:mysql://mysqlsingletest.mysql.database.azure.com:3306/demo?serverTimezone=UTC
spring.datasource.username=<your_mysql_ad_non_admin_username>@mysqlsingletest
spring.datasource.azure.passwordless-enabled=true

spring.sql.init.mode=always
```
```
logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:mysql://mysqlsingletest.mysql.database.azure.com:3306/demo?serverTimezone=UTC
spring.datasource.username=<your_mysql_non_admin_username>@mysqlsingletest
spring.datasource.password=<your_mysql_non_admin_password>

spring.sql.init.mode=always
```
Warning

The configuration property spring.sql.init.mode=always means that Spring Boot will automatically generate a database schema, using the schema.sql file that you'll create next, each time the server is started. This feature is great for testing, but remember that it will delete your data at each restart, so you shouldn't use it in production.

The configuration property spring.datasource.url has ?serverTimezone=UTC appended to tell the JDBC driver to use the UTC date format (or Coordinated Universal Time) when connecting to the database. Without this parameter, your Java server wouldn't use the same date format as the database, which would result in an error.

Create the src/main/resources/schema.sql configuration file to configure the database schema, then add the following contents.

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

Create a new Todo Java class. This class is a domain model mapped onto the todo table that will be created automatically by Spring Boot. The following code ignores the getters and setters methods.

import org.springframework.data.annotation.Id;

public class Todo {

    public Todo() {
    }

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

    @Id
    private Long id;

    private String description;

    private String details;

    private boolean done;

}

Edit the startup class file to show the following content.
```
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Bean;
import org.springframework.data.repository.CrudRepository;

import java.util.stream.Stream;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }

    @Bean
    ApplicationListener<ApplicationReadyEvent> basicsApplicationListener(TodoRepository repository) {
        return event->repository
            .saveAll(Stream.of("A", "B", "C").map(name->new Todo("configuration", "congratulations, you have set up correctly!", true)).toList())
            .forEach(System.out::println);
    }

}

interface TodoRepository extends CrudRepository<Todo, Long> {

}
```
Tip

In this tutorial, there are no authentication operations in the configurations or the code. However, connecting to Azure services requires authentication. To complete the authentication, you need to use Azure Identity. Spring Cloud Azure uses DefaultAzureCredential, which the Azure Identity library provides to help you get credentials without any code changes.

DefaultAzureCredential supports multiple authentication methods and determines which method to use at runtime. This approach enables your app to use different authentication methods in different environments (such as local and production environments) without implementing environment-specific code. For more information, see DefaultAzureCredential.

To complete the authentication in local development environments, you can use Azure CLI, Visual Studio Code, PowerShell, or other methods. For more information, see Azure authentication in Java development environments. To complete the authentication in Azure hosting environments, we recommend using user-assigned managed identity. For more information, see What are managed identities for Azure resources?

Start the application. The application stores data into the database. You'll see logs similar to the following example:

2023-02-01 10:22:36.701 DEBUG 7948 --- [main] o.s.jdbc.core.JdbcTemplate : Executing prepared SQL statement [INSERT INTO todo (description, details, done) VALUES (?, ?, ?)]    
com.example.demo.Todo@4bdb04c8

Deploy to Azure Spring Apps

Now that you have the Spring Boot application running locally, it's time to move it to production. Azure Spring Apps makes it easy to deploy Spring Boot applications to Azure without any code changes. The service manages the infrastructure of Spring applications so developers can focus on their code. Azure Spring Apps provides lifecycle management using comprehensive monitoring and diagnostics, configuration management, service discovery, CI/CD integration, blue-green deployments, and more. To deploy your application to Azure Spring Apps, see Deploy your first application to Azure Spring Apps.

Next steps

Azure for Spring developers Spring Cloud Azure MySQL Samples

Jaa

Use Spring Data JDBC with Azure Database for MySQL

Prerequisites

See the sample application

Configure a firewall rule for your MySQL server

Create a MySQL non-admin user and grant permission

Store data from Azure Database for MySQL

Configure Spring Boot to use Azure Database for MySQL

See the sample application

Configure a firewall rule for your MySQL server

Create a MySQL non-admin user and grant permission

Store data from Azure Database for MySQL

Configure Spring Boot to use Azure Database for MySQL

Deploy to Azure Spring Apps

Next steps

Palaute

Lisäresursseja