Close

Entries with tag data binding .

Building a web UI for MySQL databases (in plain Java)

Note: This post has been updated to use Vaadin 8.

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.

Prerequisites

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:

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 SCHEMA demo;

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:

spring.datasource.url=jdbc:mysql://localhost/demo
spring.datasource.username=user
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

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;

@Component
public class CustomerService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List<Customer> 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) {
        jdbcTemplate.update(
            "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.data.Binder;
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;

@SpringUI
public class VaadinUI extends UI {

    @Autowired
    private CustomerService service;

    private Customer customer;
    private Binder<Customer> binder = new Binder<>(Customer.class);

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

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

        binder.bindInstanceFields(this);

        VerticalLayout layout = new VerticalLayout(grid, firstName, lastName, save);
        setContent(layout);
    }

    private void updateGrid() {
        List<Customer> customers = service.findAll();
        grid.setItems(customers);
        setFormVisible(false);
    }

    private void updateForm() {
        if (grid.asSingleSelect().isEmpty()) {
            setFormVisible(false);
        } else {
            customer = grid.asSingleSelect().getValue();
            binder.setBean(customer);
            setFormVisible(true);
        }
    }

    private void setFormVisible(boolean visible) {
        firstName.setVisible(visible);
        lastName.setVisible(visible);
        save.setVisible(visible);
    }

    private void saveCustomer() {
        service.update(customer);
        updateGrid();
    }
}

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. The Binder class allows us to connect the text fields with the corresponding Java properties in the Customer class.

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:

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:

@Configuration
public class DatabaseConfig {

    @Bean
    @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:

datasource2.url=jdbc:mysql://localhost/demo2
datasource2.username=user2
datasource2.password=password2
datasource2.driver-class-name=com.mysql.jdbc.Driver

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

@Component
public class CustomerService2 {

    private JdbcTemplate jdbcTemplate;

    @Autowired
    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:

@Component
public class CustomerService {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    ...

