How to use Spring Data JDBC

How to use Spring Data JDBC

Introduction

Overview

I've been using MyBatis3 until now, but I was having trouble with version control of the automatically generated source code, so I was looking for another O / R mapper around Spring. So I found out about Spring Data JDBC in this document and wrote down the extension part that I often use.

environment

Java 11 Gradle 5.4.1 Spring Boot 2.3.1.RELEASE Spring Data JDBC 2.0.1.RELEASE

Features of Spring Data JDBC

Until now, Spring Data has published modules that support JPA, which is the most commonly used RDB persistence API for Java applications. The newly released Spring Data JDBC is released as a simpler and easier-to-understand module than JPA. The official documentation specifically lists the following:

1. Do not use lazy loading or caching to load entities. It issues SQL every time and all the fields of the entity are read.
2. Spring Data JDBC does not manage the life cycle of entity instances. If you save the entity, it will be saved in the database, and if you do not explicitly save the entity, the changes will not be reflected in the database. Even if the entity is rewritten by another thread, it is not detected.
3. Entity-table mapping uses a simple mapping method. If you don't follow the mapping method prepared in advance, you need to code the mapping yourself.

Unlike Spring Data JPA, there are not so many functions, so designing according to the method provided by Spring Data JDBC seems to be the key to utilizing Spring Data JDBC.

Introduction method

Project creation

Select the following in Spring Initializr and download the project.

It is okay to select different Project and Language, but some of the sources introduced this time will be replaced. The version of Spring Boot that can be selected changes depending on the time, but it is okay if you choose the default version.

The build.gradle should look like this:

plugins {
	id 'org.springframework.boot' version '2.2.2.RELEASE'
	id 'io.spring.dependency-management' version '1.0.8.RELEASE'
	id 'java'
}

group = 'com.example'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
	runtimeOnly 'com.h2database:h2'
	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}
}

test {
	useJUnitPlatform()
}

Set data source in Spring

Create ʻapplication.yml under src / main / resources` and set the data source as follows. This time I set H2Database to start in PostgreSQL mode.

application.yml

spring:
  datasource:
    driver-class-name: org.h2.Driver
    url: jdbc:h2:mem:;DB_CLOSE_ON_EXIT=TRUE;MODE=PostgreSQL
    username: sa
    password:

Create schema.sql

Create a SQL file schema.sql that describes the test DDL under src / main / resources.

schema.sql

create table member
(
	id varchar not null
		constraint member_pk
			primary key auto_increment,
	name varchar not null
);

Create entities and repositories

Don't forget to annotate the property that corresponds to the table's primary key with @ Id. Since the Id of the Member class when it is not persisted is Null, add the @Wither annotation as well.

Member.java

@RequiredArgsConstructor
@Getter
@EqualsAndHashCode(of = {"id"})
@ToString
public class Member {
	@Id
	@Wither
	private final String id;
	private final String name;
}

Create a repository that inherits from CrudRepository. Specify the type argument in the order of Entity type and Id type.

MemberRepository.java

public interface MemberRepository extends CrudRepository<Member, String> {
}

With this alone, the following method is defined in MemberCredentialRepository.

• Member save(Member entity)
• Iterable saveAll(Iterable entities)
• Optional findById(String id)
• boolean existsById(String id)
• Iterable findAll()
• Iterable findAllById(Iterable ids)
• long count()
• void deleteById(String id)
• delete(Member entity)
• deleteAll(Iterable entities)
• deleteAll()

It's similar to JPA. The INSERT statement and UPDATE statement will be executed by the save method, and the logic that determines which one to execute is as follows.

1. The column with the @Id annotation is Null.
2. Entity is the Persistable interface implementation class and the isNew () method is true.

This time I adopted pattern 1.

Check the operation

Prepare a test class to check the operation. I wonder if it's okay if I can register the data for the time being and check if the data is included.

MemberRepositoryTest.java

@DataJdbcTest
class MemberRepositoryTest {

	@Autowired
	private MemberRepository memberRepository;

	@Test
	void test() {
		String name = "Kuchita";
		Member save = memberRepository.save(new Member(null, name));
		assertNotNull(save.getId());
		Optional<Member> maybeMember = memberRepository.findById(save.getId());
		assertTrue(maybeMember.isPresent());
		maybeMember
			.ifPresent(member -> assertEquals(save.getName(), member.getName()));
	}
}

Tips: If Test does not use an embedded database

If you give @DataJdbcTest, the embedded database will start by default. This time I used h2, so it's okay, but if you want to connect to an external database server, add the following to ʻapplication.properties`.

