User Guide (EN)

Spring Boot Starter Data JDBC Plus SQL
- 1.1. Gradle / Maven Dependency
- 1.2. JdbcRepositorySupport, JdbcDaoSupport
- 1.3. JdbcReactiveDaoSupport
- 1.4. EntityRowMapper, AggregateResultSetExtractor
  - 1.4.1. EntityRowMapper
  - 1.4.2. AggregateResultSetExtractor
- 1.5. SqlGeneratorSupport (SqlAware)
- 1.6. SqlParameterSource
- 1.7. SingleValueSelectTrait
- 1.8. SqlParameterSourceFactory
  - 1.8.1. DefaultSqlParameterSourceFactory
  - 1.8.2. EntityConvertibleSqlParameterSourceFactory
  - 1.8.3. ConvertibleParameterSourceFactory
  - 1.8.4. JdbcParameterSourceConverter (DefaultJdbcParameterSourceConverter)
- 1.9. SqlTableAlias
- 1.10. @SqlFunction
Spring Boot Starter Data JDBC Plus Repository
- 2.1. JdbcRepository
- 2.2. Reactive Type Support
- 2.3. @SoftDeleteColumn

1. Spring Boot Starter Data JDBC Plus SQL

Spring Boot Starter Data JDBC Plus SQL provides helpful functions for creating custom SQL statements while using Spring Data JDBC

1.1. Gradle / Maven Dependency

Gradle

dependencies {
    implementation("com.navercorp.spring:spring-boot-starter-data-jdbc-plus-sql:3.3.5")
}

Maven

<dependency>
	<groupId>com.navercorp.spring</groupId>
	<artifactId>spring-boot-starter-data-jdbc-plus-sql</artifactId>
	<version>3.3.5</version>
</dependency>

Java Codes

@Table("n_order")
@Data
public class Order {
    @Id
    @Column("order_no")
    private Long orderNo;

    @Column("price")
    private long price;

    @Column("purchaser_no")
    private String purchaserNo;
}

public interface OrderRepository extends CrudRepository<Order, Long>, OrderRepositoryCustom {
}

public interface OrderRepositoryCustom {
    List<Order> findByPurchaserNo(String purchaserNo);
}

public class OrderRepositoryImpl extends JdbcRepositorySupport<Order> implements OrderRepositoryCustom {
	private final OrderSql sqls;

	public OrderRepositoryImpl(EntityJdbcProvider entityJdbcProvider) {
		super(Order.class, entityJdbcProvider);
		this.sql = super.sqls(OrderSql::new);
	}

	@Override
	public List<Order> findByPurchaserNo(String purchaserNo) {
		String sql = this.sql.selectByPurchaserNo();
		return find(sql, mapParameterSource()
			.addValue("purchaserNo", purchaserNo));
	}
}

Groovy codes for SQL

implementation("org.codehaus.groovy:groovy:${groovyVersion}")

class OrderSql extends SqlGeneratorSupport {

    String selectByPurchaserNo() {
        """
        SELECT ${sql.columns(Order)}
        FROM n_order
        WHERE purchaser_no = :purchaserNo
        """
    }
}

1.2. JdbcRepositorySupport, JdbcDaoSupport

Provides helpful methods to use Customizing Individual Repositories
Using JdbcOperations(NamedParameterJdbcTemplate) internally

# JdbcRepositorySupport
public class OrderRepositoryImpl extends JdbcRepositorySupport<Order> implements OrderRepositoryCustom {
	private final OrderSql sqls;

	public OrderRepositoryImpl(EntityJdbcProvider entityJdbcProvider) {
		super(Order.class, entityJdbcProvider);
		this.sql = super.sqls(OrderSql::new);
	}

	@Override
	public List<Order> findByPurchaserNo(String purchaserNo) {
		String sql = this.sql.selectByPurchaserNo();
		return find(sql, mapParameterSource()
			.addValue("purchaserNo", purchaserNo));
	}
}

# JdbcDaoSupport
public class OrderDaoImpl extends JdbcDaoSupport implements OrderRepositoryCustom {
	private final OrderSql sqls;

	public OrderDaoImpl(EntityJdbcProvider entityJdbcProvider) {
		this.sql = super.sqls(OrderSql::new);
	}

