Migrate to Innovate Summit:
Learn how migrating and modernizing to Azure can boost your business's performance, resilience, and security, enabling you to fully embrace AI.Register now
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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:
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:
Azure CLI
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 a database
The first thing we'll create is a managed logical server for Azure SQL Database.
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 the logical server for your database.
Configure a firewall rule for your server
Azure SQL Database is secured by default since it has 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:
Azure CLI
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 \
| jq
Configure a database
The 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:
Azure CLI
az sql db create \
--resource-group$AZ_RESOURCE_GROUP \
--name demo \
--server$AZ_DATABASE_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:
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:
Java
package com.example.demo;
import java.sql.*;
import java.util.*;
import java.util.logging.Logger;
publicclassDemoApplication{
privatestaticfinal Logger log;
static {
System.setProperty("java.util.logging.SimpleFormatter.format", "[%4$-7s] %5$s %n");
log =Logger.getLogger(DemoApplication.class.getName());
}
publicstaticvoidmain(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.
Note
The database credentials are stored in the user and password properties of the application.properties file. 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:
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 package 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:
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:
Java
privatestatic 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!");
returnnull;
}
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:
Java
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
Update data
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:
Java
privatestaticvoidupdateData(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:
Java
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
Delete data
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:
Java
privatestaticvoiddeleteData(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:
Java
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:
Azure CLI
az group delete \
--name$AZ_RESOURCE_GROUP \
--yes
Start here and learn how you can get the full power of Azure with your Java apps - use idiomatic libraries to connect and interact with your preferred cloud services, including Azure SQL and NoSQL databases, messaging and eventing systems, Redis cache, storage and directory services. As always, use tools and frameworks that you know and love – Spring, Tomcat, WildFly, JBoss, WebLogic, WebSphere, Maven, Gradle, IntelliJ, Eclipse, Jenkins, Terraform and more.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.