This is a Spring Boot tutorial to use Spring Data JPA to access the database. In this post, we will introduce how to access data in MySQL by Spring Data JPA.

It is easy to get started with using Spring Data JPA to operate data in MySQL, but there are still a lot of things involved. Even you are newer to learn Spring Boot, it’s also easy to learn that.

Starting the project

Firstly, you can create a new Spring Boot project by Spring Initializr or IDE. (Read: Spring Boot Getting Started Tutorial)

This JPA demo project needs the Spring Web Starter, Spring Data JPA, Test, Lombok, and MySQL Driver dependencies. The pom.xml like this:

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.2.5.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.etbye</groupId>
	<artifactId>demo</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>demo</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>13</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</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>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
			<exclusions>
				<exclusion>
					<groupId>org.junit.vintage</groupId>
					<artifactId>junit-vintage-engine</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

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

</project>

Configure database connection and JPA configuration

Then, go to configure database connection information and JPA configuration.

The content of application.properties:

spring.datasource.url=jdbc:mysql://localhost:3306/springboot_jpa?useSSL=false
spring.datasource.username=root
spring.datasource.password=password
#print the sql sentence
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.open-in-view=false
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL55Dialect

About spring.jpa.hibernate.ddl-auto=createthis configuration option, there are four common options for this attributor:

  • create: Every time you restart the project, the table structure will be re-innovated, which will result in data loss.
  • create-drop: Create the table structure every time you start the project, delete the table structure when you close the project
  • update: The table structure updated every time when the project is started
  • validate: Verify the table structure without making any changes to the database

Create a database in MySQL

mysql> create database springboot_jpa;

Entity Class

Now, we need to create an Entity class,  it is a persistent class.

package com.etbye.entity;

import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Data
@NoArgsConstructor
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @Column(unique = true)
    private String name;
    private Integer age;

    public Person(String name, Integer age) {
        this.name = name;
        this.age = age;
    }
}

By now, you can run the project and view the output to verify that you completed the above steps correctly.

The SQL statement printed by the console looks like this:

Hibernate: drop table if exists person
Hibernate: create table person (id bigint not null auto_increment, age integer, name varchar(255), primary key (id)) engine=InnoDB
Hibernate: alter table person add constraint UK_p0wr4vfyr2lyifm8avi67mqw5 unique (name)

When you see the output info like that, then you can go the following steps.

Create a Repository interface

We need to Create a Repository interface for the operational database. Usually, the new repository extends the JpaRepository interface. JpaRepository contains some database operations.

You can see the detail of the JpaRepository interface:

package org.springframework.data.jpa.repository;

import java.util.List;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.Sort;
import org.springframework.data.repository.NoRepositoryBean;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.QueryByExampleExecutor;

@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
    List<T> findAll();

    List<T> findAll(Sort var1);

    List<T> findAllById(Iterable<ID> var1);

    <S extends T> List<S> saveAll(Iterable<S> var1);

    void flush();

    <S extends T> S saveAndFlush(S var1);

    void deleteInBatch(Iterable<T> var1);

    void deleteAllInBatch();

    T getOne(ID var1);

    <S extends T> List<S> findAll(Example<S> var1);

    <S extends T> List<S> findAll(Example<S> var1, Sort var2);
}

Here, we create a PersonRepository interface that extends from JpaRepository.

package com.etbye.repository;

import com.etbye.entity.Person;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.List;
import java.util.Optional;

@Repository
public interface PersonRepository extends JpaRepository<Person, Long> {

    //find Person by name
    Optional<Person> findByName(String name);
    //find Person by age range
    List<Person> findByAgeGreaterThan(int age);

    //Custom SQL qeruy
    @Query("select p.name from Person p where p.id = :id ")
    String findPersonNameById(@Param("id") Long id);

    //Custom SQL qeruy
    @Query("select p from Person p where p.name = :name")
    Optional<Person> findByNameCustomQuery(@Param("name") String name);

    //Custom SQL qeruy
    @Modifying
    @Transactional
    @Query("update Person p set p.name = ?1 where p.id = ?2")
    void updatePersonNameById(String name, Long id);
}

Test class

We have added some query methods in our PersonRepository interface. Now, we will go to test its wether works fine.

DemoApplicationTests.java

package com.etbye;

import com.etbye.entity.Person;
import com.etbye.repository.PersonRepository;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;
import java.util.Optional;

import static org.junit.jupiter.api.Assertions.*;

@SpringBootTest
@RunWith(SpringRunner.class)
public class DemoApplicationTests {


    @Autowired
    private PersonRepository repository;
    private Long id;

    /**
     * save Person data to database
     */
    @Before
    public void setUp() {
        assertNotNull(repository);
        Person person = new Person("Jannies", 25);
        Person savedPerson = repository.saveAndFlush(person);
        repository.save(savedPerson);
        id = savedPerson.getId();
    }

    /**
     * to find Person by JPA
     */
    @Test
    public void should_get_person() {
        Optional<Person> person = repository.findById(id);
        assertTrue(person.isPresent());
        assertEquals("Jannies", person.get().getName());
        assertEquals(Integer.valueOf(25), person.get().getAge());

        List<Person> personList = repository.findByAgeGreaterThan(18);
        assertEquals(1, personList.size());

        repository.deleteAll();
    }

    /**
     * find Person by custom SQL queries
     */
    @Test
    public void should_get_person_use_custom_query() {
        Optional<Person> person = repository.findByName("Jannies");
        assertTrue(person.isPresent());
        assertEquals(Integer.valueOf(25), person.get().getAge());

        String name = repository.findPersonNameById(id);
        assertEquals("Jannies", name);
        System.out.println(id);

        repository.updatePersonNameById("UpdatedName", id);
        Optional<Person> updatedPerson = repository.findByNameCustomQuery("UpdatedName");
        assertTrue(updatedPerson.isPresent());

        repository.deleteAll();
    }

}

Run the tester class, if all tests work fine, meaning the custom query methods working fine.

Spring Data JPA to access the MySQL

Good luck!