How to use Spring Boot with MySQL database

Greetings!

Spring Framework simplifies working with databases by auto configuring connections, handling transactions, using ORM tool like hibernate, abstract sql by Spring Data Repository. We are going to focus on how to connect MySQL database with Spring Boot application.

Sprint Boot has many defaults. For databases, H2 in-memory database is the default database. It auto-configures in-memory databases even without connection url. Those are good for simple testing. For production use we need to use a database like MySQL.

Spring Boot selects HickariCP Datasource due to it is performance. When spring-boot-starter-data-jpa dependency in classpath it automatically pick HickariCP.

complete source code this blog post is here.

How to configure a database

Obviously, to use a database in our application we need;

Database driver to connect to database
Connection url
Database username and password

In Spring Boot application we need to provide atleast connection url, otherwise it will try to configure in-memory database. Using connection url it can deduce the database driver to be used. So we do not need to configure database driver.

To configure above properties, Spring externalize configuration properties using spring.datasource.*.

spring.datasource.url = jdbc:mysql://localhost/test
spring.datasource.username = dbuser
spring.datasource.password = dbpassword
spring.datasource.driver-class-name = com.mysql.jdbc.Driver // no need

If we need more fine tuning we can use other configuration properties like spring.datasource.hikari.*.

How to auto-create a database

If we like to let the application create the database for us, we can use spring.jpa.hibernate.ddl-auto property. This value is none for MySQL and create-drop for embedded databases.

spring.jpa.hibernate.ddl-auto = create

Additionally if schema.sql (DDL) and data.sql (DML) files are in resouces folder Spring Boot can pick those and populate database. We can change default location by using schema and data properties.

spring.datasource.initialization-mode = always
spring.datasource.schema = classpath:/database/schema.sql # Schema (DDL) script resource references.
spring.datasource.data = classpath:/database/data.sql # Data (DML) script resource references.

Using above knowledge let's create a simple application which connects to MySQL database.

Create our database

I like to create database separately. Connect to MySQL database and create our database.

> mysql -uroot -proot
> create database book_store;
> use book_store;
// use schema.sql and data.sql to populate database

Create the project

Go to https://start.spring.io and select spring-boot-starter-data-jpa, spring-boot-starter-web, mysql-connector-java and lombok dependencies.
pom.xml will be look like this.

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

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

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

Configure the database

Create applicatin.yml and add below configuration properties.

spring:
  jpa:
    hibernate:
      ddl-auto: validate
  datasource:
    url: jdbc:mysql://localhost:3306/book_store
    username: root
    password: root

This is all we need to connect to MySQL database. Let's create our domain object and repository.

@Data
@Entity
public class Book implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String title;
    private String author;

}

public interface BookRepository extends JpaRepository<Book, Integer> {
}

Service layer

@Service
@Transactional
public class BookServiceImpl implements BookService {

    private final BookRepository bookRepository;

    @Autowired
    public BookServiceImpl(BookRepository bookRepository) {
        this.bookRepository = bookRepository;
    }

    @Override
    public List<Book> findAll() {
        return bookRepository.findAll();
    }

    @Override
    public Book findById(Integer id) {
        return bookRepository.findById(id).orElse(null);
    }

    @Override
    public Book save(Book book) {
        return bookRepository.save(book);
    }

    @Override
    public void delete(Integer id) {
        bookRepository.deleteById(id);
    }

    @Override
    public Book update(Book book) {
        return bookRepository.save(book);
    }

}

Rest controller layer

@RestController
public class BookController {

    private final BookService bookService;

    @Autowired
    public BookController(BookService bookService) {
        this.bookService = bookService;
    }

    @GetMapping(value = "")
    public List<Book> findAll() {
        return bookService.findAll();
    }

    @GetMapping(value = "/{id}")
    public Book findById(@PathVariable Integer id) {
        return bookService.findById(id);
    }

    @PostMapping
    public Book save(@RequestBody Book book) {
        return bookService.save(book);
    }

    @DeleteMapping(value = "/{id}")
    public void delete(@PathVariable Integer id) {
        bookService.delete(id);
    }

    @PutMapping
    public Book update(@RequestBody Book book) {
        return bookService.save(book);
    }

}

Now start the application and try below cURL commands.

curl -X GET http://localhost:7070/

curl -X POST \
  http://localhost:7070/ \
  -H 'Content-Type: application/json' \
  -d '{
    "title": "Java Persistence with Hibernate",
    "author": "Gavin King"
}'

curl -X GET http://localhost:7070/1

That is the basics you want know when working with relational database with Spring Boot. You can use below references for further study.

References

https://spring.io/guides/gs/accessing-data-mysql/
https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#boot-features-sql

org.springframework.boot.autoconfigure.orm.jpa.HibernateProperties

Manju