Skip to content

Latest commit

 

History

History
126 lines (85 loc) · 6.51 KB

configure-spring-data-jdbc-with-azure-sql-server.md

File metadata and controls

126 lines (85 loc) · 6.51 KB
Raw
title description ms.date author ms.author ms.topic ms.custom
Use Spring Data JDBC with Azure SQL Database
Learn how to use Spring Data JDBC with an Azure SQL Database.
04/06/2023
KarlErickson
hangwan
article
devx-track-java, devx-track-azurecli, team=cloud_advocates, spring-cloud-azure, passwordless-java, devx-track-extended-java

Use Spring Data JDBC with Azure SQL Database

This tutorial demonstrates how to store data in Azure SQL 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 SQL Database authentication. The Passwordless tab shows the Microsoft Entra authentication and the Password tab shows the SQL Database authentication.

Microsoft Entra authentication is a mechanism for connecting to Azure Database for SQL Database 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.

SQL Database authentication uses accounts stored in SQL Database. 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 SQL Database, you need to manage the rotation of the passwords by yourself.

[!INCLUDE spring-data-prerequisites.md]

  • sqlcmd Utility.

  • ODBC Driver 17 or 18.

  • If you don't have one, create an Azure SQL Server instance named sqlservertest and a database named demo. For instructions, see Quickstart: Create a single database - Azure SQL Database.

  • 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 MS SQL Server Driver dependencies, and then select Java version 8 or higher.

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-sql-server.

[!INCLUDE spring-data-sql-server-setup.md]

Store data from Azure SQL Database

With an Azure SQL Database instance, you can store data by using Spring Cloud Azure.

To install the Spring Cloud Azure Starter 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.13.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 artifact:

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

    [!NOTE] As this is a dependency, it should be added in the <dependencies> section of the pom.xml. Its version is not configured here, as it is managed by the BOM that we added previously.

Configure Spring Boot to use Azure SQL Database

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

  1. Configure an Azure SQL Database credentials in the application.properties configuration file.

    Passwordless (Recommended)

    logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:sqlserver://sqlservertest.database.windows.net:1433;databaseName=demo;authentication=DefaultAzureCredential;

spring.sql.init.mode=always

    Password

    logging.level.org.springframework.jdbc.core=DEBUG

spring.datasource.url=jdbc:sqlserver://sqlservertest.database.windows.net:1433;database=demo;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
spring.datasource.username=<your_sql_server_non_admin_username>@sqlservertest
spring.datasource.password=<your_sql_server_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 is great for testing, but remember that this will delete your data at each restart, so you shouldn't use it in production.

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

[!INCLUDE spring-data-jdbc-create-application.md]

[!INCLUDE deploy-to-azure-spring-apps]

Next steps

[!div class="nextstepaction"] Azure for Spring developers