Over a million developers have joined DZone.

Building a Web UI for MySQL Databases in Plain Java

Learn how to connect MySQL databases from Java web applications, from creating classes, implementing the UI, and running the application.

· Java Zone

Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code! Brought to you in partnership with ZeroTurnaround.

This guide walks you through the process of connecting to MySQL databases from Java web applications using JDBC and Spring Framework. The UI part will be built using Vaadin Framework that allows you to build modern single-page web apps with only Java.

When connecting to databases from Java applications you have two main options: Using an ORM, such as JPA, or using low level JDBC. ORM frameworks are a better option if you are developing an application from scratch. The approach shown in this guide suits cases in which you have an existing and probably old database which might not really be compatible with an ORM, such as JPA.


This guide assumes you have previously installed MySQL Server, your favorite IDE, and Maven. No previous experience with Spring Framework is required to follow this guide.

Create a Spring-based Project

The easiest way to create a new Spring-based application is by using Spring Boot and Spring Initializr. Spring Boot simplifies the process of developing Spring applications. Spring Initializr is a web-based tool that allows you to create project skeletons for Spring applications.

To create a new application, go to http://start.spring.io and add the Vaadin, MySql, and JDBC dependencies as shown in the following figure:

Image title

Click the Generate Project button and extract the generated zip file. You should get a Maven project you can import into your favorite IDE.

Create a MySQL Database

Connect to the MySQL instance and create a new schema:


Create the following table:

