DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Running a Java App With MySQL in Any Docker Environment
  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Keep Your Application Secrets Secret
  • Distributed Tracing System (Spring Cloud Sleuth + OpenZipkin)

Trending

  • Secure by Design: Modernizing Authentication With Centralized Access and Adaptive Signals
  • Event-Driven Architectures: Designing Scalable and Resilient Cloud Solutions
  • IoT and Cybersecurity: Addressing Data Privacy and Security Challenges
  • A Modern Stack for Building Scalable Systems
  1. DZone
  2. Data Engineering
  3. Databases
  4. Building a Web UI for MySQL Databases in Plain Java

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.

By 
Alejandro Duarte user avatar
Alejandro Duarte
DZone Core CORE ·
Updated Apr. 13, 17 · Tutorial
Likes (37)
Comment
Save
Tweet
Share
41.7K Views

Join the DZone community and get the full member experience.

Join For Free

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:

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

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:

@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. 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.

Database connection Spring Framework MySQL Java (programming language) Spring Boot application

Opinions expressed by DZone contributors are their own.

Related

  • Running a Java App With MySQL in Any Docker Environment
  • Java EE 6 Pet Catalog with GlassFish and MySQL
  • Keep Your Application Secrets Secret
  • Distributed Tracing System (Spring Cloud Sleuth + OpenZipkin)

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!