Building a web UI for an Azure SQL Server in plain Java

Azure is one of the leading PaaS and IaaS providers, and, despite being a Microsoft service, it is an excellent choice to host Java web applications. I had a chance to host a webinar with Microsoft’s OSS folks about running Java web apps in Azure and during that, I also figured out that Azure is probably the easiest way to get an access to SQL Server, which is essential for many web developers. SQL Server is a proven basis for many busines apps, but you cannot install it that easily on your workstation, especially if you have a Mac or Linux as your development machine.

Using Azure you can easily create SQL Server instances and databases for your projects and also use them remotely during development. Let’s see how you can create an Azure SQL database, load it with a demo data-set and build a modern HTML5 UI for it - using nothing but Java.

Create the database and load it with test data

To start up, you’ll need an Azure account and head to portal.azure.com. From the left menu, choose SQL databases and hit “+” button. The wizard will take you through creating a database server and a database.

Whether you are developing or learning, you might also wish to populate your database with some demo data. For this tutorial I chose the “Sample” dataset as a basis.

Open up the firewall for your workstation

As you most probably want to run a development version of your application locally on your workstation, you also need to let Azure know it should accept connections from it. This is defined per database, so you must choose “All resources” from the main menu and locate the SQL Server which is hosting your database. The “Essentials” panel for your server contains a shortcut to firewall settings, from there click “Add client IP” button and the server will automatically create a rule that allows connections from your current IP address.

You can now try to connect to the server with some SQL client to verify everything works. I configured the JDBC driver to NetBeans and it was an easy to access database with the IDEs built-in SQL server tooling. Setting up some basic SQL client may be handy later when debugging some SQL queries, but it is not necessary in this tutorial.

Connecting from Java web apps

The first thing we need with Java apps is the JDBC driver for SQL Server. You can download it from Microsoft for free. Most Java developers nowadays use a dependency management system, but the driver is not available from any public Maven repository. Thus, you should probably install it to your company’s Maven proxy or to your local maven repository. I used Maven coordinates com.microsoft.sqlserver:sqljdbc42:4.2, but use the convention you are used to.

One could now go with low level JDBC API and make queries to the database, but that seldom really makes sense. Pretty much always you should choose either a Java EE server and use its data sources or go with the Spring stack. This way you’ll get e.g. a proper connection pooling for serious deployment and it is actually also easier to connect to the database this way. Let’s use Spring Boot, Spring Data and JPA repositories in this exercise.

To create a project stub for this exercise, go to start.spring.io. Add  JPA and Vaadin modules to your application.

Next we’ll need to add the JDBC driver dependency to the project. If you installed it with the same “Maven coordinates”  as I did, just add the following dependency to the pom.xml file:

 <dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>sqljdbc42</artifactId>
    <version>4.2</version>
</dependency>

Azure makes it really easy to create the JDBC connection. You can just go to the console, find your database and copy the JDBC connection string to your clipboard. With Spring Data, you can just place the JDBC connection string to your application.properties file, which start.spring.io by default created to src/main/resources/. Also, you can explicitly specify the JDBC driver and hibernate dialect to be used. After this step my application.properties file looked like this:

spring.datasource.url=jdbc:sqlserver://vaadin-demo.database.windows.net:1433;database=vaadin-demo;user=vaadin-demo@vaadin-demo;password=******;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect

As we chose to use JPA to map the database data into Java classes, we now need JPA entity objects. I used the reverse engineering tools in NetBeans to automatically create a domain model from the example dataset. But let’s not concentrate on using reverse engineering tools in this tutorial and you can just copy my Customer JPA entity class to your application.

Also note that by default Spring Data uses a naming strategy that doesn’t respect the @Column annotations in the entity class created by NetBeans. Thus, you need to replace that with e.g. hibernates default naming strategy by adding the following configuration to your application.properties file:

spring.jpa.hibernate.naming-strategy=org.hibernate.cfg.DefaultNamingStrategy

We could now use the EntityManager API to access the database, but with Spring Data it makes more sense to create a repository for the Customer entities. Create the following interface to your application:

package com.example;

import org.springframework.data.jpa.repository.JpaRepository;

public interface CustomerRepository extends JpaRepository
           
            {
}

          
          

Based on that interface alone, Spring Data will generate a repository instance via which we can make all basic CRUD operations. Naturally, you’ll add your domain specific query methods to that later.

Now we are ready to actually start building the UI to access your data. Vaadin core is basically just a set of core components you use to build your application. The UI class is the “root” of your component tree and its init method is called when a user opens the mapped webpage. You can also extend Vaadin really easily or use some of the numerous add-on libraries.

To create a really simple listing from the database running in Azure, create a VaadinUI class with the following implementation:

package com.example;

import com.vaadin.annotations.Theme;
import com.vaadin.data.util.BeanItemContainer;
import com.vaadin.server.VaadinRequest;
import com.vaadin.spring.annotation.SpringUI;
import com.vaadin.ui.Table;
import com.vaadin.ui.UI;
import java.util.Arrays;
import org.springframework.beans.factory.annotation.Autowired;

@SpringUI
@Theme("valo")
public class VaadinUI extends UI {

   @Autowired
   CustomerRepository repo;

   @Override
   protected void init(VaadinRequest vaadinRequest) {
       Table table = new Table();
       table.setContainerDataSource(
               new BeanItemContainer(Customer.class, repo.findAll()),
               Arrays.asList("firstName", "lastName", "emailAddress")
       );
       setContent(table);
   }
}

Now if you run the main method of the Application class or execute “mvn spring-boot:run” from the project root, you’ll get a web server running at http://localhost:8080 with the following UI:

I’d call this a dead simple way for Java developers to build a web UI for SQL Server. The full example we built in the webinar is available via GitHub, but more than that, if you want to extend this example, I suggest you should refer to Vaadin tutorials and look into my Spring Data + Vaadin example.

(Register to see the recorded webinar)