	@Override
	public List<OrderDto> selectByPurchaserNo(String purchaserNo) {
		String sql = this.sql.selectByPurchaserNo();
		return select(sql, mapParameterSource()
			.addValue("purchaserNo", purchaserNo),
                        OrderDto.class);
	}
}

Providing separate components for the Repository and Dao, allowing for their conceptual separation in usage. JdbcRepositorySupport and JdbcDaoSupport are similar to each other, but there's a difference in terms of implementation.

(1) Method name of Select query execution

JdbcRepositorySupport executes via find.
JdbcDaoSupport executes via select.
There's no functional difference.

(2) Return type

JdbcRepositorySupport uses the AggregateRoot(Entity) as its basic type just like the standard Repository interface.
JdbcDaoSupport can return many QueryModel types so it does not have the basic type.

# JdbcRepositorySupport
public List<Order> findByPurchaserNo(String purchaserNo) {
    String sql = this.sql.selectByPurchaserNo();
    return find(sql, mapParameterSource()
        .addValue("purchaserNo", purchaserNo));  // It uses the order type basically, which is set in the constructor.
}

# JdbcDaoSupport
public List<OrderDto> selectByPurchaserNo(String purchaserNo) {
    String sql = this.sql.selectByPurchaserNo();
    return select(sql, mapParameterSource()
        .addValue("purchaserNo", purchaserNo),
        OrderDto.class);  // Specify the return type when executing the Query.
}

You can specify the return type while using the JdbcRepositorySupport

(3) Mapping the query result

JdbcRepositorySupport maps the query result using the AggregateResultSetExtractor
- AggregateResultSetExtractor maps the query results for 1 : N relationships to the target Aggregate type.
JdbcDaoSupport maps the query result using the EntityRowMapper
- EntityRowMapper maps the query results row by row.

The two mapping strategies also result in differences in the SQL that is required.

You can use different mapping strategy with both JdbcRepositorySupport and JdbcDaoSupport

# JdbcRepositorySupport
public List<Order> findByPurchaserNo(String purchaserNo) {
    String sql = this.sql.selectByPurchaserNo();
    return find(sql, mapParameterSource()
        .addValue("purchaserNo", purchaserNo),
        this.getRowMapper());  // This will map the query result using `EntityRowMapper`.
}

# JdbcDaoSupport
public List<OrderDto> selectByPurchaserNo(String purchaserNo) {
    String sql = this.sql.selectByPurchaserNo();
    return select(sql, mapParameterSource()
        .addValue("purchaserNo", purchaserNo),
        this.getAggregateResultSetExtractor(OrderDto.class));  // This will map the query result using `AggregateResultSetExtractor`
}

(4) Publishing the query result to the AfterLoadEvent and AfterLoadCallback 4.8. Entity Callbacks

JdbcRepositorySupport publishes the query result to both the ApplicationEventPublisher and the EntityCallbacks
JdbcDaoSupport does not publish the query result.

1.3. JdbcReactiveDaoSupport

JdbcReactiveDaoSupport extends JdbcDaoSupport and supports that return Reactive types (e.g. Flux).
JDBC Query execution is blocking, but this approach is useful in scenarios where Asynchronous Stream is beneficial (e.g. Excel download)
To use CustomRepository extensions, the reactive-support option of spring-data-jdbc-plus-repository must be activated. Reactive Type Support

1.4. EntityRowMapper, AggregateResultSetExtractor

There is a difference in query result mapping between the AggregateResultSetExtractor and EntitiyRowMapper, requiring the SQL JOIN statement to be adjusted accordingly when associations are present.

1:1, 1:N Relationship Entity

@Table("n_board")
public class Board {
    @Id
    private Long id;
    
    private String name;

    @Column("board_id")
    private Audit audit;    // 1:1 Relationship (FK Column `n_audit.board_id`)

    @MappedCollection(idColumn = "board_id", keyColumn = "board_index")
    private List<Post> posts = new ArrayList<>();    // 1:N Relationship (FK Column `n_post.board_id`, Order By Column `n_post.board_index`)
}

@Table("n_audit")
public class Audit {
    @Id
    private Long id;