application.properties

spring.test.database.replace=none

By adding like this, you can use your favorite database server at the time of testing.

Detailed usage

Repository

Basic usage

Spring Data JDBC realizes database access by creating an interface that inherits the Repository interface prepared in advance. A convenient interface that prepares basic methods according to the ID specified in the type argument and the type of entity is provided as standard.

Repository interface variations

The classes prepared in advance are as follows.

I think it's okay if you use it properly as follows.

• If you want to create a read-only repository, inherit Repository.
• If you want to create the original repository base class, inherit Repository.
• If you use all CRUD, inherit CrudRepository.
• Inherit PagingAndSortingRepository for paging and sorting.

** Spring Data JDBC 1.1.1.RELEASE as of ** PagingAndSortRepository did not work properly. stack overflow -PagingAndSortingRepository methods throw error when used with spring data jdbc-

Custom base repository

You may want to define an interface that is common to all projects, in addition to the standard interface. In that case, let's extend the interface. At this time, add the @NoRepositoryBean annotation to the class.

Below is the base interface of a repository that defines only the methods that load entities in the Crud repository.

ReadOnlyRepository.java

@NoRepositoryBean
public interface ReadOnlyRepository extends Repository<Member, String> {
    Iterable<Member> findAll();
    Optional<Member> findById(String id);
}

Custom query

For methods that are not provided as standard, add @Query annotation to the method and describe SQL in the argument of the annotation to define it. The parameters you want to pass to SQL can be specified with : argument name.

MemberRepository.java

public interface MemberRepository extends CrudRepository<Member, String> {
    @Query("SELECT * FROM member WHERE name = :name")
    List<Member> getMembersByNameEquals(String name);
}

entity

Basic definition

The basic implementation pattern is to define immutable objects or JavaBeans. I will describe it on the assumption that the ID is automatically generated.

Immutable object

An entity that defines an immutable field and a constructor that takes all fields as arguments.

To define multiple constructors with arguments, it is necessary to annotate the constructor used for instantiation in Spring Data JDBC with @PersistenceConstructor annotation. If you don't want to annotate Spring Data JDBC, you can define a factory method separately.

Annotate the column that becomes the identifier with @Id. Since id will be assigned the identifier issued in the database after saving the entity, define wither so that the id can be updated.

** * The reference showed two methods, either to use the full-argument constructor or to use wither, but since the former method did not work in the environment at hand, I will introduce the method using wither. I am. ** **

Member.java

@RequiredArgsConstructor
@Getter
@EqualsAndHashCode(of = {"id"})
@ToString
public class Member {
    @Id
    @With
    private final String id;
    private final String name;

    public static Member createInstance(String name) {
        return new Member(null, name);
    }
}

JavaBeans pattern

An entity that defines accessors for default constructors and fields.

Member.java

@Getter
@Setter
@EqualsAndHashCode(of = {"id"})
@ToString
public class Member {
    @Id
    private String id;
    private String name;

    public static Member createInstance(String name) {
        return new Member(null, name);
    }
}

Specifications for object creation

1. If a no-argument constructor is defined, create an instance with the no-argument constructor
2. If one constructor with arguments is defined other than 1, instantiate with the constructor with arguments
3. If there are multiple constructors with arguments, create an instance with the constructor with @PersistenceConstructor.

Specifications for field assignment

1. If a wither is defined for an immutable field, use wither to set the value for the field.
2. If you have a setter, use the setter to set the value in the field
3. If the field is mutable, set the value directly in the field

Specifications for entity lifecycle management

In Spring Data JDBC, entities are saved and updated using the save method. Whether you want to issue an INSERT statement or an UPDATE statement depends on whether the entity is already persisted or not yet persisted. There are two main decision logics in Spring Data JDBC:

1. Determine if the entity identifier is null.
2. If the entity implements Persistable # isNew, determine if the entity is persistent or not based on the return value of the method.

