{{announcement.body}}
{{announcement.title}}

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

DZone 's Guide to

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

In this article, see part two of how to connect a MySQL database to a Vaadin application.

· Integration Zone ·
Free Resource

Two skyscrapers connected by a diagonal piece of metal

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 to help you get started fast.

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

In Part 1, we looked at getting started with Vaadin, how to open the downloaded project in your favorite IDE, and more.

7. Saving Employee Data

Next, we will create a code to insert and update employees. In this example, they both work from the same Save button. To know if the press is an update or insert, we will check if the given email address is already in the database we are going to update. If the email address is new, then it is an insert.

NOTE: You might think should there be a save and insert button, or is the email address the correct key to use to determine a new employee? You probably have a better solution already, and that is very good news for you. You can fix it to your liking later on.

You might also want to read: Mule 4: Database Connector (Part 1)

We start by creating a method to save the employee to database. Add saveEmployee method to EmployeeService class. It should take employee as an argument, and it returns an int value (the number of employees updated/inserted in database).

Java




xxxxxxxxxx
1


 
1
public int saveEmployee(Employee employee) { 
2
  List employees = this.findByEmail(employee.getEmail()); 
3
  if ( employees.size() > 0 ) { 
4
    return updateEmployee(employee); 
5
  } else { 
6
    return insertEmployee(employee); 
7
  } 
8
} 



You might notice that the method saveEmployee is using new methods called findByEmail, updateEmployee and insertEmployee. You need to create those methods in the EmployeeService class. The findByEmail method is used to check if that email is already in use in the database. It returns a list of employees with that email address. It should always be a 0 or 1, but if you have already entered the same email address many times to the database, it can be more than 1. In that case, all the rows with that email are going to get updated at the same time. If the email address is already in the database (list size is more than 0), we will call the updateEmployee method. Otherwise, we will insert a new employee by calling insertEmployee method.

Java




xxxxxxxxxx
1
10


 
1
public List findByEmail(String email) { 
2
  try { 
3
    return jdbcTemplate.query("SELECT firstname, lastname, email, title FROM
4
        employees WHERE email = ?", new Object[]{email}, (rs, rowNum) -> new
5
        Employee(rs.getString("firstname"), rs.getString("lastname"),
6
        rs.getString("email"), rs.getString("title"))); 
7
  } catch (Exception e) { 
8
    return new ArrayList(); 
9
  } 
10
} 


Java




xxxxxxxxxx
1


 
1
private int insertEmployee(Employee employee) { 
2
  try { 
3
    return jdbcTemplate.update("INSERT INTO employees VALUES (?, ?, ?, ?, ?)",
4
        employee.getFirstname(), employee.getLastname(), employee.getTitle(),
5
        employee.getEmail(), ""); 
6
  } catch (Exception e) { 
7
    return 0; 
8
  } 
9
} 


Java




xxxxxxxxxx
1


 
1
private int updateEmployee(Employee employee) { 
2
  try { 
3
    return jdbcTemplate.update("UPDATE employees SET lastname = ?, firstname = ?
4
        WHERE email = ?", employee.getLastname(), employee.getFirstname(),
5
        employee.getEmail()); 
6
  } catch (Exception e) { 
7
    return 0; 
8
  } 
9
} 



Now that we have set up the EmployeeService class, we need to connect the user interface (the Save button) to work. Open the MasterDetailView.java file (with MasterDetailView class). In the constructor of that class, you can find a line of code that is run when the Save button is pressed. In the example, it is something like this:

Java




xxxxxxxxxx
1


 
1
save.addClickListener(e -> { Notification.show("Not implemented"); }); 



This code means that when the Save button is clicked, it runs the code Notification.show("Not implemented");. We are going to replace showing of the notification with actually saving (or inserting) the employees information to the database. Like this:

Java




xxxxxxxxxx
1


 
1
save.addClickListener( 
2
  e -> { Employee employee = binder.getBean(); 
3
        if ( employeeService.saveEmployee(employee) > 0) {
4
            employees.setItems(employeeService.findAll()); 
5
        } else { 
6
          Notification.show("Save error"); 
7
        } 
8
    }
9
); 



This block of code uses the binder to get the employees information that is currently shown in the form with employee information (not the list, but the fields on the right side). Then it calls the saveEmployee method of the EmployeeService class to save the employee. If the saveEmployee method returns a value that is more than 0 we update the list of employees with setItem command. Otherwise, a notification with a text Save error is shown to the user.

There is one more step you need to do: find the populateForm method in the MasterDetailView class. Check that it exists and, if yes, change the following line of code:

Java




xxxxxxxxxx
1


 
1
binder.readBean(value); 



to

Java




xxxxxxxxxx
1


 
1
binder.setBean(value); 



This is an important step, and without the binder being set using the setBean method, we are not able to get values from the binder with binder.getBean() command when pressing the Save button.

Now we should all be clear to test the application. Remember to save every file in the IDE and run the application. When the application is started, go to the MasterDetail page, select an employee from the list, change the first and the last name of that employee, and press the Save button. If everything went well, the input fields should be cleared and the name of that employee should change while the email remains the same.

Now take another line from the list and change the names and the email address (but use a completely new email address that is not already in the list). Then press the Save button. A new line should appear in the list. Now have a play with the list, select a line, and change the email address to something that is already on the list but for another person.

What happens when you change someone else's email address to match that one? You should see that the logic is not there for the user. Try to figure out why those things happen and how you can prevent that. Also, selecting an employee from the list and then pressing the Clear button empties the information of that employee from the list. It does not delete the employee or save the cleared data, but it's not quite the outcome the end user would expect.

8. Adding Employee Information

You might have noticed already that if you press the Clear button and then try to enter completely new employee information, this does not work. The same thing happens when reloading the page and you start inserting employee info without first selecting an employee. Why is that?

So, Vaadin helps you out with using the binders. You bind the employee information input fields to that particular employee. You can return the information from all the fields with a simple binder.getBean command instead of having to ask the value from each and every field separately. The application binds the employee to input fields when you select it from the list. This is done in the constructor of the MasterDetailView class with the following line:

Java




xxxxxxxxxx
1


 
1
employees.asSingleSelect().addValueChangeListener(
2
  event -> populateForm(event.getValue())
3
); 



It means that when a single line from the list is selected we call the populateForm method. If you want to know what happens in more detail, have a look at the populateForm method again. From there, you can find the added binder.setBean(value); line of code. This binds the selected employee information to the fields.

First, we will handle the problem that when you refresh the page (without selecting an employee from the list), fill the employee information to the empty fields, and press save, nothing happens. The root cause of this is that the input fields are not bound by the binder to any employee. When you press the Save button and the application runs the code Employee employee = binder.getBean(); in the click listener of the Save button. Because the binder is not set with the setBean command, it returns a null. Null is then passed to employeeService.saveEmployee command as a parameter. That null is then used in the saveEmployee method with employee.getEmail(), but since null does not have a method getEmail, it throws a null exception and the code execution is stopped. You can check this from the console of your IDE or wherever you log out of the error messages from the application.

We can handle the nonexistent binding in many ways; one would be to check if the employee bean received from the binder is null and create a new employee bean that we pass along. This would require us to get all the information from the input fields. Now we have only a couple, but if there would be tens of input fields, this might be a lot of work.

Instead, we will set the binder with an empty employee bean straight from the start. This empty bean is then replaced by a new one if an employee is selected from the list. Find the constructor of the MasterDetailView class (we have discussed this earlier already). Find the line of code binder.bindInstanceFields(this); and add the following line of code after that:

Java




xxxxxxxxxx
1


 
1
binder.setBean(new Employee()); 



This line of code binds a newly created employee to the input fields. If you want to initialize the default employee with values, you can use the Employee class constructor that takes first name, last name, email address, and title as a parameter. Title has not been used during this tutorial at all, but in this case, you need to provide that also. As an exercise, you can add a new constructor to Employee class without the title. If you decide to use the constructor with initial values as names and email address after you refresh the browser, you can see the given values already in the input fields.