    private String name;
}

@Table("n_post")
public class Post {
   @Id
   private Long id;

   private String title;

   private String content;
}

1:1 Relationship (n_board -> b_audit) does not introduce duplicates when joined.
1:N Relationship results in the duplication of "n_board" rows by the number of associated n_post records.

(If the associated data is optional, the SQL must be written using a LEFT OUTER JOIN)

1.4.1. EntityRowMapper

The RowMapper used by the CrudRepository in Spring Data JDBC

The EntityRowMapper maps SQL query results row by row, including one-to-one relationships.
For 1:N relationship EntityRowMapper maps the query results using additional query execution (EAGER Fetch).
When write the SQL, you must write the SELECT and FROM clauses (including the JOIN) up to the one-to-one relationship.
Writing the SELECT SQL

SELECT n_board.id AS id, n_board.name AS name, audit.id AS audit_id, audit.name AS audit_name
FROM n_board
LEFT OUTER JOIN n_audit AS audit
WHERE id = :id

The n_post which represents the 'many' side of 1 to N relationship, is mapped using an additional SQL query, which is performed automatically (EAGER Fetch).

SELECT n_post.id AS id, n_post.title AS title, n_post.content AS content
FROM n_post
WHERE n_post.board_id = :board_id
ORDER BY board_index

This execution is exactly the same as the one performed by the Spring Data JDBC CrudRepository, which may lead to the N+1 Query problem.

1.4.2. AggregateResultSetExtractor

The AggregateResultSetExtractor maps the SELECT query to the aggregate entity by grouping the data, supporting up to one-to-many relationships.

There is no EAGER fetch mechanism equivalent to @EntityGraph; only a single SQL execution is used.
This maps the query results from a LEFT OUTER JOIN to the aggregate entity.

Board	Audit	Post
Board 1	Audit 1	Post 1
Board 1	Audit 1	Post 2
Board 1	Audit 1	Post 3
Board 2	Audit 2	Post 4
Board 2	Audit 2	Post 5

# The mapping results
1. Board 1 / Audit 1 / Post 1, Post 2
2. Board 2 / Audit 2 / Post 4, Post 5

To use the AggregateResultSetExtractor, the aggregate entity must have a field annotated with @Id.

1.5. SqlGeneratorSupport (SqlAware)

The SqlGeneratorSupport uses SqlProvider internally. The SqlProvider provides useful methods such as columns, tables, aggregateColumns, aggregateTables

An SqlProvider can be injected into JdbcRepositorySupport and JdbcDaoSupport via the sqls method.

public class BoardRepositoryImpl extends JdbcRepositorySupport<Board> implements BoardRepositoryCustom {
	private final BoardSql sqls;

	public BoardRepositoryImpl(EntityJdbcProvider entityJdbcProvider) {
		super(Board.class, entityJdbcProvider);
		this.sql = super.sqls(BoardSql::new);    // Creating BoardSql and inject the SqlProvider.
	}
}

The class defining the SQL can be written in Groovy or Kotlin, both of which support more convenient multiline strings than pure JAVA.

Also, you can use Java 13 JEP 355: Text Blocks(Preview), Java 14 JEP 368: Text Blocks(Second Preview).

# Groovy
class BoardSql extends SqlGeneratorSupport {

    /**
    *    SELECT n_board.id AS id, n_board.name AS name, audit.id AS audit_id, audit.name AS audit_name
    *    FROM n_board
    *    LEFT OUTER JOIN n_audit AS audit
    *    WHERE name = :name
    */
    String selectByName() {
        """
        SELECT ${sql.columns(Board)} 
        FROM ${sql.tables(Board)}
        WHERE name = :name
        """
    }

    /**
    *    SELECT n_board.id AS id, n_board.name AS name, audit.id AS audit_id, audit.name AS audit_name, post.id AS post_id, post.title AS post_title, post.content AS post_content
    *    FROM n_board
    *    LEFT OUTER JOIN n_audit AS audit
    *    LEFT OUTER JOIN n_post AS post
    *    WHERE name = :name
    */
    String selectAggregateByName() {
        """
        SELECT ${sql.aggregateColumns(Board)} 
        FROM ${sql.aggregateTables(Board)}
        WHERE name = :name
        """
    }
}

