Spring Boot Access MySQL Database with Spring Data JPA

Tags: Spring Boot MySQL Spring Data JPA Data

Introduction

In this tutorial we walk you through step by step to create a Spring Boot application to access MySQL database using Spring Data JPA and MySQL Driver. Via step by step example we will create a Spring Boot project to save Customer entity into customer data table in MySQL database.

Create New Spring Boot Project

From Spring Tool Suite IDE select menu File > New > Spring Starter Project.

On the New Spring Starter Project popup input new project spring-boot-mysql information as following screenshot.

Spring Boot Access MySQL Database with Spring Data JPA - Create New Project

On the New Spring Starter Project Dependencies popup choose dependencies:

  • Spring Data JPA
  • MySQL Driver

Spring Boot Access MySQL Database with Spring Data JPA - Create New Project

Implement Entity and Repository

Create a new Java package named dev.simplesolution.entity and implement Customer class.

package dev.simplesolution.entity;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Customer {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String firstName;
	private String lastName;
	private String email;
	private String phone;
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getFirstName() {
		return firstName;
	}
	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}
	public String getLastName() {
		return lastName;
	}
	public void setLastName(String lastName) {
		this.lastName = lastName;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	@Override
	public String toString() {
		return "Customer [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", email=" + email + ", phone=" + phone + "]";
	}
}

Create a new Java package named dev.simplesolution.repository and define CustomerRepository interface.

package dev.simplesolution.repository;

import org.springframework.data.repository.CrudRepository;
import dev.simplesolution.entity.Customer;

public interface CustomerRepository extends CrudRepository<Customer, Long> {

}

Create Datatable on MySQL Server

Execute the SQL script below to create a new table named customer in your MySQL database.

CREATE TABLE `customer` (
  `id` INT NOT NULL,
  `first_name` VARCHAR(100) NULL,
  `last_name` VARCHAR(100) NULL,
  `email` VARCHAR(100) NULL,
  `phone` VARCHAR(20) NULL,
  PRIMARY KEY (`id`));

Following screenshot is the result we get after creating the new table. For example, we have created it in the db_test database.

MySQL data table

Configure MySQL Connection

In order to connect to the database from the Spring Boot application we need to configure connection information.

Add the following configs to properties file at \src\main\resources\application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/db_test
spring.datasource.username=root
spring.datasource.password=123@abc
  • spring.datasource.url to configure the URL to your database. For example we have the database named db_test at localhost server which is running on default MySQL port 3306.
  • spring.datasource.username to configure the user name to access the MySQL database.
  • spring.datasource.password to configure password of your MySQL user.

Create Read Update and Delete data from MySQL

Next step, create a new TestMySQLData class as the following code that tries to write and read data from MySQL server.

The new class implements CommandLineRunner interface to indicate that it will be executed when Spring Boot application is started.

package dev.simplesolution;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

import dev.simplesolution.entity.Customer;
import dev.simplesolution.repository.CustomerRepository;

@Component
public class TestMySQLData implements CommandLineRunner {

	private Logger logger = LoggerFactory.getLogger(TestMySQLData.class);
	
	@Autowired
	private CustomerRepository customerRepository;
	
	@Override
	public void run(String... args) throws Exception {
		// create new customer 
		Customer newCustomer = new Customer();
		newCustomer.setFirstName("John");
		newCustomer.setLastName("Doe");
		newCustomer.setEmail("john@simplesolution.dev");
		newCustomer.setPhone("123-456-789");
		customerRepository.save(newCustomer);
		
		List<Customer> customers = new ArrayList<>(); 
		customerRepository.findAll().forEach(customers::add);
		for(Customer customer: customers) {
			logger.info("Customer: " + customer.toString());
		}
	}
}

You also can Update and Delete data by using customerRepository.save() and customerRepository.delete() method.

Final Application

The final Spring Boot application structure looks like the following screenshot.

MySQL data table

Run the Spring Boot application and we can see the following logs in the console.

2020-10-20 00:54:09.166  INFO 20168 --- [           main] dev.simplesolution.TestMySQLData         : Customer: Customer [id=7, firstName=John, lastName=Doe, email=john@simplesolution.dev, phone=123-456-789]

Select data from MySQL database.

MySQL data table

Conclusion

In this tutorial we have implemented the basic Spring Boot console application that uses the Spring Data JPA and MySQL Driver dependency to access MySQL database.

Download Source Code

The source code in this article can be found at: github.com/simplesolutiondev/spring-boot-mysql

or clone at:

git clone https://github.com/simplesolutiondev/spring-boot-mysql.git

or download at:

Download Source Code

Happy Coding 😊