Spring Boot MySQL tutorial

Spring Boot MySQL tutorial shows how to use MySQL database in a Spring Boot application.

Spring is a popular Java application framework for creating enterprise applications. Spring Boot is an evolution of Spring framework which helps create stand-alone, production-grade Spring based applications with minimal effort.

MySQL

MySQL is a leading open source database management system. It is a multi-user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform, which includes Linux, Apache, MySQL, and PHP. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, and Mac.

MySQL setup

We are going to show how to install MySQL database on a Debian Linux system.

$ sudo apt-get install mysql-server

This command installs MySQL server and related packages.

$ sudo service mysql start
$ sudo service mysql stop

These two commands are used to start and stop MySQL.

$ sudo service mysql status

We check the status of the database with service mysql status command.

$ mysql -u root -p

Now we need to reset the root password. We start the mysql command line tool. (The server must be running.) We connect as root.

mysql> SET PASSWORD = PASSWORD('newpassowrd');

We set a new password for root.

$ mysql_secure_installation

We can use mysql_secure_installation to increase security of MySQL server. We are given the choice to improve the MySQL root password, remove anonymous user accounts, disable root logins outside of localhost, and remove test databases.

mysql> CREATE DATABASE testdb;

We create a new testdb database.

mysql> CREATE USER user12@localhost IDENTIFIED BY 's$cret';
mysql> GRANT ALL ON testdb.* TO user12@localhost;

We create a new MySQL user and grant it privileges to the testdb database.

Creating MySQL table

Now we are going to create a new MySQL table called cities.

cities_mysql.sql
DROP TABLE IF EXISTS cities;
CREATE TABLE cities(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100), 
    population INT);

INSERT INTO cities(name, population) VALUES('Bratislava', 432000);
INSERT INTO cities(name, population) VALUES('Budapest', 1759000);
INSERT INTO cities(name, population) VALUES('Prague', 1280000);
INSERT INTO cities(name, population) VALUES('Warsaw', 1748000);
INSERT INTO cities(name, population) VALUES('Los Angeles', 3971000);
INSERT INTO cities(name, population) VALUES('New York', 8550000);
INSERT INTO cities(name, population) VALUES('Edinburgh', 464000);
INSERT INTO cities(name, population) VALUES('Berlin', 3671000);

This is SQL to create the cities table.

mysql> use testdb;
mysql> source cities_mysql.sql

With the source command, we execute the SQL statements.

Spring Boot MySQL example

The following application is a simple Spring Boot web application, which uses MySQL database. We have a home page with a link to display data from a database table. We use Freemarker templating system to join data with HTML.

$ tree
.
├── pom.xml
└── src
    └── main
        ├── java
        │   └── com
        │       └── zetcode
        │           ├── Application.java
        │           ├── bean
        │           │   └── City.java
        │           ├── controller
        │           │   └── MyController.java
        │           ├── repository
        │           │   └── CityRepository.java
        │           └── service
        │               ├── CityService.java
        │               └── ICityService.java
        └── resources
            ├── application.properties
            ├── static
            │   └── index.html
            └── templates
                └── showCities.ftl

This is the project structure.

pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
http://maven.apache.org/xsd/maven-4.0.0.xsd">
    
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.zetcode</groupId>
    <artifactId>SpringBootMySQL</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.9.RELEASE</version>
    </parent>      
    
    <dependencies>
        
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
       
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-freemarker</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>           

    </dependencies>       
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>            
        </plugins>
    </build>         
    
</project>

Spring Boot starters are a set of convenient dependency descriptors which greatly simplify Maven configuration. The spring-boot-starter-parent has some common configurations for a Spring Boot application. The spring-boot-starter-freemarker is a starter for building MVC web applications using Freemarker views. The spring-boot-starter-data-jpa is a starter for using Spring Data JPA with Hibernate.

The mysql-connector-java dependency is for the MySQL database driver.

The spring-boot-maven-plugin provides Spring Boot support in Maven, allowing us to package executable JAR or WAR archives. Its spring-boot:run goal runs the Spring Boot application.

application.properties
server.port=8086
server.contextPath=/myapp

spring.main.banner-mode=off
logging.level.org.springframework=ERROR

spring.datasource.url=jdbc:mysql://localhost:3306/testdb?useSSL=false
spring.datasource.username=user12
spring.datasource.password=s$cret

In the application.properties file we write various configuration settings of a Spring Boot application. The server.port sets for server port and the server.contextPath context path (application name). After these settings, we access the application at localhost:8086/myapp/. With the spring.main.banner-mode property we turn off the Spring banner.