${sql.columns(Board)}: returns the SELECT columns for 1:1 relationships. (The counterpart of EntityRowMapper)
${sql.tables(Board)}: returns the FROM JOIN statements for 1:1 relationships. (The counterpart of EntityRowMapper)
${sql.aggregateColumns(Board)}: returns the SELECT columns for 1:N relationships. (The counterpart of AggregateResultSetExtractor)
${sql.aggregateTables(Board)}: returns the FROM JOIN statements for 1:N relationships. (The counterpart of AggregateResultSetExtractor)

1.6. SqlParameterSource

When using JdbcOperations, you should provide an SqlParameterSource to bind the SQL parameters.
SqlParameterSource 는 beanParameterSource, mapParameterSource, entityParameterSource, compositeSqlParameterSource 를 제공합니다.

# JdbcRepositorySupport
public List<Order> find(OrderCriteria criteria) {
    String sql = this.sql.select();
    return find(sql, beanParameterSource(criteria));  // beanParameterSource
}

public List<Order> findByPurchaserNo(String purchaserNo) {
    String sql = this.sql.selectByPurchaserNo();
    return find(sql, mapParameterSource()
        .addValue("purchaserNo", purchaserNo));  // mapParameterSource
}

public List<Order> findByExample(Order order) {
    String sql = this.sql.select();
    return find(sql, entityParameterSource(order));  // entityParameterSource
}