Now if you run the application, refresh the browser, enter last name, first name, email address, and press the Save button, an employee with given information is created. Of course, if you enter an already-existing email address, then it is not inserted but updated. But you already knew this from before.

Now we take a look at the second problem with inserting new employees. If you select an employee from the list and press save or clear, the input fields are cleared. If you now try to add new employee information and press the save button, you get the same null pointer exception. Why is that?

This is because when you hit the Clear button, it calls the code employees.asSingleSelect().clear(), which clears the selections from the employees list. When that happens, a line of code populateForm(event.getValue()) gets called. Since the selected value is cleared by the first command, populateForm method gets a parameter null (since nothing is selected). In the populateForm method, the code binder.setBean(value); sets the binder to the value of null. This is the same situation that we used to have when refreshing the page and starting to add an employee without first clicking a line in the list. Setting the binder to null also happens when you hit the Save button. The chain of commands is a bit longer, but the end result is the same as with the Clear button.

We can prevent this in many ways. Again, this tutorial is not about best practices but to show you how to get started fast. With this in mind, we are going to use the following solution; since both our problems (pressing the clear button and the save button) lead us to the populateForm method, we will place the fix there. When the method is run, we’ll check if the parameter Employee value is null. If it is not null, we continue as before. If it is null, then we will create an employee and bind that one. Basically, it's the same thing we did before in the constructor. Add the following code to the populateForm method:

Java




xxxxxxxxxx
1


 
1
if ( value == null ) { value = new Employee(); } 



Remember to add it before the code line:

Java




xxxxxxxxxx
1


 
1
binder.setBean(value); 



Now the binder should always have an employee bean bound to it and those null pointer exceptions should end. This means that you can insert new employees to empty fields. That problem with employee info getting updated if the email address is already in use is still there, but that is another story.

9. Removing an Employee

CRUD stands for Create, Read, Update, and Delete. We already have Create (insert), Read (select), and Update (update), but we are missing Delete. This requires us to place a new button in the user interface and create a couple of methods to delete something from the database.

First, we start with the needed methods. Since we have the saveEmployee method, we could also have the deleteEmployee method. Open the EmployeeService class and add the following method:

Java




xxxxxxxxxx
1


 
1
public int deleteEmployee(Employee employee) { 
2
  try { 
3
    return jdbcTemplate.update("DELETE FROM employees WHERE email = ?",
4
        employee.getEmail()); 
5
  } catch (Exception e) { 
6
    return 0; 
7
  } 
8
} 



This deletes from the database all the lines that have the same email address as the given employee. This means that if you have several employees with the same email address, they are deleted. Sounds dangerous, but as you remember, we agreed to use email as the key, and there should be only one employee with the same email address in the database.

Now we have to place the delete button in the user interface. The correct place for the delete button can cause discussion among developers, designers, and users. At this point, we just place the button to the UI (user interface) and do not discuss the placing in any depth.

The delete button should appear after the input fields on the right side of the screen; the same area where the Clean and Save buttons are. First, add the button declaration as a variable to the MasterDetailView class (the same way as save and clear buttons are done).

Java




xxxxxxxxxx
1


 
1
private Button delete = new Button("Delete"); 



Open the MasterDetailView class and locate the createButtonLayout method. We will using them to show the button that it is used to remove something. Add a line of code to the createButtonLayout:

Java




xxxxxxxxxx
1


 
1
delete.addThemeVariants(ButtonVariant.LUMO_ERROR);



The logical place for the code is before or after the cancel.addThemeVariants and save.addThemeVariants commands. Then add the button to the form by adding the button to the parameters of the buttonLayout.add command:

Java




xxxxxxxxxx
1


 
1
buttonLayout.add(delete, cancel, save);


The order of the buttons in the parameters determines the order of the buttons in the UI. Placing delete as the first parameter also renders it as the first button from the left. When you run the application, the Delete button should be shown next to the cancel and save buttons.

