Payara Platform on Microsoft Azure: Accessing SQL Databases

Microsoft Azure provides fully managed Cloud SQL databases for use by your Azure hosted cloud services. Payara® Micro is built to be the best runtime for Cloud Native Java EE and MicroProfile applications. Here’s how to rapidly create a REST web service that retrieves data from an Azure SQL Database and returns it as JSON.

Creating the Database

In this blog we will use the example Adventure Works database which provides us with an example schema already populated with data. Throughout the example you will need to replace the names of resource groups and servers etc. with your choice.

To create a new database server use:

az sql server create --resource-group steve-rg --name payaraexample --admin-user steve --admin-password <password> --location westeurope

After the database is created we need to create a firewall rule to allow Azure services access to the database:

az sql server firewall-rule create --name payaraexample --resource-group steve-rg --name AllowWindowsAzureAccess --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

Note this will allow access from any service running on Azure so ensure your database is secured.

Then to create the example database using the Adventure Works sample schema:

az sql db create --name ExampleDB --server payaraexample --sample-name AdventureWorksLT --resource-group steve-rg

With the database created we can now create the Java EE application using REST.

Example Application

You can clone the example application using:

git clone git@github.com:smillidge/azure-sql-example.git

cd azure-sql-example/AzureSQLExample

mvn clean package

This will build the application into the target directory.

The application consists of a Simple REST web service which uses an EJB Facade to retrieve data from the SQL database using JPA.

The datasource to access the Azure SQL Database is defined in the web.xml

1 2 3 4 5 6 7 8 9 10 <data-source> 
<name>java:global/AzureSQLDB</name> 
<class-name>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</class-name> 
<server-name>${ENV=DB_HOST}</server-name> <port-number>1433</port-number> 
<database-name>${ENV=DB_NAME}</database-name> 
<user>${ENV=JDBC_USER}</user> <!-- Example of using a Payara password alias in the datasource definition --> 
<password>${ENV=JDBC_PASSWORD}</password> </datasource>

The persistence.xml file references the JNDI name of the datasource:

1 2 3 4 5 6 7 8 <?xml version="1.0" encoding="UTF-8"?> 
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence 
http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd"> 
<persistence-unit name="SalesLT" transaction-type="JTA"> 
<jta-data-source>java:global/AzureSQLDB</jta-data-source> 
<exclude-unlisted-classes>false</exclude-unlisted-classes> <properties/> 
</persistence-unit> </persistence>

Finally, the ProductResource class utilises the EJB to expose Product data via REST for example the method below retrieves all products and returns them as JSON;

1 2 3 4 5 @GET @Produces(MediaType.APPLICATION_JSON) public Collection<Product> getProducts() { return products.findAll(); }

In this example we will package the application as a Docker Image and run it directly on Azure using Azure Container Instances. In this way we don’t have to worry about creating and configuring Virtual Machines. Azure Container Instances gives us the ability to run a Docker Image directly on Azure.

We will assume that you have an Azure Container Registry created in your account.

The example application contains two docker files one for a base Payara Micro image which you can use for other applications and the second that builds on that image to add the application.

To build the base image from the directory containing the DockerFile we can use Azure’s container build capability.

az acr build --registry YourRegistry --image payara-micro/base:latest --file DockerfileBase .

Then you can build the application image. Note you will have to edit the Dockerfile and replace the registry name in the line:

FROM payararegistry.azurecr.io/payara-micro/base:latest

with the name of your registry then build the image.

az acr build --registry YourRegistry --image payara-micro/azure-sql-example:latest .

You should now have at least 2 images in your docker registry.

Running the Application on Azure

Now that the images are available in the registry we can run the docker image directly on Azure. The key point is that we need to pass environment variables to the image that replace those contained in the data source definition to ensure they refer to the database created in the first step.

For example the command below will create and run the docker image on Azure:

az container create --resource-group steve-rg --name azure-sql-example 
--image yourregistry.azurecr.io/payara-micro/azure-sql-example:latest 
--cpu 1 --memory 1 --registry-login-server 
yourregistry.azurecr.io --registry-username YourRegistry 
--registry-password <password> --dns-name-label your-payara-azure-sql-example --ports 80 
--environment-variables DB_HOST=payaraexampledatabase.windows.net DB_NAME=ExampleDB 
JDBC_USER=steve JDBC_PASSWORD=<password>

Note the environment variables must correspond to the data entered when creating the SQL Database.

Testing the Application

Once your container is successfully running you should be able to navigate to the URL http://your-payara-azure-sql-example.westeurope.azurecontainer.io/AzureSQLExample/adventureworks/product and see the list of products as JSON data.

Payara Micro Cloud Native Capabilities Run Applications on Microsoft Azure

With the built-in Cloud Native capabilities of Payara Micro, it’s simple to package and run Java EE and MicroProfile applications on Microsoft Azure. Payara Micro can easily be packaged into a Docker Image with a data source to access Azure SQL databases which can be reconfigured easily to suit your cloud deployments.