public List<Order> findByPurchaserNo(String purchaserNo, OrderCriteria criteria) {
    String sql = this.sql.selectExample;
    return find(sql, compositeSqlParameterSource(
        mapParameterSource().addValue("purchaserNo", purchaserNo),
        beanParameterSource(criteria)
    );  // compositeSqlParameterSource
}

beanParameterSource: parameter 객체의 getter 를 사용해서 binding 할 수 있다.
mapParameterSource: map 에 key/value 를 파라미터로 전달해서 binding 할 수 있다.
entityParameterSource: Spring Data JDBC 의 매핑 정보를 사용해서 파라미터 binding 할 수 있다. (@Column)
compositeSqlParameterSource: 복합 SqlParameterSource 를 조합한 파라미터로 전달해서 binding 할 수 있다.

1.7. SingleValueSelectTrait

count 결과와 같이 단일 컬럼 결과를 매핑하는 JdbcOperations 호출할 때 SingleValueSelectTrait 을 사용할 수 있습니다.

public class OrderRepositoryImpl extends JdbcRepositorySupport<Order> 
    implements OrderRepositoryCustom, SingleValueSelectTrait {
	private final OrderSql sqls;

	public OrderRepositoryImpl(EntityJdbcProvider entityJdbcProvider) {
		super(Order.class, entityJdbcProvider);
		this.sql = super.sqls(OrderSql::new);
	}

	@Override
	public Long countByPurchaserNo(String purchaserNo) {
		String sql = this.sql.countByPurchaserNo();
		return selectSingleValue(sql, mapParameterSource()
			.addValue("purchaserNo", purchaserNo),
                       Long.class);
	}
}

class OrderSql extends SqlGeneratorSupport {
    String countByPurchaserNo() {
        """
        SELECT count(id)
        FROM n_order
        WHERE purchaser_no = :purchaserNo
        """
    }

1.8. SqlParameterSourceFactory

SqlParameterSource (beanParameterSource, mapParameterSource, entityParameterSource) 를 생성한다. DefaultSqlParameterSourceFactory (Default) 와 EntityConvertibleSqlParameterSourceFactory 가 제공된다. SqlParameterSourceFactory 에 등록된 Converter 등의 설정은 Spring Data JDBC CrudRepository 와 별도로 설정됩니다.

1.8.1 DefaultSqlParameterSourceFactory

기본 JDBC Parameter 컨버팅 전략 사용 (Default 설정)
생성한 ParameterSource 는 JdbcDriver 의 Type Converting 전략에 의존한다.

1.8.2 EntityConvertibleSqlParameterSourceFactory

몇가지 타입에 대한 ParameterSource Type Converter 등록 지원

@Configuration
public class JdbcConfig extends JdbcPlusSqlConfiguration {
    @Bean
    @Override
    public SqlParameterSourceFactory sqlParameterSourceFactory(
        JdbcMappingContext jdbcMappingContext, JdbcConverter jdbcConverter, Dialect dialect) {

	return new EntityConvertibleSqlParameterSourceFactory(
		this.parameterSourceConverter(),
                jdbcMappingContext,
                jdbcConverter,
                dialect.getIdentifierProcessing());
    }

    private ConvertibleParameterSourceFactory parameterSourceConverter() {
        JdbcParameterSourceConverter converter = new DefaultJdbcParameterSourceConverter();
        ConvertibleParameterSourceFactory parameterSourceFactory = new ConvertibleParameterSourceFactory(converter, null);
        parameterSourceFactory.setPaddingIterableParam(true);
        return parameterSourceFactory;
    }
}

1.8.3 ConvertibleParameterSourceFactory

JdbcParameterSourceConverter, FallbackParameterSource, PaddingIterable 설정을 적용한 SqlParameterSource 를 생성한다.

JdbcParameterSourceConverter: ParameterSource Converting 에 적용할 Converter 를 등록한다.
FallbackParameterSource: SQL Binding 에 필요한 Parameter 가 ParameterSource 에 존재하지 않을 때 처리할 전략을 주입한다.
PaddingIterable: Iterable(List, Set, Collection) 파라미터 바인딩시 SQL Parsing 비용을 줄이기 위해 바인딩 파라미터 갯수를 균일하게 조정한다. 특히 WHERE IN 조건에 주로 사용된다.
- parameterSourceFactory.setPaddingIterableParam(true); 로 padding 설정을 활성화 할 수 있다.
- this.setPaddingIterableBoundaries(...); 로 padding scope 패턴을 지정할 수 있다.
default boundaries: new int[]{0, 1, 2, 3, 4, 8, 16, 32, 50, 100, 200, 300, 500, 1000, 1500, 2000}

SELECT *
FROM n_order
WHERE id in (:ids)

mapParameterSource()
    .add("ids", Arrays.asList("1", "2", "3", "4", "5", "6"));

-->

SELECT *
FROM n_order
WEHERE id IN (?, ?, ?, ?, ?, ?, ?, ?)

-->

SELECT *
FROM n_order
WEHERE id IN ("1", "2", "3", "4", "5", "6", "6", "6")

Spring Data JDBC CrudRepository 에는 적용되지 않습니다.

1.8.4 JdbcParameterSourceConverter (DefaultJdbcParameterSourceConverter)

Default Converter 가 내장되어 있으며, 추가 타입 Converter, Unwrapper 를 등록할 수 있다.

Default Converter
- InstantParameterTypeConverter: Instant 타입을 Date 로 변환한다.
- LocalDateTimeParameterTypeConverter: LocalDateTime 타입을 Date 로 변환한다.
- LocalDateParameterTypeConverter: LocalDate 타입을 Date 로 변환한다.
- ZonedDateTimeParameterTypeConverter: ZonedDateTime 타입을 Date 로 변환한다.
- UuidToStringTypeConverter: UUID 타입을 String 으로 변환한다. (VARCHAR(36))
- EnumToNameConverter: ENUM 타입을 name 으로 변환한다.
Unwrapper
- AggregateReference 와 같이 Wrapping 된 값을 Unwrapping 할 수 있는 Unwrapper 를 등록할 수 있다.
- Unwrapper 가 적용되면, Unwrapping 한 결과를 Converter 로 한번 더 변환 동작할 수 있다.

Converter 와 Unwrapper 는 정확한 타입에만 매칭되서 적용된다. 매칭 조건을 직접 작성할 필요가 있다면, ConditionalConverter 와 ConditionalUnwrapper 를 등록할 수 있다. matches 메소드를 구현하면 조건에 맞는 Converter 와 Unwrapper 가 선택된다. Spring Data JDBC CrudRepository 에는 적용되지 않습니다.

1.9 SqlTableAlias

@Value
@Builder
@Table("post")
public class PostDto {
    @Id
    Long id;

    @Column
    Post post;

    @SqlTableAlias("p_labels")
    @MappedCollection(idColumn = "board_id")
    Set<Label> labels;
}

// In custom SQL

val sql = """
SELECT ${sql.aggregateColumns(PostDto::class.java)}
FROM n_post AS post
LEFT OUTER JOIN n_label AS p_labels
ON post.board_id = p_labels.board_id
"""

@SqlTableAlias 는 SQL 에서 table 의 AS 이름을 지정할 수 있는 기능입니다. Custom SQL Statement 를 작성할 때 유용하게 사용할 수 있습니다. Class, Field, Method 에 적용될 수 있습니다.

1.10 @SqlFunction

@SqlFunction 은 field 또는 method 에 설정할 수 있는 annotation 입니다. 특정 column 을 SQL function 으로 매핑하고 싶을 때 사용할 수 있습니다.

@Table("n_order")
@Getter
@Builder
public class Order {
	@Id
	private Long id;

	@SqlFunction(expressions = {SqlFunction.COLUMN_NAME, "0"})
	private Long price;

	private OrderStatus status;

	private String purchaserNo;

	public void complete() {
		this.status = OrderStatus.COMPLETED;
	}
}

이 Entity 는 columns 로 아래의 SQL 을 기본적으로 생성하게됩니다.

`n_order`.`id` AS `id`,
COALESCE(`n_order`.`price`, 0) AS `price`,
`n_order`.`status` AS `status`,
`n_order`.`purchaser_no` AS `purchaser_no`

null default 를 SQL level 에서 설정하고 싶을 때 유용합니다

2. Spring Boot Starter Data JDBC Plus Repository

Spring Data JDBC 의 CrudRepository 에 확장 및 추가 기능을 제공합니다.

2.1. JdbcRepository

Spring Data JDBC 의 CrudRepository 는 save 메소드를 제공 합니다. (merge)
@Id 생성 전략에 따라 insert 를 직접 호출할 필요가 있습니다.
insert / update 를 직접 호출할 때 JdbcRepository 를 상속해서 기능을 제공할 수 있습니다.
Spring Data JDBC 에서는 insert / update 메소드를 직접 제공할 계획이 없습니다. DATAJDBC-282
spring-data-jdbc-plus-repository dependency 를 가지면, Entity 에 @Table 을 선언해야 합니다.

Gradle

dependencies {
    implementation("com.navercorp.spring:spring-boot-starter-data-jdbc-plus-repository:3.3.5")
}

Maven

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>com.navercorp.spring:spring-boot-starter-data-jdbc-plus-repository</artifactId>
	<version>3.3.5</version>
</dependency>

Java Codes

@Table("n_order")
@Data
public class Order {
    @Id
    @Column("order_no")
    private Long orderNo;

    @Column("price")
    private long price;

    @Column("purchaser_no")
    private String purchaserNo;
}

public interface OrderRepository extends JdbcRepository<Order, Long> {
}

@Service
public class OrderService {
    private final OrderRepository repository;

    public OrderService(OrderRepository repository) {
        this.repository = repository;
    }

    public Order save(Order order) {
        return this.repository.save(order);
    }

    // JdbcRepository 추가된 insert / update 메소드를 직접 사용
    public Order insert(Order order) {
        return this.repository.insert(order);
    }
  
    public Order update(Order order) {
        return this.repository.update(order);
    }
}

2.2. Reactive Type Support

Spring Data JDBC 에서는 CrudRepository 확장 메소드 반환 타입으로 Reactive(Flux, Mono) 타입을 허용하지 않는다.
간단한 설정으로 Reactive(Flux, Mono) 타입을 반환타입으로 가지는 확장 메소드를 선언할 수 있도록 지원한다.

spring:
  data:
    jdbc:
      plus:
        repositories:
          reactive-support: true

public interface OrderRepository extends CrudRepository<Order, Long>, OrderRepositoryCustom {
}

public interface OrderRepositoryCustom {
    Flux<Order> findOrders(String purchaserId);
}

2.3. @SoftDeleteColumn

@Value
@Builder
@Table("article")
static class SoftDeleteArticle {
	@Id
	Long id;

	String contents;

	@SoftDeleteColumn.Boolean(valueAsDeleted = "true")
	boolean deleted;
}