Spring Boot Connect to MySQL Database Examples

Details: Written by Nam Ha Minh; Last Updated on 31 December 2020 | Print Email

In this article, I’d like to share with you how to configure a Spring Boot application to connect to MySQL database server, in these two common scenarios:

A Spring Boot console application with Spring JDBC and JdbcTemplate
A Spring Boot web application with Spring Data JPA and Hibernate framework

Basically, in order to make a connection to a MySQL server, you need to do the following steps:

Declare a dependency for MySQL JDBC driver, which enables Java application to communicate with MySQL server.
Declare a dependency for Spring JDBC or Spring Data JPA
Specify data source properties for the database connection information
In case of Spring JDBC, use JdbcTemplate APIs for executing SQL statements against the database
In case of Spring Data JPA, you need to create an entity class, a repository interface and then use the Spring Data JPA API.

Below are the details for connecting to MySQL server in a Spring Boot application.

1. Declare dependency for MySQL JDBC Driver

To use MySQL JDBC driver, declare the following dependency in the Maven pom.xml file of your Spring Boot project:

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

You don’t need to specify the version as Spring Boot uses the default version specified in the parent POM.

2. Specify Data Source Properties

Next, you need to specify the following properties in the Spring Boot application configuration file (application.properties):

spring.datasource.url=jdbc:mysql://localhost:3306/bookshop
spring.datasource.username=root
spring.datasource.password=password

Update the data source URL, username and password according to your MySQL configuration. If you connect to a remote MySQL server, you need to replace localhost by IP address or hostname of the remote host.

3. Connect to MySQL with Spring JDBC

Spring JDBC provides a simple API on top of JDBC (JdbcTemplate), which you can use in simple cases, e.g. executing plain SQL statements. You need to declare the following dependency:

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

The following example program illustrates how to use JdbcTemplate to execute a SQL Insert statement:

package net.codejava;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class MySqlConnectionApplication implements CommandLineRunner {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	public static void main(String[] args) {
		SpringApplication.run(MySqlConnectionApplication.class, args);
	}

	@Override
	public void run(String... args) throws Exception {
		String sql = "INSERT INTO users (fullname, email, password) VALUES (?, ?, ?)";
		
		int result = jdbcTemplate.update(sql, "Ravi Kumar", "ravi.kumar@gmail.com", "ravi2021");
		
		if (result > 0) {
			System.out.println("A new row has been inserted.");
		}
		
	}

}

This is example code of a Spring Boot console program that connects to a MySQL server and insert a new row into the users table. As you can see, Spring JDBC handles connection to the database automatically so you can focus on your business code.

To learn more about using Spring JdbcTemplate, check this article.

4. Connect to MySQL with Spring Data JPA

Spring Data JPA provides more advanced API that greatly simplifies database programming based on Java Persistence API (JPA) specification with Hibernate as the implementation framework.

You need to declare dependency for Spring Data JPA as follows:

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

For data source properties, before the URL, username and password you can also specify these additional properties:

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57InnoDBDialect

And then, you need to code an entity class that maps to a table in the database, for example:

import javax.persistence.*;

@Entity
@Table(name = "users")
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Integer id;
	
	private String email;
	
	private String password;
	
	// getters and setters...
}

And declare a corresponding repository interface:

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {

}

And then you can use the repository in a Spring MVC controller or business class like this:

@Controller
public class UserController {
	@Autowired
	private UserRepository repo;
	
	@GetMapping("/users")
	public String listAll(Model model) {
		List<User> listUsers = repo.findAll();
		model.addAttribute("listUsers", listUsers);
		return "users";
	}
	
}

To learn more about Spring Data JPA, I recommend you to follow this article: Understand Spring Data JPA with Simple Example

That’s how to connect to MySQL database server in a Spring Boot application. You can use Spring JDBC for simple cases and Spring Data JPA for more advanced usage.

To see the coding in action, I recommend you to watch the following video:

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.