Pressing the delete button currently does nothing. We need to add an event listener to it. We also need to add the code that is run when the click event happens. Do this by locating the constructor of the MasterDetailView class. Add the following code into the constructor. The logical place is near where the save and cancel buttons event listeners are defined. You already made modifications to the save button event listener.

Java




xxxxxxxxxx
1


 
1
delete.addClickListener(
2
  e -> { Employee employee = binder.getBean(); 
3
        if ( employeeService.deleteEmployee(employee) > 0) {
4
          employees.setItems(employeeService.findAll()); 
5
        } else { 
6
          Notification.show("Delete error"); 
7
        } 
8
}); 



When the Delete button is clicked, we get the employee that is currently bound to the input fields and pass it as a parameter to deleteEmployee method. We already coded that method and know that it takes the email address of the employee and removes all employees with that email address from the database.

If you run the applications, you can select an employee from the list and remove it (from the database and following from the list) by pressing the delete button. If there is nothing selected or the given email address is not found, you should see a small notification (at the lower right corner of the screen) that says Delete error.

Now have a go with your application. Remove all the employees from the list, add a couple new ones, modify existing employees, and so on. Once you have removed all the employees, it should not be possible to add any more employees with a duplicate email address.

10. What to do Next?

Adding the CRUD operations to a Vaadin application allows you to continue further down the rabbit hole of application development. There are still a couple of things you can do here like figure out how to make sure the user won't accidentally try to add a new email address that is already in use or how to prevent the user from changing the email address of an existing employee. We also have title and password fields that are not in use. I’m not going to help you with those ideas here, but hopefully you now have an idea of how to continue.

Thank you for reading this short tutorial.

I warmly welcome you to the world of application development using Vaadin!

11. Troubles Ahead?

Sometimes, things just don't work as planned. If you don't see the list of employees in http://localhost:8080/masterdetail you should double-check everything.

If the list is empty, but everything works fine, there is probably something wrong with the database connection. We created a try-catch for the database operation, so you can either debug the code or remove the try-catch. In this case, having things in try-catch prevents the possible error from being shown to the user. Removing the try-catch shows the exception on the browser window. This is not the optimal solution, but if you are having trouble with debugging, you can go with this option.

If you have a problem with SQL query, you will probably see something like:

Java




xxxxxxxxxx
1


 
1
There was an exception while trying to navigate to 'masterdetail' with the exception message 'StatementCallback; bad SQL grammar [SELECT firstname, lastname, email, title FROM employeess]; nested exception is java.sql.SQLSyntaxErrorException: Table 'dummydata.employeess' doesn't exist' 



or

Java




xxxxxxxxxx
1


 
1
There was an exception while trying to navigate to 'masterdetail' with the exception message 'StatementCallback; bad SQL grammar [SELECT firstname, last_name, email, title FROM employees]; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'last_name' in 'field list'' 



The fix is to check the query text to match the table and column names you created in the beginning.

If there is a problem with application.properties definitions, you might get an exception:

Java




xxxxxxxxxx
1


 
1
There was an exception while trying to navigate to 'masterdetail' with the exception message 'Failed to obtain JDBC Connection; nested exception is java.sql.SQLSyntaxErrorException: Unknown database 'dummydata2'' 



The problem is a wrong database name (is dummydata2, should be dummydata). Check the name of the database you have created (or have already) and see if they match.

Another one could be:

Java




xxxxxxxxxx
1


 
1
There was an exception while trying to navigate to 'masterdetail' with the exception message 'Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Access denied for user 'dummydata2'@'localhost' (using password: YES)' 



This can be caused by a wrong username or password.
It can also be a case when the user does not have the correct privileges.

In all the cases, double-check the steps and your code.

Further Reading

MySQL Tutorial: A Beginners Guide To Learn MySQL

Tomcat Connection Pooling Using DBCP and MySQL Database [Video]

Topics:
integration ,mysql ,tutorial ,vaadin ,mysql database

Opinions expressed by DZone contributors are their own.

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

{{ parent.tldr }}

{{ parent.urlSource.name }}