    public void update(Customer customer) {
        jdbcTemplate.update(
                "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()
BEGIN
    SELECT id, first_name, last_name FROM customers;
END

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"),
            rs.getString("last_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. An easy way to do this is by using the Grid.setDataProvider method. This strategy is explained in this blog post.

The complete project with source code is available on GitHub

Further reading that might interest you:

Learn more about Vaadin The easiest way to build web apps in Java

A Web Architects Guide to the Future of Business Web Apps

Mission RIP Table: Migrate to Grid! - Selection

This is a series of tutorials for upgrading the Table component to Grid. It will be performed by migrating to Framework 7 style of Grid, then migrating to Framework 8. As a reference, Gridv7 is the Grid component from Framework 7, while Grid is the latest Grid component in Framework 8. Naturally, you can also migrate straight from Table to the Framework 8 style Grid.

 

In the previous post, we saw the migration of a basic Table to Grid for Framework 7, then to Framework 8. Moving forward, I will skip all similar code related to containers and populating data, and will only focus on the highlighted APIs for the examples we have.

 

In this part, I will cover two examples about the selection modes, and make some comparisons on how Grid deals with data, as well as some corner cases that got modified.

Single Selection

Migrate from Table to Framework 7 Grid

Grid by default has single selection, so we don’t need to explicitly write this:

table.setSelectable(true);

But at any point, if you want to move or revert back to default selection mode, you can use:

table.setSelectionMode(SelectionMode.SINGLE);
 

Grid is immediate by default, so we don’t need this line anymore:

table.setImmediate(true);
 

To keep track of a selected item, instead of:

table.addValueChangeListener( … )

We use:

table.addSelectionListener( … )
 

And the selection event does not contain information about the selected item, instead of:

event.getProperty().getValue().toString();

We get it from the table directly:

table.getSelectedRow();
 

Related commit: Single Select: To Grid v7

 

The event.getProperty().getValue() was always returning a raw Object, instead of a class of the type of the container’s data, and it was required to cast it. But losing information about the selected item all together was not an optimal solution either in the early phase of Grid, and this got fixed as we will see in Framework 8.

Migrate to Framework 8 Grid

No big difference from the previous code, except that the new Grid deals with data a bit differently, so we need to differentiate between SingleSelect and MultiSelect, this code:

if (table.getSelectedRow() != null)

Should be replaced with:

if (table.asSingleSelect().getValue() != null)
 

And to get the selected bean, you can replace this code:

table.getSelectedRow()

With:

table.asSingleSelect().getValue()
 

And here comes an important note: table.getSelectedRow() returns the ID of the selected row, which was a number because it is using an IndexedContainer. Most often developers were actually using BeanItemContainer, where the ID was the actual bean presented on the selected row. Now, in Framework 8, it is always the bean.

 

One more thing we notice, if we compare the final result here, is the order of the columns, as well as the case of the first letter in the column name.

You can adjust the order using setColumnOrder:

table.setColumnOrder("name", "city", "year");

And you can modify the captions manually using setCaption:

table.getColumn("name").setCaption("name");
 

In this demo I will skip all those corner cases, they are explained here just for elaboration.

 

Related commit: Single Select: To Grid v8

Multi Selection

Migrate from Table to Framework 7 Grid

Similar to what we have done in Single Selection, here is the only code difference to get multi selection enabled, instead of:

table.setMultiSelect(true);

Use:

table.setSelectionMode(Gridv7.SelectionMode.MULTI);
 

The result is different though, multi selection in Grid is done through checkboxes instead of holding ctrl/cmd while selecting other items. There is a feature request for it, however it is not highly demanded by many developers. If you want this feature to be implemented, I encourage you to show interest by giving it a +1 on Github, otherwise it might never be implemented due to low need by the community, and probably due to bad UX.

 

Related commit: Multi Select: To Grid v7

Migrate to Framework 8 Grid

In Framework 8, Grid multi selection would be defined as the following:

table.setSelectionMode(Grid.SelectionMode.MULTI);
 

And retrieving the set of selected beans is done by using:

table.asMultiSelect().getSelectedItems())
 

Related commit: Multi Select: To Grid v8

 

Do you think migration from Table to Grid is complicated or doable?

Mission RIP Table: Migrate to Grid! - Basic

As promised earlier, here is the first and most basic example. As a reference, Gridv7 is the Grid component from Framework 7, while Grid is the latest Grid component in Framework 8. Naturally, you can also migrate straight from Table to the Framework 8 style Grid.

Migrate from Table to Framework 7 Grid

Before starting, we need to remember that the fundamental difference between Table and Grid is that Grid is a pure Component, while Table is a mixture of a Component and a Container at the same time. So whenever we have a Table without an external declaration of Container, we must create one first to be able to use it with Grid:

IndexedContainer container = new IndexedContainer();

 

The declaration of Table and Grid is pretty similar, so replacing:

Table table = new Table("The Brightest Stars");

With:

Gridv7 table = new Gridv7("The Brightest Stars", container);

 

And here I defined the container in the constructor. We can do that in a separate line as well using setContainerDataSource. The important step now is to replace all Container-related calls that were directly using Table, to use container, for example replace:

table.addContainerProperty("Name", String.class, null);

With:

container.addContainerProperty("Name", String.class, null);

 

There is no addItem() API anymore in the Containers. You can not do something like this:

table.addItem(new Object[]{"Canopus", -0.72f}, 2);

But instead:

itemId = container.addItem(2);
itemId.getItemProperty("Name").setValue("Canopus");
itemId.getItemProperty("Mag").setValue(-0.72f);

 

While this sounds like a drawback at first, the main reason behind this architecture was to get rid of manual filling of data components and make them all based on Containers. Basically you should not fill any data components one by one using addItem, that’s why it has disappeared completely in Framework 8.

 

The setPageLength API specifies the height of the Table. Grid’s height can be specified using different methods, that’s why this line:

table.setPageLength(table.size());

Needs to be replaced with two lines:

table.setHeightMode(HeightMode.ROW);
table.setHeightByRows(container.size());
 

Before you feel like you have done everything, there is one small thing missing if you want an exact migration from Table to Grid. You must disable the selection because that was the case by default in Table:

table.setSelectionMode(Gridv7.SelectionMode.NONE);

But in future examples I will ignore this extra line.

 

Related commit: Basic: To Grid v7 first method

 

A perfect migration would involve architecture modernization as well, that’s why I prefer not to stop at this point, and change the way how the data is populated inside this basic Grid. This will also help in future migration to Framework 8.

 

So I define a simple hard coded data and populate it inside the container:

Object[][] data = {
  {"Sirius", -1.46f},
  {"Canopus", -0.72f},
  {"Arcturus", -0.04f},
  {"Alpha Centauri", -0.01f}
};

IndexedContainer container
    = new IndexedContainer(Arrays.asList(data));
 

And this will make it easy to iterate over them while giving values:

for(Object[] item: data) {
  container.getItem(item).getItemProperty("Name")
      .setValue(item[0]);
  container.getItem(item).getItemProperty("Mag")
      .setValue(item[1]);
}
 

This replaces the manual hard coded definition of items. Note that we don’t need to define items anymore, we just set the values.

 

Related commit: Basic: To Grid v7 second method

Migrate to Framework 8 Grid

Grid in Framework 8 is way much simpler, we don’t use Containers nor Property anymore. Grid is parameterized with the type of data you want to display. So instead of:

IndexedContainer container
    = new IndexedContainer(Arrays.asList(data));
Gridv7 table = new Gridv7("The Brightest Stars", container);

We set the data directly into Grid:

Grid<object> table = new Grid<>("The Brightest Stars");
table.setItems(data);
 

We don’t need to define properties manually anymore, nor define items one by one, we can use some lambda magic:

table.addColumn(v -> v[0]).setCaption("Name");
table.addColumn(v -> v[1]).setCaption("Mag");
 

The first part table.addColumn(v -> v[i]) defines how the columns are provided from the given data, and the second part setCaption("Name") is to manually give a name for the column, since we are not using a proper POJO that can help us automatically generate the column names. Later there will be some more examples about this, but now we can see how this first example is easily transformed into a very simple code with the power of Framework 8.

 

Related commit: Basic: To Grid v8

 

Does this give you a good kickstarter for your migration process? Let us know how you feel about it so far in a comment below!

— 3 Items per Page
Showing 1 - 3 of 6 results.