A Common Table Expression (CTE) is a named result set of a query that exist temporarily for use within the context of a larger query. CTEs are used to increase the readability of complex queries by decomposition and reusable blocks. Recursive CTEs can be used to traverse graphs and trees and so on.

CTEs can also be used to execute data-modifying, multi-statement implicit transactions, for example when combining several INSERT and UPDATE statements into one logical unit of operation. This saves roundtrips over the network to the database and you can also leverage automatic retries in CockroachDB if the workload is contended.

This article will use Spring Data JDBC to demonstrate this capability in a conceptual way.

Example Code

The code examples in this post are available in Github.

Introduction

Let's use a simple write-only use case where we have just two entities or tables - accounts and transactions:

create table t_account
(
    id            int         not null default unique_rowid(),
    balance       float       not null,
    creation_time timestamptz not null default clock_timestamp(),

    primary key (id)
);

create table t_transaction
(
    id                 int         not null default unique_rowid(),
    account_id         int         not null,
    amount             float       not null,
    transaction_type   string      not null default 'generic',
    transaction_status string      not null default 'pending',
    creation_time      timestamptz not null default clock_timestamp(),

    primary key (id)
);

alter table if exists t_transaction
    add constraint fk_transaction_ref_account
        foreign key (account_id)
            references t_account (id);

The statements involved in our business transaction would then be:

BEGIN;

INSERT INTO t_transaction (
    id,
    account_id,
    amount,
    transaction_type,
    transaction_status)
VALUES (unique_rowid(), ?, ?, ?, ?);

UPDATE t_account SET balance=balance+? WHERE id=?;

COMMIT;

The CTE approach for the above would be:

WITH x AS ( 
INSERT INTO t_transaction ( 
  id, 
  account_id, 
  amount, 
  transaction_type, 
  transaction_status) 
VALUES (unique_rowid(), ?, ?, ?, ?) RETURNING account_id) 
  UPDATE t_account SET balance=balance+? WHERE id=? RETURNING NOTHING

As you can see it's an implicit transaction with no BEGIN and COMMIT that signifies and explicit transaction. In CockroachDB you can also see this in the DB console at the statement statistics page.

Implementation

To use CTEs in Java you need to use JDBC. While it's common to use ORMs like JPA with Hibernate these days, neither the JPA query language (JPQL) or programmatic Criteria API support CTEs. Other frameworks may have such capabilities, but its always fine to use JDBC along side with any data access framework.

Configuring the Datasource

To leverage CTEs the best way you want to run them as implicit transactions, as in auto-commit mode. It's common to disable auto-commit and let the transaction manager and it's interceptor apply the explicit transaction markers based on the propagation attributes of the @Transactional annotation.

Here we enable auto-commit's on our Hikari connection pool datasource (for clarity):

    @Bean
    public HikariDataSource hikariDataSource() {
        HikariDataSource ds = properties
                .initializeDataSourceBuilder()
                .type(HikariDataSource.class)
                .build();
        ds.setMaximumPoolSize(50);// Should be: cluster_total_vcpu * 4 / total_pool_number
        ds.setMinimumIdle(25); // Should be maxPoolSize for fixed-sized pool
        ds.setAutoCommit(true);
        ds.addDataSourceProperty("reWriteBatchedInserts", "true");
        ds.addDataSourceProperty("application_name", "Spring Boot CTE");
        return ds;
    }

Thats pretty much it and the rest is just using JDBC as its normally used with Spring and its mechanisms.

Business Service

Spring provides the JdbcTemplate that makes using JDBC really easy.

You can use it directly in the service bean or in a repository implementation. In this example, we use the template directly in the service:

    @Override
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public void createTransaction(TransactionEntity singleton) {
        Assert.isTrue(!TransactionSynchronizationManager.isActualTransactionActive(), "Transaction not expected!");

        jdbcTemplate.update(
                "WITH x AS ( "
                        + "INSERT INTO t_transaction ( "
                        + "  id, "
                        + "  account_id, "
                        + "  amount, "
                        + "  transaction_type, "
                        + "  transaction_status) "
                        + "VALUES (unique_rowid(), ?, ?, ?, ?) RETURNING account_id) "
                        + "  UPDATE t_account SET balance=balance+? WHERE id=?",
                ps -> {
                    int i = 1;
                    ps.setLong(i++, singleton.getAccountId());
                    ps.setDouble(i++, singleton.getAmount());
                    ps.setString(i++, singleton.getTransactionType());
                    ps.setString(i++, singleton.getTransactionStatus());
                    ps.setDouble(i++, singleton.getAmount());
                    ps.setLong(i++, singleton.getAccountId());
                });
    }

One thing to point out is that the transaction propagation attribute is set to NOT_SUPPORTED since we want our CTE to run in auto-commit mode. If for any reason there is a transaction in progress, it will be surfaced. You can of course still run CTEs in explicit mode as well.

@Transactional(propagation = Propagation.NOT_SUPPORTED)

Conclusions

Common table expressions are helpful for complex queries, recursive use cases and to save roundtrips to the database in contrast to batching. It's not supported in JPA or Hibernate, but you can still use JDBC in combination to leverage this feature.