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)