CREATE TABLE customers(
    id SERIAL,
    first_name VARCHAR(255),
    last_name VARCHAR(255)

Add some test data, such as the following:

INSERT INTO customers(first_name, last_name) VALUES('Bruce', 'Tate');
INSERT INTO customers(first_name, last_name) VALUES('Mario', 'Fusco');
INSERT INTO customers(first_name, last_name) VALUES('Edson', 'Yanaga');
INSERT INTO customers(first_name, last_name) VALUES('Anton', 'Arhipov');
INSERT INTO customers(first_name, last_name) VALUES('Andres', 'Almiray');

Create a Customer class

Create the following Customer class to encapsulate the data from the customers table:

package com.example;

public class Customer {

    private Long id;
    private String firstName, lastName;

    public Customer(Long id, String firstName, String lastName) {
        this.id = id;
        this.firstName = firstName;
        this.lastName = lastName;

    ... setters and getters ...

Create a Backend Service Class

Start by configuring the database connection in the application.properties file inside the src/main/resources directory:


You may need to change the username and password used to connect to your database.

We will encapsulate the logic to query and modify data in a service class. This service class will use Spring Boot’s autoconfiguration capabilities and Spring Framework’s JdbcTemplate class to connect to the database and to query and update rows in the customers table.

Create the following CustomerService class:

package com.example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.List;

public class CustomerService {

    private JdbcTemplate jdbcTemplate;

    public List findAll() {
        return jdbcTemplate.query(
            "SELECT id, first_name, last_name FROM customers",
                (rs, rowNum) -> new Customer(rs.getLong("id"),
                rs.getString("first_name"), rs.getString("last_name")));

    public void update(Customer customer) {
            "UPDATE customers SET first_name=?, last_name=? WHERE id=?",
            customer.getFirstName(), customer.getLastName(), customer.getId());

Notice how the CustomerService class is annotated with @Component. Spring Framework will automatically create an instance of this class. The term for this kind of instance is a bean. We can inject beans in other beans. Spring Boot itself has defined some beans for us that we can inject into the CustomerService bean. One way of injecting beans is by using the @Autowired annotation. We used this annotation to tell Spring to inject a bean of type JdbcTemplate. This is one of the beans Spring Boot has predefined for us.

The JdbcTemplate class simplifies the use of JDBC. For example, the update method of the JdbcTemplate class will execute an update (or delete) statement using Prepared Statements which protects against SQL injection.

The findAll method in the CustomerService uses Java 8 lambda expressions to map the values of the SQL query result with Customer instances.

Implement the UI

Create a Vaadin UI by implementing the VaadinUI class:

package com.example;

import com.vaadin.annotations.Theme;
import com.vaadin.data.fieldgroup.BeanFieldGroup;
import com.vaadin.data.util.BeanItemContainer;
import com.vaadin.server.VaadinRequest;
import com.vaadin.spring.annotation.SpringUI;
import com.vaadin.ui.*;
import org.springframework.beans.factory.annotation.Autowired;

import java.util.List;

public class VaadinUI extends UI {

    private CustomerService service;

    private Customer customer;

    private Grid grid = new Grid();
    private TextField firstName = new TextField("First name");
    private TextField lastName = new TextField("Last name");
    private Button save = new Button("Save", e -> saveCustomer());

    protected void init(VaadinRequest request) {
        grid.setColumns("firstName", "lastName");
        grid.addSelectionListener(e -> updateForm());

        VerticalLayout layout = new VerticalLayout(grid, firstName, lastName,

    private void updateGrid() {
        List customers = service.findAll();
        grid.setContainerDataSource(new BeanItemContainer<>(Customer.class,

    private void updateForm() {
        if (grid.getSelectedRows().isEmpty()) {
        } else {
            customer = (Customer) grid.getSelectedRow();
            BeanFieldGroup.bindFieldsUnbuffered(customer, this);

    private void setFormVisible(boolean visible) {

    private void saveCustomer() {

This class creates a UI containing a Grid component to show all the customers in the database and a form to edit customers’ first and last names. Notice how the VaadinUI class is annotated with @SpringUI. This means we can inject the CustomerService bean in this class and use it to read and update customers.

The most interesting part of this class is how data binding is managed inside the updateForm method. The BeanFieldGroup.bindFieldsUnbuffered method binds the values in the given Customer instance with the Vaadin Fields in the VaadinUI instance (this).

Running the Application

Spring Initializr created the Application class with a standard main method defining the entry point of the Java application. When you run the application using this method, Spring Boot configures and runs a Jetty server on port 8080 (all this can be configured).

Before running the application you have to build it. You can use the command line to build and run the application:

mvn install

cd target
java -jar demo-0.0.1-SNAPSHOT.jar

The following is a screenshot of the application:

Image title

Tips for More Advanced Requirements

The following sections give hints on how to implement more advanced requirements.

Connecting to Multiple Databases

In order to connect to multiple databases, you need to define additional data sources and set the data source in the JdbcTemplate instance. The following is one way of defining an additional data source:

public class DatabaseConfig {

    @ConfigurationProperties(prefix = "datasource2")
    public DataSource dataSource2() {
        return DataSourceBuilder.create().build();

The connection properties can be added into the application.properties file and should be defined using the prefix specified in the @ConfigurationProperties annotation:


The data source can be injected and used in a service class as shown below:

public class CustomerService2 {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource2(DataSource dataSource2) {
        jdbcTemplate = new JdbcTemplate(dataSource2);

    ... findBy / update / delete methods ...

Using Named Parameters

Use the NamedParameterJdbcTemplate class to use named parameters in queries instead of classic question mark placeholders:

public class CustomerService {

    private NamedParameterJdbcTemplate jdbcTemplate;


    public void update(Customer customer) {
                "UPDATE customers SET first_name=:first_name, last_name=:last_name WHERE id=:id",
                new HashMap() {{
                    put("first_name", customer.getFirstName());
                    put("last_name", customer.getLastName());
                    put("id", customer.getId());

Invoking Stored Procedures

For simple stored procedures such as the following:

CREATE PROCEDURE find_all_customers()
    SELECT id, first_name, last_name FROM customers;

A simple call to the procedure using JdbcTemplate is enough:

jdbcTemplate.query("CALL find_all_customers()", ...);

However, for more sophisticated store procedures (such as when having multiple OUT parameters) use the SimpleJdbcCall or StoreProcedure classes.

Lazy Loading

One way to implement lazy loading is to use LIMIT and OFFSET clauses in the SQL queries and introduce parameters for them in the service class. For example:

public List findAll(int limit, int offset) {
    return jdbcTemplate.query(
        "SELECT id, first_name, last_name FROM customers LIMIT ? OFFSET ?",
        new Object[] {limit, offset},
        (rs, rowNum) -> new Customer(rs.getLong("id"), rs.getString("first_name"),

The UI implementation should include components to change the offset accordingly.

It’s also possible to make the Grid component to lazy load data. The easiest way to do this is by using the Viritin add-on. For small or trivial applications, the SqlContainer class is an option but requires a small change in the architecture.

See the complete source code on GitHub.

The Java Zone is brought to you in partnership with ZeroTurnaround. Check out this 8-step guide to see how you can increase your productivity by skipping slow application redeploys and by implementing application profiling, as you code!


The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}