title

description

author

ms.author

ms.date

ms.service

ms.subservice

ms.topic

ms.custom

ms.devlang

monikerRange

Use Java and JDBC with Azure SQL Database

Learn how to use Java and JDBC with an Azure SQL Database.

jdubois

bbenz

04/07/2023

sql-database

development

quickstart

devx-track-java

devx-track-azurecli

mode-api

passwordless-java

java

= azuresql || = azuresql-db || = azuresql-mi

Use Java and JDBC with Azure SQL Database

This topic demonstrates creating a sample application that uses Java and JDBC to store and retrieve information in Azure SQL Database.

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

Prerequisites

An Azure account. If you don't have one, get a free trial.
Azure Cloud Shell or Azure CLI. We recommend Azure Cloud Shell so you'll be logged in automatically and have access to all the tools you'll need.
A supported Java Development Kit, version 11 (included in Azure Cloud Shell).
The Apache Maven build tool (included in Azure Cloud Shell).

Prepare the working environment

We are using environment variables to limit typing mistakes, avoid exposing sensitive information to the public if this sample code is pushed to a repo, and make it easier for you to customize the following configuration for your specific needs.

Set up those environment variables by using the following commands:

Passwordless (Recommended)

AZ_RESOURCE_GROUP=<YOUR_RESOURCE_GROUP_NAME>
AZ_DATABASE_SERVER_NAME=<YOUR_DATABASE_SERVER_NAME>
AZ_DATABASE_NAME=<YOUR_DATABASE_NAME>
AZ_LOCATION=<YOUR_AZURE_REGION>
AZ_LOCAL_IP_ADDRESS=<YOUR_LOCAL_IP_ADDRESS>
CURRENT_USERNAME=$(az ad signed-in-user show --query userPrincipalName --output tsv)
CURRENT_USER_OBJECTID=$(az ad signed-in-user show --query id --output tsv)

Connection String

AZ_RESOURCE_GROUP=<YOUR_RESOURCE_GROUP_NAME>
AZ_DATABASE_SERVER_NAME=<YOUR_DATABASE_SERVER_NAME>
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>: The name of your Azure SQL Database server. It should be unique across Azure.
<YOUR_AZURE_REGION>: The Azure region you'll use. You can use eastus by default, but we recommend that you configure a region closer to where you live. You can have the full list of available regions by entering az account list-locations.
<AZ_SQL_SERVER_PASSWORD>: The password of your Azure SQL Database server. That password should have a minimum of eight characters. 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>: The IP address of your local computer, from which you'll run your Java application. One 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 \
    | jq

Note

We use the jq utility to display JSON data and make it more readable. This utility is installed by default on Azure Cloud Shell. If you don't like that utility, you can safely remove the | jq part of all the commands we'll use.

Create an Azure SQL Database instance

The first thing we'll create is a managed Azure SQL Database server.

Note

You can read more detailed information about creating Azure SQL Database servers in Quickstart: Create an Azure SQL Database single database.

Passwordless (Recommended)

In Azure Cloud Shell, run the following command:

az sql server create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME \
    --location $AZ_LOCATION \
    --enable-ad-only-auth \
    --external-admin-principal-type User \
    --external-admin-name $CURRENT_USERNAME \
    --external-admin-sid $CURRENT_USER_OBJECTID \
    | jq

This command creates an Azure SQL Database server and sets the Azure AD admin to the current signed-in user.

Note

You can only create one Azure AD admin per Azure SQL Database server. Selection of another one will overwrite the existing Azure AD admin configured for the server.

Connection String

In Azure Cloud Shell, 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 \
    | jq

This command creates an Azure SQL Database server with a dependency on a connection string containing a pre-defined admin user and password.

Configure a firewall rule for your Azure SQL Database server

Azure SQL Database instances are secured by default. They have a firewall that doesn't allow any incoming connection. 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.

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_SERVER_NAME \
    --start-ip-address $AZ_LOCAL_IP_ADDRESS \
    --end-ip-address $AZ_LOCAL_IP_ADDRESS \
    | jq

Configure a Azure SQL database

The Azure SQL Database server that you created earlier is empty. It doesn't have any database that you can use with the Java application. Create a new database called demo by running the following command:

az sql db create \
    --resource-group $AZ_RESOURCE_GROUP \
    --name $AZ_DATABASE_NAME \
    --server $AZ_DATABASE_SERVER_NAME \
    | jq

