User Guide (EN)

Contents

Spring Data JDBC Reference Documentation

1. 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
2. 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 provides 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 : Using Object Getters for parameter binding
• mapParameterSource : Using map key/value for parameter binding
• entityParameterSource : Using Spring Data JDBC mapping information for parameter binding
• compositeSqlParameterSource : Parameter binding from combined sources

1.7 SingleValueSelectTrait

For mapping a single-column ResultSet, such as a count result.

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

Generate SqlParameterSource (beanParameterSource, mapParameterSource, entityParameterSource). Spring JDBC Plus supplies DefaultSqlParameterSourceFactory (Default) and EntityConvertibleSqlParameterSourceFactory. The converter settings of SqlParameterSourceFactory should be set separately with Spring Data JDBC CrudRepository settings

1.8.1 DefaultSqlParameterSourceFactory

• Using default JDBC Parameter converting strategy
• The generated ParameterSource depends on the JDBC driver's type converting strategy

1.8.2 EntityConvertibleSqlParameterSourceFactory

• Supports registration of ParameterSource Type Converters for various types.

@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

Create an SqlParameterSource with settings applied for JdbcParameterSourceConverter, FallbackParameterSource, and PaddingIterable.

• JdbcParamaterSourceConverter : Registers the Converter to be applied for ParameterSource Converting.
• FallbackParameterSource : Injects the strategy to handle cases where a required Parameter for SQL Binding is not present in the ParameterSource.
• PaddingIterable : Adjusts the number of binding parameters uniformly to reduce SQL Parsing overhead when binding Iterable (List, Set, Collection) parameters. It is mainly used for the WHERE IN condition.
  • You can activate this settings via parameterSourceFactory.setPaddingIterableParam(true);
  • You can set padding scope pattern via this.setPaddingIterableBoundaries(...);

  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")

This option is not applied in Spring Data JDBC CrudRepository

1.8.4 JdbcParameterSourceConverter (DefaultJdbcParameterSourceConverter)

This provides default converters. You can register custom converters and unwrappers as well.

• Default Converter
  • InstantParameterTypeConverter : Convert Instant to Date
  • LocalDateTimeParameterTypeConverter : Convert LocalDateTime to Date
  • LocalDateParamterTypeConverter : Convert LocalDate to Date
  • ZonedDateTimeParamterTypeConverter : Convert ZonedDateTime to Date
  • UuidToStringTypeConverter : Convert UUID to String
  • EnumToNameConverter : Convert Enum to name
• Unwrapper
  • You can register an Unwrapper to unwrap wrapped values, such as AggregateReference
  • If the unwrapper is applied, the unwrapped result is converted by Converter

Converters and Unwrappers are matched and applied only to the exact type. If you need to define custom matching conditions, you can register a ConditionalConverter and a ConditionalUnwrapper By implmenting the matches method, the appropriate Converter and Unwrapper are selected based on the condition.

This feature is not applied to 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 is a feature that allows you to specify a table's AS name in SQL. This can be used advantageously when writing custom SQL Statements. It can be applied to classes, fields, and methods.

1.10 @SqlFunction

@SqlFunction is an annotation that can be set on a field or a method. You can use it when you want to map a specific column to an 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;
	}
}

This Entity creates

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

This feature is helpful when setting a null default at the SQL level

2. Spring Boot Starter Data JDBC Plus Repository

• This module provides some extended features for Spring Data JDBC CrudRepository

2.1 JdbcRepository

Spring Data JDBC CrudRepository provides save method (merge) However, it is necessary to directly call insert depending on the @Id generation strategy. The JdbcRepository is helpful when you want to directly call the insert and update methods.

If you include the spring-data-jdbc-plus-repository dependency, you must declare @Table on your Entities.

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 does not support Reactive types (Flux, Mono) as the return type for extension methods. Spring Boot Starter Data JDBC Plus Repository provides support for declaring extension methods that return Reactive types with simple configuration.

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;
}

@SoftDeleteColumn is created to enable the SoftDelete feature. It works by overriding Spring Data JDBC's default DELETE functionality to execute an UPDATE on the corresponding field instead. It supports boolean and String types, and can be configured using @SoftDeleteColumn.Boolean or @SodtDeleteColumn.String, respectively