[JAVA] I tried Spring Data JDBC 1.0.0.BUILD-SNAPSHOT (-> 1.0.0.RELEASE)

The first entry in 2018 is ... Spring Data JDBC that I thought "Hey" when I saw @ sndr's "Spring Data JDBC Preview" It is a memo when I tried it. It still seems to be just simple CRUD level support, but with the official release of Spring Data JDBC (https://github.com/spring-projects/spring-data-jdbc), Spring Data REST (https) We are (very) looking forward to being supported at: //projects.spring.io/spring-data-rest/)! ↓ ** Since it was officially released on September 21, 2018, the content has been modified based on 1.0.0.RELEASE! ** **

Verification version

• Spring Data JDBC 1.0.0.RELEASE
• Spring Boot 2.0.5.RELEASE
• MyBatis Spring Boot Starter 1.3.2 (MyBatis 3.4.6 + MyBatis Spring 1.3.2)
• H2 Database 1.4.197

NOTE: Update history

2018-02-06 :

• Supports interface changes due to DATAJDBC-161 support
• Revalidated with Spring Boot 2.0.0.RC1

2018-02-08 :

• Added how to use the @ Query method (corresponding to DATAJDBC-172)

2018-03-08 :

• Added that simple type is supported as the return value of @Query method due to support for DATAJDBC-175.
• Revalidated with Spring Boot 2.0.0.RELEASE

2018-03-09 :

• Added description about handling related objects
• In relation to the above, added that it is necessary to import Lovelace-BUILD-SNAPSHOT of spring-data-release train when using Spring Data JDBC on Spring Boot 2.0.0.RELEASE.

2018-03-10 :

• Added usage of update query (@Modifying) (corresponding to DATAJDBC-182)

2018-03-23 :

• Fixed not to use DefaultNamingStrategy due to support for DATAJDBC-189 (default implementation is defined as NamingStrategy.INSTANCE)
• Updated to MyBatis Spring Boot Starter 1.3.2

2018-03-31 :

• Fixed not to specify NamedParameterJdbcOperations when generating DefaultDataAccessStrategy due to support for DATAJDBC-155
• Added NamingStrategy to support snake cases (underscore delimiters) with support for DATAJDBC-184.

2018-04-03 :

• With support for DATAJDBC-178, you can set any NamespaceStrategy instance in MyBatisDataAccessStrategy to create a namespace naming convention. Added that it can be changed

2018-05-18 :

• Added description about support for annotation-based Auditing feature (corresponding to DATAJDBC-204)
• Update to Spring Boot 2.0.2.RELEASE and revalidate

2018-05-19 :

• Reflect package configuration changes (corresponding to DATAJDBC-138)

2018-06-28 :

• Reflect package configuration and class name changes (corresponding to DATAJDBC-226)
• Added that the default implementation of NamingStrategy has been changed to Snake Case with support for DATAJDBC-207.
• Updated to Spring Boot 2.0.3.RELEASE and revalidated

2018-07-03 :

• Removed the explicit import of spring-data-releasetrain Lovelace-BUILD-SNAPSHOT and added <spring-data-releasetrain.version> Lovelace-BUILD-SNAPSHOT </ spring-data-releasetrain.version> to the properties. Fixed to add

2018-07-20 :

• Reflected changes in how custom converters are applied (corresponding to DATAJDBC-235)
• Fixed an error in MyBatis when deleteAll. It seems that the SQL ID that deletes the Entity has changed! ??

2018-07-28 :

• Reflected changes in how to apply JdbcConfiguration (corresponding to DATAJDBC-243)

2018-09-22 :

• Fixed Spring Data JDBC validation version to 1.0.0.RELEASE
• Fixed Spring Boot verification version to 2.0.5.RELEASE
• Describes spring-boot-starter-data-jdbc

2018-09-23 :

• Corrected the description of how to specify / extend JdbcConfiguration (corrected due to the influence of DATAJDBC-267)

Demo project

The sources described in this entry are published in the following repositories. (Because Spring JDBC and MyBatis are mixed and verified, there are some differences from the description in the entry)

• https://github.com/kazuki43zoo/spring-data-jdbc-demo

Creating a development project

First, in SPRING INITIALIZR, select "H2", "JDBC", and "MyBatis (only when using MyBatis)" for Dependencies and create a project. (This entry is based on the Maven project premise) Next, add "spring-data-jdbc" to the project created by SPRING INITIALIZR. When using Spring Data JDBC in Spring Boot 2.0 series, it is necessary to specify the Lovelace-RELEASE version of spring-data-releasetrain in the property provided by Spring Boot as follows.

pom.xml

<properties>
	<spring-data-releasetrain.version>Lovelace-RELEASE</spring-data-releasetrain.version>
</properties>

pom.xml

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

DB setup

Prepare DDL to create a table.

src/main/resources/schema.sql

CREATE TABLE IF NOT EXISTS todo (
	id IDENTITY
	,title TEXT NOT NULL
	,details TEXT
	,finished BOOLEAN NOT NULL
);

Creating a domain object

Create a Todo object that represents the records in the TODO table. Add @Id to the property that holds the key value.

src/main/java/com/example/demo/domain/Todo.java

package com.example.demo.domain;

import org.springframework.data.annotation.Id;

public class Todo {
	@Id
	private int id;
	private String title;
	private String details;
	private boolean finished;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getDetails() {
		return details;
	}

	public void setDetails(String details) {
		this.details = details;
	}

	public boolean isFinished() {
		return finished;
	}

	public void setFinished(boolean finished) {
		this.finished = finished;
	}

}

Creating Repository

Create Repository interface for manipulating domain objects. The point is to inherit the Croud Repository provided by Spring Data.

src/main/java/com/example/demo/repository/TodoRepository.java

package com.example.demo.repository;

import org.springframework.data.repository.CrudRepository;

import com.example.demo.domain.Todo;

public interface TodoRepository extends CrudRepository<Todo, Integer> {

}

By doing this ... You can operate the Todo object using the following methods defined in CrudRepository.

Reference: Excerpt from Crud Repository

package org.springframework.data.repository;

import java.util.Optional;

@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {
	<S extends T> S save(S entity);
	<S extends T> Iterable<S> saveAll(Iterable<S> entities);
	Optional<T> findById(ID id);
	boolean existsById(ID id);
	Iterable<T> findAll();
	Iterable<T> findAllById(Iterable<ID> ids);
	long count();
	void deleteById(ID id);
	void delete(T entity);
	void deleteAll(Iterable<? extends T> entities);
	void deleteAll();
}

How to execute SQL

Spring Data JDBC provides DataAccessStrategy as an interface to abstract the method of executing SQL, and at the moment," Spring JDBC ( NamedParameterJdbcOperations) implementation" and "MyBatis implementation" are built-in.

Using Spring JDBC implementation

If you use Spring JDBC implementation, SQL to be executed when you call the method defined in CrudRepository is automatically generated (= you do not need to write SQL for CRUD operation).

Bean definition example

Create a configuration class with @EnableJdbcRepositories and@Import (JdbcConfiguration.class). However ... If you want to change the bean defined in JdbcConfiguration, you need to create a configuration class that inherits JdbcConfiguration and register it as a DI container. For example, if you need type conversions that are not supported by default, you can override the jdbcCustomConversions method and return JdbcCustomConversions with any Converter specified. In this entry, Converter is added to convert TEXT type (Clob) of H2 Database to String. By the way ... If you use VARCHAR instead of TEXT, you don't need to override the jdbcCustomConversions method.

@EnableJdbcRepositories
@Configuration
public class SpringDataJdbcConfig extends JdbcConfiguration {

	@Override
	protected JdbcCustomConversions jdbcCustomConversions() { 
		return new JdbcCustomConversions(Collections.singletonList(new Converter<Clob, String>() {
			@Override
			public String convert(Clob clob) {
				try {
					return clob == null ? null : clob.getSubString(1L, (int) clob.length());
				} catch (SQLException e) {
					throw new IllegalStateException(e);
				}
			}
		}));
	}

}

Note:

NamingStrategy is provided as an interface for determining the naming strategy for column names and property names. By default, NamingStrategy.INSTANCE is used, but you can change the default behavior by defining a Bean of NamingStrategy. ~~ In addition, NamingStrategy that supports snake cases (underscore delimiters) has been added by supporting DATAJDBC-184 (when using it) Bean definition required). ~~ The default behavior is treated as a snake case (underscore delimiter) (the default behavior has been changed to support DATAJDBC-206).

Using MyBatis implementation

When using MyBatis implementation, it is necessary to define the SQL to be executed when the method defined in CrudRepository is called on MyBatis side. (= You also need to write SQL for CRUD operations).

Bean definition example

Create a configuration class with @EnableJdbcRepositories and@Import (JdbcConfiguration.class), and define a Bean of MyBatisDataAccessStrategy (MyBatis implementation) as DataAccessStrategy.

@EnableJdbcRepositories
@Import(JdbcConfiguration.class)
@Configuration
public class SpringDataJdbcConfig {
	@Bean
	DataAccessStrategy dataAccessStrategy(SqlSession sqlSession) {
		return new MyBatisDataAccessStrategy(sqlSession);
	}
}

NOTE:

2018-02-06: With support for DATAJDBC-161, pass the object to the constructor argument of MyBatisDataAccessStrategy from SqlSessionFactory to SqlSession (Actually changed to SqlSessionTemplate).

MyBatis setting example

Set the location and type alias of the Mapper XML file.

src/main/resources/application.properties

mybatis.mapper-locations=classpath:/com/example/demo/mapper/*Mapper.xml
mybatis.type-aliases-package=com.example.demo.domain

SQL definition example

Define SQL corresponding to the method of CrudRepository. When using MyBatis via Spring Data JDBC, it is necessary to be aware of some special rules when defining SQL.

• The corresponding SQL definition namespace should be "FQCN of domain object +" Mapper ""
• Parameters are passed wrapped in a class called MyBatisContext (excerpt of MyBatisContext is posted separately)
• There is no one-to-one correspondence between the CrudRepository method and the SQL definition (unlike the correspondence rule of MyBatis's Mapper interface). For specific support, refer to [README] of Spring Data JDBC (https://github.com/spring-projects/spring-data-jdbc#mybatis).

NOTE:

2018-04-03: With support for DATAJDBC-178, name by setting any NamespaceStrategy instance in MyBatisDataAccessStrategy You can change the naming convention for spaces.

src/main/resources/com/example/demo/mapper/TodoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.TodoMapper">

	<!-- statements for CrudRepository method -->
	<insert id="insert" useGeneratedKeys="true" keyProperty="instance.id">
		INSERT INTO todo 
			(title, details, finished)
		VALUES 
			(#{instance.title}, #{instance.details}, #{instance.finished})
	</insert>
	<update id="update">
		UPDATE todo SET
			title = #{instance.title}, details = #{instance.details}, finished = #{instance.finished}
		WHERE
			id = #{instance.id}
	</update>
	<delete id="delete">
		DELETE FROM todo WHERE id = #{id}
	</delete>
	<delete id="deleteAll">
		DELETE FROM todo
	</delete>
	<select id="existsById" resultType="_boolean">
		SELECT count(*) FROM todo WHERE id = #{id}
	</select>
	<select id="findById" resultType="Todo">
		SELECT
			id, title, details, finished 
		FROM
			todo
		WHERE
			id = #{id}
	</select>
	<select id="findAll" resultType="Todo">
		SELECT
			id, title, details, finished
		FROM
			todo
		ORDER BY
			id
	</select>
	<select id="findAllById" resultType="Todo">
		SELECT
			id, title, details, finished
		FROM
			todo
		<where>
			<foreach collection="id" item="idValue" open="id in("
				separator="," close=")">
				#{idValue}
			</foreach>
		</where>
		ORDER BY
			id
	</select>
	<select id="count" resultType="_long">
		SELECT count(*) FROM todo
	</select>

</mapper>

Reference: Excerpt from MyBatisContext

package org.springframework.data.jdbc.mybatis;

import java.util.Map;

public class MyBatisContext {

	private final Object id;
	private final Object instance;
	private final Class domainType;
	private final Map<String, Object> additonalValues;

	public MyBatisContext(Object id, Object instance, Class domainType, Map<String, Object> additonalValues) {
		this.id = id;
		this.instance = instance;
		this.domainType = domainType;
		this.additonalValues = additonalValues;
	}

	public Object getId() {
		return id;
	}

	public Object getInstance() {
		return instance;
	}

	public Class getDomainType() {
		return domainType;
	}

	public Object get(String key) {
		return additonalValues.get(key);
	}
}

Combined implementation

I will not explain it in this entry + I have not verified it, but it seems that it is possible to use multiple implementations (eg Spring JDBC and MyBatis) together using CascadingDataAccessStrategy.

Use of Repository

Spring Data JDBC Repository is injected and used like other Spring Data projects.

@Autowired
private TodoRepository todoRepository;

@Test
public void insertAndFineById() {
	Todo newTodo = new Todo();
	newTodo.setTitle("drinking party");
	newTodo.setDetails("Ginza 19:00");
	todoRepository.save(newTodo);

	Optional<Todo> todo = todoRepository.findById(newTodo.getId());
	Assertions.assertThat(todo.isPresent()).isTrue();
	Assertions.assertThat(todo.get().getId()).isEqualTo(newTodo.getId());
	Assertions.assertThat(todo.get().getTitle()).isEqualTo(newTodo.getTitle());
	Assertions.assertThat(todo.get().getDetails()).isEqualTo(newTodo.getDetails());
	Assertions.assertThat(todo.get().isFinished()).isFalse();
}

Addition of @Query method

Arbitrary queries can be executed (using Spring JDBC functions) by adding a method with @Query to the Repository.

~~WARNING:~~ ~~ With the current implementation, it is not possible to execute update SQL (it seems that there are plans to support it). ~~

NOTE: 2018-03-10: DATAJDBC-182 also supports the execution of update SQL.

src/main/java/com/example/demo/repository/TodoRepository.java

package com.example.demo.repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;
import java.util.stream.Stream;

import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;

import com.example.demo.domain.Todo;

public interface TodoRepository extends CrudRepository<Todo, Integer> {

	@Query("SELECT * FROM todo WHERE id = :id")
	Optional<Todo> findOptionalById(@Param("id") Integer id);

	@Query("SELECT * FROM todo WHERE id = :id")
	Todo findEntityById(@Param("id") Integer id);

	@Query("SELECT * FROM todo ORDER BY id")
	Stream<Todo> findAllStream();

	@Query("SELECT * FROM todo ORDER BY id")
	List<Todo> findAllList();

	@Query("SELECT count(*) FROM todo WHERE finished = :finished")
	long countByFinished(@Param("finished") Boolean finished);

	@Query("SELECT count(*) FROM todo WHERE finished = :finished")
	boolean existsByFinished(@Param("finished") Boolean finished);

	@Query("SELECT current_timestamp()")
	LocalDateTime currentDateTime();

	@Modifying
	@Query("UPDATE todo SET finished = :finished WHERE id = :id")
	boolean updateFinishedById(@Param("id") Integer id, @Param("finished") boolean finished);

}

NOTE:

@ Param can be omitted by specifying the Java compiler's -parameters option.

Currently, the supported return types are

• T (domain class)
• java.util.Optional<T>
• Types that can be assigned to java.lang.Iterable <T> (such as java.util.List <T>)
• java.util.stream.Stream<T>
• Simple type (primitive type, primitive wrapper type, etc.)

And ʻorg.springframework.data.domain.Page and ʻorg.springframework.data.domain.Slice <T>are not supported (if you want to treat these types as return values, "Add custom operation" described later is required).

In addition, what is supported as the return value of the update method is

• int(Integer)
• boolean(Boolean)
• void

Is.

~~WARNING:~~ ~~ In the current implementation, it is not possible to specify a type other than the domain class such as a number (ʻint, long, etc.) or boolean value as the return value (that is ... @ Query method derecord You can't specify SQL to get the number or SQL to check the existence of the record). It can be handled by the method introduced in "Adding custom operations" below, but ... I feel like I'm missing some consideration, so I'll give it an issue. ⇒ [DATAJDBC-175](https://jira.spring.io/browse/DATAJDBC-175) ~~ ↓ 2018-03-08: It is now possible to return types other than domain classes (so-called simple types) such as numeric values (ʻint, long, etc.) and boolean values as return values! !! Internally ... SingleColumnRowMapper ＋ Spring Data Type resolution is performed in cooperation with ConversionService applied to JDBC. By the way ... Spring Framework 5.0.4.RELEASE or higher is required for this support.

Add custom operations

"Mechanism for adding custom operations (custom methods)" in Spring Data This mechanism can also be used in Spring Data JDBC.

Creating a custom interface

Define an interface for defining custom operations (custom methods).

src/main/java/com/example/demo/repository/CustomizedTodoRepository.java

package com.example.demo.repository;

import com.example.demo.domain.Todo;

public interface CustomizedTodoRepository {

	Iterable<Todo> findAllByFinished(boolean finished);

}

Inherit the created interface with Todo Repository.

src/main/java/com/example/demo/repository/TodoRepository.java

package com.example.demo.repository;

import org.springframework.data.repository.CrudRepository;

import com.example.demo.domain.Todo;

public interface TodoRepository extends CrudRepository<Todo, Integer>, CustomizedTodoRepository {

}

Creating a Spring JDBC implementation

When using Spring JDBC, create the following implementation class.

src/main/java/com/example/demo/repository/CustomizedTodoRepositoryImpl.java

package com.example.demo.repository;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;

import com.example.demo.domain.Todo;

public class CustomizedTodoRepositoryImpl implements CustomizedTodoRepository {

	private static final RowMapper<Todo> ROW_MAPPER = new BeanPropertyRowMapper<>(Todo.class);

	private final NamedParameterJdbcOperations namedParameterJdbcOperations;

	public CustomizedTodoRepositorySpringJdbcImpl(NamedParameterJdbcOperations namedParameterJdbcOperations) {
		this.namedParameterJdbcOperations = namedParameterJdbcOperations;
	}

	public Iterable<Todo> findAllByFinished(boolean finished) {
		return this.namedParameterJdbcOperations.query(
				"SELECT id, title, details, finished FROM todo WHERE finished = :finished ORDER BY id",
				new MapSqlParameterSource("finished", finished), ROW_MAPPER);
	}

}

Creating a MyBatis implementation

When using MyBatis, create the following implementation class.

src/main/java/com/example/demo/repository/CustomizedTodoRepositoryImpl.java

package com.example.demo.repository;

import org.apache.ibatis.session.SqlSession;

import com.example.demo.domain.Todo;

public class CustomizedTodoRepositoryImpl implements CustomizedTodoRepository {

	private final String NAMESPACE = Todo.class.getName() + "Mapper";

	private final SqlSession sqlSession;

	public CustomizedTodoRepositoryMyBatisImpl(SqlSession sqlSession) {
		this.sqlSession = sqlSession;
	}

	public Iterable<Todo> findAllByFinished(boolean finished) {
		return this.sqlSession.selectList(NAMESPACE + ".findAllByFinished", finished);
	}

}

Also add the SQL definition.

src/main/resources/com/example/demo/mapper/TodoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.TodoMapper">

	<!-- ... -->

	<!-- statements for custom repository method -->
	<select id="findAllByFinished" resultType="Todo">
		SELECT
			id, title, details, finished
		FROM
			todo
		WHERE
			finished = #{finished}
		ORDER BY
			id
	</select>

</mapper>

Persistence operation for related objects (1: 1 or 1: N)

Spring Data JDBC supports persistence operations on related objects that have a 1: 1 or 1: N relationship. However, the support status differs between the Spring JDBC implementation and MyBatis implementation. If you take a quick look ... The support status for update operations is the same. However ... For the operation of the reference system when using MyBatis, implementation on the MyBatis side (table join + 1: 1/1: N mapping using association and collection) is required.

DB setup

Create a table to persist related objects.

src/main/resources/schema.sql

CREATE TABLE IF NOT EXISTS activity (
	id IDENTITY
	,todo INTEGER NOT NULL --Column that stores the ID of the domain object
	,todo_key INTEGER NOT NULL --Column that stores the identification key (and sort key) of related objects in the domain object
	,content TEXT NOT NULL
	,at TIMESTAMP NOT NULL
);

In the default implementation of Spring Data JDBC, the column name of "column that stores ID of domain object" is "class name of domain object", and the column name of "column that stores identification key of related object in domain object" is It becomes "column that stores ID of domain object +" _key "".

Creating related objects

Create a domain object that represents the activity of TODO and associate it with the Todo object.

src/main/java/com/example/demo/domain/Activity.java

package com.example.demo.domain;

import org.springframework.data.annotation.Id;

import java.time.LocalDateTime;

public class Activity {
	@Id
	private int id;
	private String content;
	private LocalDateTime at;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public LocalDateTime getAt() {
		return at;
	}

	public void setAt(LocalDateTime at) {
		this.at = at;
	}
}

src/main/java/com/example/demo/domain/Todo.java

public class Todo {
	// ...
	private List<Activity> activities;
	// ...
	public List<Activity> getActivities() {
		return activities;
	}
	public void setActivities(List<Activity> activities) {
		this.activities = activities;
	}
}

CRUD operation execution example

Here, the domain object that holds the related object with 1: N relationship is operated by using the method defined in CrudRepository.

CRUD operation execution example

@Test
public void oneToMany() {

	// Insert
	Todo newTodo = new Todo();
	newTodo.setTitle("drinking party");
	newTodo.setDetails("Ginza 19:00");
	Activity activity1 = new Activity();
	activity1.setContent("Created");
	activity1.setAt(LocalDateTime.now());

	Activity activity2 = new Activity();
	activity2.setContent("Started");
	activity2.setAt(LocalDateTime.now());

	newTodo.setActivities(Arrays.asList(activity1, activity2));

	todoRepository.save(newTodo);

	// Assert for inserting
	Optional<Todo> loadedTodo = todoRepository.findById(newTodo.getId());
	Assertions.assertThat(loadedTodo.isPresent()).isTrue();
	loadedTodo.ifPresent(todo -> {
		Assertions.assertThat(todo.getId()).isEqualTo(newTodo.getId());
		Assertions.assertThat(todo.getTitle()).isEqualTo(newTodo.getTitle());
		Assertions.assertThat(todo.getDetails()).isEqualTo(newTodo.getDetails());
		Assertions.assertThat(todo.isFinished()).isFalse();
		Assertions.assertThat(todo.getActivities()).hasSize(2);
		Assertions.assertThat(todo.getActivities().get(0).getContent()).isEqualTo(activity1.getContent());
		Assertions.assertThat(todo.getActivities().get(1).getContent()).isEqualTo(activity2.getContent());

	});

	// Update
	Activity activity3 = new Activity();
	activity3.setContent("Changed Title");
	activity3.setAt(LocalDateTime.now());
	loadedTodo.ifPresent(todo -> {
		todo.setTitle("[Change] " + todo.getTitle());
		todo.getActivities().add(activity3);
	});
	todoRepository.save(loadedTodo.get());

	// Assert for updating
	loadedTodo = todoRepository.findById(newTodo.getId());
	Assertions.assertThat(loadedTodo.isPresent()).isTrue();
	loadedTodo.ifPresent(todo -> {
		Assertions.assertThat(todo.getTitle()).isEqualTo("[Change] " + newTodo.getTitle());
		Assertions.assertThat(todo.getActivities()).hasSize(3);
		Assertions.assertThat(todo.getActivities().get(0).getContent()).isEqualTo(activity1.getContent());
		Assertions.assertThat(todo.getActivities().get(1).getContent()).isEqualTo(activity2.getContent());
		Assertions.assertThat(todo.getActivities().get(2).getContent()).isEqualTo(activity3.getContent());
	});

	// Delete
	todoRepository.deleteById(newTodo.getId());

	// Assert for deleting
	Assertions.assertThat(todoRepository.findById(newTodo.getId())).isNotPresent();

}

Using Spring JDBC implementation

If you use the Spring JDBC implementation, you don't need to do anything. If you simply call the CrudRepository method, Spring Data JDBC will generate and execute the SQL.

Using MyBatis implementation

If you use MyBatis implementation, you need to define SQL in Mapper XML file.

First ... Define SQL for inserting related objects.

src/main/resources/com/example/demo/mapper/ActivityMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.ActivityMapper">
	<insert id="insert" useGeneratedKeys="true" keyProperty="instance.id">
		INSERT INTO activity
			(todo, todo_key, content, at)
		VALUES
			(#{additonalValues.Todo}, #{additonalValues.Todo_key}, #{instance.content}, #{instance.at})
	</insert>
</mapper>

The point here is that the "domain object ID" and "identification key (and sort key) of the related object in the domain object" are stored in the Map type ʻadditonalValues` property. , The key name where the value is stored is the same rule as the column name.

Next ... Define SQL to delete related objects. This SQL is also called when updating a domain object. In other words ... All related objects with a 1: N relationship are DELETEd once and then INSERTed again.

src/main/resources/com/example/demo/mapper/TodoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.TodoMapper">
	<!-- ... -->
	<delete id="delete-activities">
		DELETE FROM activity WHERE todo = #{id}
	</delete>
	<delete id="deleteAll-activities">
		DELETE FROM activity WHERE todo = #{id}
	</delete>
	<!-- ... -->
</mapper>

Finally ... Modify so that the related object is also acquired in the SQL that refers to the domain object. Specifically ... Join the tables that hold the information of related objects, and map the related objects to domain objects using the ResultMap function of MyBatis.

src/main/resources/com/example/demo/mapper/TodoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.ActivityMapper">
	<!-- ... -->
	<select id="findById" resultMap="todoMap">
		SELECT
			t.id, t.title, t.details, t.finished
			, a.id as activity_id, a.content as activity_content, a.at as activity_at
		FROM
			todo t
		LEFT OUTER JOIN activity a ON a.todo = t.id
		WHERE
			t.id = #{id}
		ORDER BY
			a.todo_key
	</select>
	<resultMap id="todoMap" type="Todo">
		<id column="id" property="id"/>
		<result column="title" property="title"/>
		<result column="details" property="details"/>
		<result column="finished" property="finished"/>
		<collection property="activities" columnPrefix="activity_" ofType="Activity">
			<id column="id" property="id"/>
			<result column="content" property="content"/>
			<result column="at" property="at"/>
		</collection>
	</resultMap>
	<!-- ... -->
</mapper>

Rename column for association column

If you don't like the column name generated by the default implementation of Spring Data JDBC ... You can change it by defining the implementation class of NamingStrategy in Bean. Here, I will introduce how to change Todo to todo_id and Todo_key to the column name sort_order.

First ... Change the column name of the table.

src/main/resources/schema.sql

CREATE TABLE IF NOT EXISTS activity (
	id IDENTITY
	,todo_id INTEGER NOT NULL --Change column name
	,sort_order INTEGER NOT NULL --Change column name
	,content TEXT NOT NULL
	,at TIMESTAMP NOT NULL
);

Next ... Bean definition the implementation class of NamingStrategy.

@Bean
NamingStrategy namingStrategy() {
	return new NamingStrategy(){
		@Override
		public String getReverseColumnName(RelationalPersistentProperty property) {
			return NamingStrategy.super.getReverseColumnName(property).toLowerCase() + "_id";
		}
		@Override
		public String getKeyColumn(RelationalPersistentProperty property) {
			return "sort_order";
		}
	};
}

If you use the MyBatis implementation, you will also need to modify the SQL.

src/main/resources/com/example/demo/mapper/ActivityMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.ActivityMapper">
	<insert id="insert" useGeneratedKeys="true" keyProperty="instance.id">
		INSERT INTO activity
			(todo_id, sort_order, content, at)
		VALUES
			(#{additonalValues.todo_id}, #{additonalValues.sort_order}, #{instance.content}, #{instance.at})
	</insert>
</mapper>

src/main/resources/com/example/demo/mapper/TodoMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.domain.ActivityMapper">
	<!-- ... -->
	<delete id="delete-activities">
		DELETE FROM activity WHERE todo_id = #{id}
	</delete>
	<delete id="deleteAll-activities">
		DELETE FROM activity WHERE todo_id = #{id}
	</delete>
	<!-- ... -->
	<select id="findById" resultMap="todoMap">
		SELECT
			t.id, t.title, t.details, t.finished
			, a.id as activity_id, a.content as activity_content, a.at as activity_at
		FROM
			todo t
		LEFT OUTER JOIN activity a ON a.todo_id = t.id
		WHERE
			t.id = #{id}
		ORDER BY
			a.sort_order
	</select>
	<!-- ... -->
</mapper>

Setting values for audit columns (Auditing function)

In Spring Data, "Mechanism to set the value in the column (audit column) that holds when / who / data was created / updated (last updated) -data / commons / docs / 2.1.0.RELEASE / reference / html / # auditing), and this function can also be used with Spring Data JDBC.

IMPORTANT:

At the time of Spring Data JDBC 1.0.0.RELEASE (Lovelace), there is a part that Auditing function for nested objects (1: 1, 1: N) is not supported. This seems to be a problem on the Spring Data Commons side, not on the Spring Data JDBC side.

• [DATACMNS-1297] (https://jira.spring.io/projects/DATACMNS/issues/DATACMNS-1297): An error always occurs when an audit property exists in the N-side class with a 1: N relationship (data) Will result in an error even if there are 0 cases)
• [DATACMNS-1296] (https://jira.spring.io/projects/DATACMNS/issues/DATACMNS-1296) : An error occurs if the object itself is null when the property for auditing exists in the nested class.

Enable Auditing feature

To use the Auditing function, add @ org.springframework.data.jdbc.repository.config.EnableJdbcAuditing to the configuration class.

@EnableJdbcAuditing //add to
@Import(JdbcConfiguration.class)
@EnableJdbcRepositories
@Configuration
public class SpringDataJdbcConfig {
    // ...
}

If you want to record who created / updated, a class that implements the ʻorg.springframework.data.domain.AuditorAware` interface.

package com.example.demo;

import org.springframework.data.domain.AuditorAware;

import java.util.Optional;

public class MyAuditorAware implements AuditorAware<String> {

	static ThreadLocal<String> currentUser = ThreadLocal.withInitial(() -> "default");

	public Optional<String> getCurrentAuditor() {
		return Optional.ofNullable(currentUser.get());
	}

}

NOTE:

Here, the implementation is such that the user name set in the thread-local variable is simply returned as the creator / last updater of the data, but when developing an actual application, Spring Security etc. It is common to return the login user name etc. managed by the authentication function of.

And register it in the application context.

@EnableJdbcAuditing
@Import(JdbcConfiguration.class)
@EnableJdbcRepositories
@Configuration
public class SpringDataJdbcConfig {
    // ...
    @Bean
    AuditorAware<String> auditorAware() {
        return new MyAuditorAware();
    }
    // ...
}

Also, if you want to change the "when" time acquisition method from the default implementation (CurrentDateTimeProvider =LocalDateTime.now ()), apply an object that implements the ʻorg.springframework.data.auditing.DateTimeProviderinterface. Specify the BeanID of the object registered in the context and registered in thedateTimeProviderRef attribute of @ EnableJdbcAuditing`.

@EnableJdbcAuditing(dateTimeProviderRef = "dateTimeProvider")
@Import(JdbcConfiguration.class)
@EnableJdbcRepositories
@Configuration
public class SpringDataJdbcConfig {
    // ...
    @Bean
    DateTimeProvider dateTimeProvider(ObjectProvider<Clock> clockObjectProvider) {
        return () -> Optional.of(LocalDateTime.now(clockObjectProvider.getIfAvailable(Clock::systemDefaultZone)));
    }
    // ...
}

Addition of columns for Auditing

After enabling the Auditing feature, first add an audit column to the table.

CREATE TABLE IF NOT EXISTS todo (
	id IDENTITY
	,title TEXT NOT NULL
	,details TEXT
	,finished BOOLEAN NOT NULL
	,created_at TIMESTAMP
	,created_by VARCHAR(64)
	,last_updated_at TIMESTAMP
	,last_updated_by VARCHAR(64)
);

Using annotation-based Auditing features

As of Spring Data JDBC 1.0.0.RELEASE (Lovelace), Annotation-based Auditing function is supported. .RELEASE / reference / html / # auditing.annotations )only.

When using the annotation-based Auditing function, add a property to the value of the audit column and add the following annotation to the target column.

• @CreatedDate
• @CreatedBy
• @LastModifiedDate
• @LastModifiedBy

~~WARNING:~~

~~ As of Spring Data JDBC 1.0 M3 (Lovelace), it does not work properly if the property type with @Id is a primitive. (DATAJDBC-216) ~~-> 2018-05-18 Supported: grin:

package com.example.demo.domain;

import org.springframework.data.annotation.CreatedBy;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.LastModifiedBy;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.relational.core.mapping.Column;

import java.time.LocalDateTime;
import java.util.List;

public class Todo {
	@Id
	private int id;
	private String title;
	private String details;
	private boolean finished;

	//Creation date and time
	@CreatedDate
	@Column("created_at")
	private LocalDateTime createdAt;

	//Author
	@CreatedBy
	@Column("created_by")
	private String createdBy;

	//Last Modified
	@LastModifiedDate
	@Column("last_updated_at")
	private LocalDateTime lastUpdatedAt;

	//Last updated
	@LastModifiedBy
	@Column("last_updated_by")
	private String lastUpdatedBy;
	private List<Activity> activities;

	// setters/getters

}

NOTE:

Unrelated to the Auditing feature, @ Column is an annotation supported after the release of Spring Data JDBC 1.0 M3 (Lovelace) ([DATA JDBC-106](https://jira.spring.io/projects/DATAJDBC/issues/ DATA JDBC-106)). ~~ By the way, if there is a certain rule such as mapping of snake case column and camel case property name, you can use NamingStrategy to absorb the difference in name. ~~ (→ DATAJDBC-206 The mapping between the snake case column name and camel case property name is now the default behavior. became)

Interface-based Auditing feature

The interface-based Auditing feature is not supported as of Spring Data JDBC 1.0.0.RELEASE (Lovelace). This is because Spring Data JDBC does not yet support the ʻOptional` property (DATAJDBC-205).

Spring Boot cooperation

For the time being ... It seems that there is spring-data-jdbc-boot-starter in the developer's personal repository. It is not deployed anywhere at this time, so you need to install and use it in your local repository. (I didn't use it this time for the time being) ↓ Spring Boot 2.1 (2.1.0.M4) will provide AutoConfigure and Starter!

NOTE:

I wrote "Try spring-boot-starter-data-jdbc (2.1.0.BUILD-SNAPSHOT)".

Summary

I think it is a library that is still in the process of development and will grow, so I would like to keep an eye on the trends. I feel that it will be quite practical if you can define custom methods in the Repository interface and specify SQL with annotations (as far as the README is concerned, there seems to be a plan to support it ⇒ supported !!). And it would be great if cooperation with Spring Data REST is supported. ↓ Finally 1.0.0 has been officially released! !! It's still a developing library, but I think it's grown a lot compared to when I wrote this entry (2018/01/08). [Spring Data REST Reference (Spring Data REST 3.1.0.RELEASE)](https://docs.spring.io/spring-data/rest/docs/3.1.0.RELEASE/reference/html/#getting-started As far as .bootstrap) is concerned, it seems that official support is not yet available (I'll give you an issue ...).