Create a new Java project

Using your favorite IDE, create a new Java project, and add a pom.xml file in its root directory:

Passwordless (Recommended)

This file is an Apache Maven that configures our project to use:

Java 11
A recent SQL Server driver for Java
The azure-identity dependency for passwordless connection enablement

<?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>11</java.version>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.2.0.jre11</version>
        </dependency>
       <dependency>
            <groupId>com.azure</groupId>
            <artifactId>azure-identity</artifactId>
            <version>1.8.1</version>
       </dependency>
    </dependencies>
</project>

Connection String

Java 11
A recent SQL Server driver for Java

<?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>11</java.version>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>mssql-jdbc</artifactId>
            <version>12.2.0.jre11</version>
        </dependency>
    </dependencies>
</project>

Prepare a configuration file to connect to Azure SQL database

Passwordless (Recommended)

Create a src/main/resources/application.properties file, and add:

String url = "jdbc:sqlserver://$AZ_DATABASE_SERVER_NAME.database.windows.net:1433;databaseName=$AZ_DATABASE_NAME;authentication=ActiveDirectoryMSI;"   
Connection con = DriverManager.getConnection(url);

Replace AZ_DATABASE_SERVER_NAME and $AZ_DATABASE_NAME with the values that you configured at the beginning of this article.

Connection String

Create a src/main/resources/application.properties file, and add:

url=jdbc:sqlserver://$AZ_DATABASE_NAME.database.windows.net:1433;database=demo;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
user=demo@$AZ_DATABASE_NAME
password=$AZ_SQL_SERVER_PASSWORD

Replace AZ_DATABASE_SERVER_NAME and $AZ_DATABASE_NAME with the values that you configured at the beginning of this article.
Replace the $AZ_SQL_SERVER_PASSWORD variable with the value that you configured at the beginning of this article.

Create an SQL file to generate the database schema

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

Next, add the Java code that will use JDBC to store and retrieve data from your Azure SQL database.

Create a src/main/java/com/example/demo/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();
    }
}

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.

You can now execute this main class with your favorite tool:

Using your IDE, you should be able to right-click on the DemoApplication class and execute it.
Using Maven, you can run the application by executing: mvn exec:java -Dexec.mainClass="com.example.demo.DemoApplication".

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

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 +
                '}';
    }
}

This class is a domain model mapped on the todo table that you created when executing the schema.sql script.

Insert data into Azure SQL database

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

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

Reading data from Azure SQL database

Let's read the data previously inserted, to validate that our code works correctly.

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;
}

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

Updating data in Azure SQL Database

Let's update the data we previously inserted.

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

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

Deleting data in Azure SQL database

Finally, let's delete the data we previously inserted.

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

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! 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

Design your first database in Azure SQL Database
Microsoft JDBC Driver for SQL Server
Report issues/ask questions

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

connect-query-java.md

connect-query-java.md

Use Java and JDBC with Azure SQL Database

Prerequisites

Prepare the working environment

Passwordless (Recommended)

Connection String

Create an Azure SQL Database instance

Passwordless (Recommended)

Connection String

Configure a firewall rule for your Azure SQL Database server

Configure a Azure SQL database

Create a new Java project

Passwordless (Recommended)

Connection String

Prepare a configuration file to connect to Azure SQL database

Passwordless (Recommended)

Connection String

Create an SQL file to generate the database schema

Code the application

Connect to the database

Create a domain class

Insert data into Azure SQL database

Reading data from Azure SQL database

Updating data in Azure SQL Database

Deleting data in Azure SQL database

Conclusion and resources clean up

Next steps

Files

connect-query-java.md

Latest commit

History

connect-query-java.md

File metadata and controls

Use Java and JDBC with Azure SQL Database

Prerequisites

Prepare the working environment

Create an Azure SQL Database instance

Configure a firewall rule for your Azure SQL Database server

Configure a Azure SQL database

Create a new Java project

Prepare a configuration file to connect to Azure SQL database

Create an SQL file to generate the database schema

Code the application

Connect to the database

Create a domain class

Insert data into Azure SQL database

Reading data from Azure SQL database

Updating data in Azure SQL Database

Deleting data in Azure SQL database

Conclusion and resources clean up

Next steps