How to Connect a MySQL Database to a Vaadin Application (Part 1)

This tutorial focuses on how to wire your web application to a MySQL database so you can Create, Read, Update, and Delete (CRUD) information in a database via a Vaadin web application. The idea is to show you how to connect a template project downloaded from https://start.vaadin.com to a database so you can replicate it to your own needs. This is not about best practices (for Vaadin, Spring, or MySQL) but about helping you get started fast.

You can find the code used in this tutorial from GitHub.

1. Get Started With start.vaadin.com

Start by downloading a project from https://start.vaadin.com. For this example, get the default project with the MasterDetail view. The default project also has a DashBoard view. Feel free to add new views as you want, but make sure the MasterDetail view is there. Download the project with the name and Java Group ID you want.

In this example, the project name is databasemysqlexample, and the Java Group ID is com.example.mysql. Use the same if you want to copy-paste code or entire classes, but it's also a good exercise to try to figure things out by yourself. For the Technology Stack, select Spring Boot. It is required in this tutorial.

You might also want to read:  Connecting Spring Boot With MySQL and Oracle Databases

2. Open the Downloaded Project in Your Favorite IDE

Download, unzip, and open the project in your favorite Integrated Development Environment (IDE). If you have trouble with IDE or setting up the environment, check out the quick start guide at vaadin.com. It has also a list of Vaadin prerequisites.

There are also more detailed tutorials for the three major Java IDs:

• IntelliJ IDEA
• Eclipse
• NetBeans

At this point, you probably want to get your brand-new Vaadin project online. Once the project is open in the selected IDE you can start it. A Vaadin application with Spring Boot can be run with the mvn spring-boot:run command or as a standard Java application. If you have Maven installed just move to the project root directory and type mvn spring-boot:run.

The tutorials listed above have extensive instructions on how to do this in each of the IDEs (check under title Running Maven goals).

NOTE: Before checking these tutorials, keep in mind that in the tutorials the Maven goal is jetty:run, but since we are using Spring Boot the Maven goal is spring-boot:run, NOT jetty:run. Follow the instructions but replace jetty:run with spring-boot:run and you should be set for success.

When you run the application for the first time, prepare to wait some time: Vaadin uses the npm package system. The downside is that in the first run you have to download quite a lot of code from the internet (from npm and Maven repositories). If you get bored during the download, you can have a look at why Vaadin uses npm from here and here.

Now we assume that everything is downloaded and the local web server has started with your application. The console should show text lines like this:

xxxxxxxxxx

INFO 8152 --- [ restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8080 (http) with context path

INFO 8152 --- [ restartedMain] c.e.m.application.spring.Application : Started Application in 25.931 seconds (JVM running for 26.81) 

This means that the Tomcat web server is up and running and serving your application at http://localhost:8080. Open a web browser and go to this address. You should see your application. Have a look, surf around, click some lists items and buttons and see what happens.

3. Database Manager and Setting up the Database

To wire MySQL to your application you need a MySQL database running locally or running online in the cloud. MySQL is an Oracle product. MariaDB is a free and open-source drop-in replacement for MySQL. You can also get MySQL from Google or running as an add-on in Heroku just to mention a couple. If you need help with setting up a database server, have a look at, for example, here.

Before going further you should have a MySQL database server up and running (or corresponding database such as MariaDB). If you are running the database locally, you need a database manager such as HeidiSQL or Oracle MySQL Workbench. Using the command line for database management is just as fine.

4. Connecting Vaadin Application to the Database

Let's assume everything is in order and we can continue wiring the database to our Vaadin application. First you must enter the database address and credentials (username/password) for the database. Don’t use root as a user. This tutorial is not about the best practices but using root user for database access is just not right and shouldn't be done ever. If you decide to ignore this warning and to do it anyways you can skip the steps where we create a new user and grant privileges to that user. The root user already has enough privileges to do anything.

In your IDE, select the application.properties file and open it. Add the MySQL server address and the credentials to the application.properties file:

xxxxxxxxxx

spring.datasource.url = jdbc:mysql://localhost:3306/

spring.datasource.username =

spring.datasource.password = 

In this example, we use dummydata as a placeholder for everything: database, username and password. For the spring.datasource.url property, we use localhost with port 3306. This is the default port for MySQL (and MariaDB) but be aware that it can be something else. This is the case especially if you use cloud-based MySQL instances. The ending /dummydata in the address refers to the database name we are going to create.

xxxxxxxxxx

spring.datasource.url = jdbc:mysql://localhost:3306/dummydata

spring.datasource.username = dummydata

spring.datasource.password = dummydata

Next, we need to make sure that the project contains the spring-boot-starter-jdbc and mysql-connector-java dependencies. Here is where Maven kicks in. Add those dependencies to your pom.xml file under the element tag <dependencies> . Once added, you might need to update the project. This can be done in Eclipse by right-clicking the project, selecting Maven | Update project…. Select your project and click ok. Wait for Maven to download the dependencies and you are good to go. Usually, this is done when you run the application but it does not hurt to do things upfront.

Add the following dependencies to the pom.xml file:

org.springframework.boot spring-boot-starter-jdbc 
mysql mysql-connector-java runtime  

xxxxxxxxxx

<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>        

<dependency>

    <groupId>mysql</groupId>

    <artifactId>mysql-connector-java</artifactId>

    <scope>runtime</scope>

</dependency>

5. Preparing the Database

If you already have the database, user with privileges, table with columns, and the data, you can skip any or all of the following steps. After this tutorial when you start creating your own new views with data, you only need a table with columns and grant privileges to the user. There is a way to grant all rights to the user but I’m not going to show how since it is a bit dangerous if you ever go live with the application. Do it at your own risk.

You need to be logged in the database manager of your choice (command line or GUI) with a user with rights to create new users, databases, and tables.

Log in to the database manager as admin or root. In the database manager, create a new database:

xxxxxxxxxx

CREATE DATABASE dummydata; 

In the database manager, create a new user with a password:

xxxxxxxxxx

CREATE USER 'dummydata'@'localhost' IDENTIFIED BY 'dummydata'; 

In the database manager, grant the dummyuser user all rights to the dummydata database:

xxxxxxxxxx

GRANT ALL PRIVILEGES ON dummydata.* TO 'dummydata'@'localhost'; 

In the database manager, create a table that contains the fields for the Masterdata view in the application. Masterdata uses the Employee class that can be found from the Employee.java file.

xxxxxxxxxx

USE dummydata; 

CREATE TABLE IF NOT EXISTS employees (firstname VARCHAR(1000), lastname VARCHAR(1000), title VARCHAR(1000), email VARCHAR(1000), notes VARCHAR(1000) ); 

In the database manager, insert example employee data:

xxxxxxxxxx

USE dummydata; 

INSERT INTO employees VALUES ('Dev', 'Eloper', 'developer', 'dev.eloper@dontsendthis.org', 'Backend developer'); 

INSERT INTO employees VALUES ('Front', 'Ender', 'developer', 'front.ender@dontsendthis.org', 'Frontend developer'); 

INSERT INTO employees VALUES ('Wo', 'Manager', 'manager', 'wo.manager@dontsendthis.org', 'PO and team leader'); 

The steps below are optional and are required only to check that things work.

• In the database manager, logout from admin or root user.
• In the database manager, login as a dummydata user.
• In the database manager, select everything from the table employees in the dummydata database:

xxxxxxxxxx

SELECT * FROM dummydata.employees 

If you see a list of employees inserted in the step above, you are ready with the database.

6. Wiring the Database to the Application

So far, we have been doing mostly configurations. Now we can start coding. First, start the application with the instructions given above (if you need to refresh your memory, look at step 2: "Open the Downloaded Project in Your Favorite IDE" in this tutorial).

NOTE: You can use the command mvn spring-boot:run or run as Application (mainclass: com.example.mysql.application.spring.Application in this example).

If you got a console error like this:

APPLICATION FAILED TO START

Description:

The Tomcat connector configured to listen on port 8080 failed to start. The port may already be in use or the connector may be misconfigured.

Action:

Verify the connector's configuration, identify, and stop any process that's listening on port 8080, or configure this application to listen on another port.

Don’t worry, all is not lost; that just means that some application is already using the port 8080. You might have started the application and not remembered to terminate it. So, you need to find the Console tab that runs your application and terminate that one.

In Eclipse, look for a red box — an icon that is hinted Terminate. There is also a button with a hint Remove all terminated Launches that deletes Console tabs that are not running anything. After pressing that one, you can find your Console tab that actually runs the application.

When the application starts, use a browser and open http://localhost:8080/masterdetail. You should see a list of employees and their information details. At this moment, the list is still populated from code, not from the database.

Follow the steps to connect the view (more specifically the Grid component) to the database:

Open the MasterDetalView.java file in the IDE:

Locate the afterNavigation method. In this method, the Grid (called employees) gets populated by a list of employees. The list of employees is currently fetched from the BackendService service object.

xxxxxxxxxx

@Override 

public void afterNavigation(AfterNavigationEvent event) {

    employees.setItems(service.getEmployees());

} 

Create a file called EmployeeService.java into the same package as BackendService.java file. We will also be using the already existing class Employee  (from Employee.java file).

Do the following steps:

1. Add the annonation @Component before EmployeeService class declaration.

xxxxxxxxxx

@Component 

public class EmployeeService { 

2. Add the jdbcTemplate variable to the class.

3. Add the @Autowired annotation before the variable declaration.

xxxxxxxxxx

@Autowired 

private JdbcTemplate jdbcTemplate; 

4. Add a function to get all employees from database:

xxxxxxxxxx

public List findAll() { 

  try { 

    return jdbcTemplate.query("SELECT firstname, lastname, email, title FROM

        employees", (rs, rowNum) -> new Employee(rs.getString("firstname"),

        rs.getString("lastname"), rs.getString("email"),

        rs.getString("title"))); 

  } catch (Exception e) { 

    return new ArrayList(); 

  } 

}

When the function findAll  is called, it uses the jdbcTemplate variable to execute a SELECT query. The query then returns the rows found and for each of them, and it creates an instance of the Employee class. Those employees are added to a list and returned to the calling party. Most of the work is done by JdbcTemplate. You just need to make sure that the Employee class has a constructor matching the info fetch with a query and read using the rs.get<> methods.

NOTE: Rowmapper (doing the employees list for you) has methods like getString, getDouble, getInt, and getDate that reflect the data received from the database. For example, if you have a datetime field in the database, you're probably better off using the getDate() method instead of getString().

Now, go back to the MasterDetalView.java file in IDE: Add a variable for the newly created EmployeeService class. You can have a look at how the BackendService implementation is done. Don't forget the @Autowired annotation.

xxxxxxxxxx

@Autowired 

private EmployeeService employeeService; 

At this point, you better check that the Application is still running and works fine (as before we started to add the code). Check the instructions given earlier in this tutorial if you need help. If everything still works like before, we can continue in the IDE with the MasterDetalView.java file. Now we change the source for the list that populates the Grid element in the MasterDetail view.

Go to the afterNavigation method (that we already had a look at) in MaterDetailView.java file. Replace the call to service.getEmployees() method with a call to the newly created EmployeeService instances employeeService.findAll() method. You can see the original line in the comments below.

xxxxxxxxxx

//employees.setItems(service.getEmployees());

employees.setItems(employeeService.findAll()); 

Now the employees grid should be populated with the data from the database.

Refresh the browser and check that this is the case. The longer list should now be replaced with just the three employees we added to the database. Select a row from the list. Form element next to the list should be populated automatically with the selected employee info. Now the employees list is connected to the database instead of the hard coded list of employees. You can verify that by going to the database manager and adding some more lines with the INSERT statement you already used. Those new lines should appear to the page after you refresh the window.

NOTE: If the list did not change, there are a couple of things you can do to try to fix it. Go back to the IDE and make sure you have saved all the modified files. Make sure (from the console view) that the server has restarted (it might take some time). If the application still shows old employees, stop the server and restart the application.

Thanks for reading Part 1! In Part 2, we will learn how to save employee data and more. Stay tuned!

Further Reading

MySQL Database SELECT Query Operation in Mule 4

Using MySQL JDBC Driver With Spring Boot