If the ID is not automatically generated on the database side, it is better to define the entity according to the second method.

About data type conversion

Supported types by default

• Basic type
• String
• Enum (numbers are converted based on ordinal, strings are converted based on name)
• Date, LocalDate, LocalTime, LocalDateTime
• Other types supported by the JDBC driver
• Array of the above types and List
• Entity or Set of Entity
• Map with entity as Value

Spring Data JDBC has limited support for hasOne and hasMany relationships. Keep entities and their sets in fields only if there is a relationship between the root entity and the entities in its aggregate in Domain Driven Design.

If you define hasOne and hasMany relationships in a disorderly manner, NULL and Empty will be defined not by "whether or not the data actually exists" but by "whether or not it is JOINed by SQL". Even if you're not using Spring Data JDBC, this can cause serious bugs and loss of productivity.

Customize type conversion

Type conversion can be customized using Converter or ConverterFactory. Apply the created Converter and ConverterFactory in the configuration class that inherits AbstractJdbcConfiguration. You can apply your own conversion class by overriding the jdbcCustomConversions method.

JdbcConfiguration.java

@Configuration
public class JdbcConfiguration extends AbstractJdbcConfiguration {
	@Override
	public JdbcCustomConversions jdbcCustomConversions() {
		return new JdbcCustomConversions(List.of(
			// Converter/Register the bean of ConverterFactory
		));
	}
}

When the conversion destination is a specific class

Implement Conveter if the destination is a specific single class, such as converting an Enum to a String.

Give @ReadingConverter or @WritingConveter to the defined Conveter. If it is a Converter used when reading from the database, add @ReadingConverter, and if it is a Converter used when writing to the database, add @WritingConverter.

EnumToStringConverter.java

@WritingConverter
public enum EnumToStringConverter implements Converter<Enum, String> {
	INSTANCE;
	@Override
	public String convert(Enum e) {
		return e.name();
	}
}

When the conversion destination is an interface implementation or a subclass of a specific class

If the conversion destination is an interface implementation or a subclass of a specific class, such as converting a String to an Enum, implement ConverterFactory.

Since the formal argument of ConverterFactory # getConverter is the conversion destination class information, it is an advantage that the conversion destination class information can be handled in the process of creating an instance of Converter.

Annotations are the same as Converter.

StringToEnumFactory.java

@ReadingConverter
public enum StringToEnumFactory implements ConverterFactory<String, Enum> {
	INSTANCE;
	@Override
	public <T extends Enum> Converter<String, T> getConverter(Class<T> aClass) {
		return new StringToEnum<T>(aClass);
	}

	@RequiredArgsConstructor
	private static class StringToEnum<T extends Enum> implements Converter<String, T> {
		private final Class<T> enumType;

		@Override
		public T convert(String s) {
			return s == null ? null : Enum.valueOf(enumType, s);
		}
	}
}

Map multiple columns to nested objects

You can use the @Embedded annotation to map user-defined value objects and columns.

The entity Member with the value object ʻAddress` in the field can be mapped to a table column by defining it as follows:

Address.java

@RequiredArgsConstructor
@Getter
@EqualsAndHashCode
public class Address {
	private final String postcode;
	private final String prefecture;
	private final String addressLine;
}

Member.java

@Getter
@EqualsAndHashCode(of = {"id"})
@ToString
public class Member {
    @Id
    @With
    private final String id;
    private final String name;
    @Embedded(prefix = "address_", onEmpty = Embedded.OnEmpty.USE_NULL)
    private final Address address;

    public static Member createInstance(String name, Address address) {
        return new Member(null, name, address);
    }
}

As in the example, the @Embedded annotation must have two arguments, prefix and ʻonEmpty`.

prefix

Each field in the value object is mapped to a column using "prefix" and "field name in the value object". Here, the field-column mapping of ʻAddress` is resolved as follows.

onEmpty

Specifies what value to set in the entity's field if the field corresponding to the value object is NULL.

Basically, I think it's safe to use USE_NULL.

at the end

This is the end of the first edition, and I would like to organize the knowledge while operating it in the future.

Reference URL

Spring Data JDBC Official Reference stack overflow -PagingAndSortingRepository methods throw error when used with spring data jdbc- Convert Enum to non-ordinal number with Spring Data JDBC