In the spring datasource properties we set up the MySQL datasource.

City.java
package com.zetcode.bean;

import java.util.Objects;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "cities")
public class City {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private String name;
    private int population;

    public City() {
    }

    public City(Long id, String name, int population) {
        this.id = id;
        this.name = name;
        this.population = population;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPopulation() {
        return population;
    }

    public void setPopulation(int population) {
        this.population = population;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 79 * hash + Objects.hashCode(this.id);
        hash = 79 * hash + Objects.hashCode(this.name);
        hash = 79 * hash + this.population;
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final City other = (City) obj;
        if (this.population != other.population) {
            return false;
        }
        if (!Objects.equals(this.name, other.name)) {
            return false;
        }
        return Objects.equals(this.id, other.id);
    }
    
    @Override
    public String toString() {
        return "City{" + "id=" + id + ", name=" + name
                + ", population=" + population + '}';
    }
}

This is the City entity. Each entity must have at least two annotations defined: @Entity and @Id.

@Entity
@Table(name = "cities")
public class City {

The @Entity annotation specifies that the class is an entity and is mapped to a database table while the @Table annotation specifies the name of the database table to be used for mapping.

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

The primary key of an entity is specified with the @Id annotation. The @GeneratedValue gives a strategy for generating the values of primary keys.

CityRepository.java
package com.zetcode.repository;

import com.zetcode.bean.City;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface CityRepository extends CrudRepository<City, Long> {

}

By extending from the Spring CrudRepository, we will have some methods for our data repository implemented, including findAll(). This way we save a lot of boilerplate code.

ICityService.java
package com.zetcode.service;

import com.zetcode.bean.City;
import java.util.List;

public interface ICityService {

    public List<City> findAll();
}

ICityService provides the findAll() contract method declaration to get all cities from the data source.

CityService.java
package com.zetcode.service;

import com.zetcode.bean.City;
import com.zetcode.repository.CityRepository;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class CityService implements ICityService {

    @Autowired
    private CityRepository repository;

    @Override
    public List<City> findAll() {

        List<City> cities = (List<City>) repository.findAll();
        
        return cities;
    }
}

CityService contains the implementation of the findAll() method. We use the repository to retrieve data from the database.

@Autowired
private CityRepository repository;

CityRepository is injected.

List<City> cities = (List<City>) repository.findAll();

The findAll() method of the repository returns the list of cities.

MyController.java
package com.zetcode.controller;

import com.zetcode.bean.City;
import com.zetcode.service.ICityService;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

@Controller
public class MyController {
    
    @Autowired
    ICityService cityService;

    @GetMapping("/showCities")
    public String findCities(Model model) {
        
        List<City> cities = (List<City>) cityService.findAll();
        
        model.addAttribute("cities", cities);
        
        return "showCities";
    }
}

The MyController class is annotated with @Controller.

@Autowired
private ICityService cityService;

We inject an ICityService into the countryService field.

@GetMapping("/showCities")
public String findCities(Model model) {
    
    List<City> cities = (List<City>) cityService.findAll();
    
    model.addAttribute("cities", cities);
    
    return "showCities";
}

We map a request with the /showCities path to the controller's findCities() method. The @GetMapping annotation maps a GET request to the method. The model gains a list of cities and the processing is sent to the showCities.ftl Freemarker template file.

showCities.ftl
<!DOCTYPE html>
<html>
    <head>
        <title>Cities</title>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        </head>
    <body>
        <h2>List of cities</h2>
        
        <table>
            <tr>
                <th>Id</th>  
                <th>Name</th>  
                <th>Population</th>
            </tr>        

            <#list cities as city>
                <tr>
                    <td>${city.id}</td> 
                    <td>${city.name}</td> 
                    <td>${city.population}</td>
                </tr>
            </#list>        
        </table>                
    </body>
</html>

In the showCities.ftl template file, we display the data in an HTML table.

index.html
<!DOCTYPE html>
<html>
    <head>
        <title>Home page</title>
        <meta charset="UTF-8"/>
        <meta name="viewport" content="width=device-width, initial-scale=1.0"/>
    </head>
    <body>
        <a href="showCities">Show cities</a>
    </body>
</html>

In the index.html there is a link to show all cities.

Application.java
package com.zetcode;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

The Application sets up the Spring Boot application. The @SpringBootApplication enables auto-configuration and component scanning.

$ mvn spring-boot:run 

After the application is run, we can navigate to localhost:8086/myapp/.

In this tutorial, we have showed how to use MySQL database in a Spring Boot application. You might also be interested in the related tutorials: