The other day (2019/1/31) was held [JSUG Study Session 2019 Part 1](https://jsug.doorkeeper.jp/events/86027?error_code=4201&error_message=User%20canceled%20the%20Dialog%20flow#_ I tried to summarize the contents of = _).

Java OR mapper selection points

[SlideShare Link](https://www.slideshare.net/masatoshitada7/java-or-jsug?ref=https%3A%2F%2Ftwitter.com%2Fi%2Fcards%2Ftfw%2Fv1%2F1090929751462531075%3Fcardname%3Dplayer&autoplay_disabled=true&forward=true&earned=true&edge=true&lang=ja&card_height=130&scribe_context=%7B%22client%22%3A%22web%22%2C%22page%22%3A%22search%22%2C%22section%22%3A%22default_tweets%22%2C%22component%22%3A%22tweet%22%7D&bearer_token=AAAAAAAAAAAAAAAAAAAAAPYXBAAAAAAACLXUNDekMxqa8h%252F40K4moUkGsoc%253DTYfbDKbT3jJPCEVnMYqilB28NHfOPqkca3qaAxGfsyKCs0wRbw&fbclid=IwAR3nUi1c0gunXLMwqG32IRyfwYgMrPwHLpJWaNzvoEYNczBAwqOZOZWWMno )

OR mapper and its classification

--Speaking of DB access in Java, JDBC --JDBC problems --Write SQL as a string in the program --You have to write the same thing every time --It is troublesome to refill ResultSet into entities

That's where the OR Mapper comes in

--Convert RDB records to Java objects --SQL can be automatically issued or described in an external file

There are 4 (+1) types of OR mappers

--JDBC wrapper type --Thinly wrapped JDBC --SQL wrapper type --Specializes in SQL and class refilling --Query builder type --Write SQL with classes and methods --OR mapper type in a narrow sense --Relation-oriented, automatic SQL issuance

Spring Data --There is Spring Data JPA --New Spring Data JDBC is now available

JDBC wrapper type

--Just a thin wrapping of JDBC --Easy to use and low learning cost --Spring JDBC etc.

Spring JDBC --Do not touch Connection and Statement --Developer describes only refilling from ResultSet --Work without Spring DI container --BeanPropertyRowMapper class --If the column name and property name are the same, they will be automatically refilled. --Other than --You can do anything JDBC can do --Classes with aggregates, batch updates, 1: N relationships --Summary --Easy to use --SQL is solid --RowMapper description is troublesome

SQL mapper type

--Specialized in SQL and class refilling --MyBatis, Doma, etc.

My Batis --Write SQL in XML --One-to-one interface with Mapper XML --Only the interface needs to be created --Conditional branch <if test="salary != null"> --It is difficult to handle inequality signs ("<", ">" cannot be applied in xml) -It is good to use <! CDATA>, but this time it is difficult to separate \ and \ <! CDATA>.

Doma --2-way SQL can be written --Can be executed by SQL alone ――In the future, only English documents will be available

2-way SQL --Write parameters and conditional branches in comments --2-way SQL is convenient, but annotation processors may conflict.

Query builder type

--Write SQL in classes and methods --If you make a mistake in the description, a compile error will occur.

jOOQ,DBFlute

jOOQ --Search example

  create 
    .select(EMPLOYEE.ID)
    .from(EMPLOYEE)  
    .orderby()

--No worries about SQL typos, but it's unclear if complex SQL can be reproduced

OR mapper type

JPA --Java EE standard data access specifications --Created in an attempt to standardize Hibernate --Since it is a specification, an implemented library is required.
- EclipseLink,Hibernate --Search by JPQL --Although you can write SQL, there are restrictions on subqueries. --The state of the entity is super important --New, Managed, Detached, Removed state --EclipseLink and Hibernate behave differently --High learning cost ――When asked why they chose JPA, many people answered that it was a standard technology.

Conditions for using JPA

--You can design a new DB, there is no complicated SQL, there are proficient people in PJ, etc. ――The difficulty level is high. Should be avoided

Spring Data --Abstract many data access technologies to provide a common interface --CrudRepository interface --Spring Data JPA only for RDB --You need to understand the complexity of JPA before using it.

OR mapper selection flow

--Recommended is Spring JDBC or MyBatis

Easy-to-understand Spring Data JDBC

[SpeakerDeck Link](https://speakerdeck.com/rshindo/jsug-2019-01 )

JPA is difficult

--So Spring Data JDBC

What is Spring Data JDBC?

--A library for RDB access that has joined the Spring Data family. --Repository like Spring Data JPA can be easily implemented without JPA. --The concept is to be simple --It's not the direction that you can do anything like JDBC Template. --Does not support cache, lazy loading, etc. --Entity is OK with Pojo (@entity is unnecessary) --Do not operate JdbcTepmlate directly --Closer to OR mapper than SQL mapper --Support one to many

Spriong Data JDBC

getting start
- Spring Boot 2.1.1
- Spring Data JDBC 1.0.4 --save method… insert or update

architecture

--You can choose Spring JDBC or MyBatis --Wrapper for JDBC Template --Collaboration with MyBatis is also possible --Repository interface, if you create an xml file, you can use Repository

How to make an entity

--Make one-on-one with the table like JPA --No @Entity required --Add @id to the primary key --Supports constructors with arguments --No need for setter. --It is also possible to make an entity immutable

Repository

--You can execute any query with @Query --Custom repository

Things impossible

--Paging, sorting --PagingAndSortRepository is not supported --Ability to automatically generate a query from a method name --Composite primary keys are not supported ――It may be possible to do it in the future

relation

--Supports related object persistence operations --Supports 1-to-N relationships --N to 1 and M to N are not supported --Support status differs between Spring JDBC implementation and MyBatis implementation

Parent-child table (1: N)

--When you delete the parent record, the child record is also deleted. --When updating the parent record, the child record is Del / Insert --Create an employee department table in addition to the employee table and department table

Other features

Events -Event trigger can be set for each operation of Repository
Auditing --Value setting for audit column

Summary

--People who recommend Spring Data JDBC --People who want to use Repository but don't want to use JPA --People who don't want to write queries --People who do not recommend Spring Data JDBC --People who want to write all queries --People who want cache and lazy fetch --People who don't match the existing schema --Natural keys are not suitable

JSUG Study Group Participation Report