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 commonly used to increase the readability of complex queries by decomposition and reusable blocks. In addition, recursive CTEs can be used to traverse graphs and tree like structures.

Data-modifying CTEs can be used to execute multi-statement implicit transactions, for example when combining several INSERT and UPDATE statements into one logical unit of operation / transaction. This saves roundtrips over the network 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.

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

For the purpose of demonstration, the statements involved in our business transaction are:

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 equivalent of the above when using a modifying CTE 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

It's also an implicit transaction without a BEGIN and COMMIT that signifies and explicit transaction.

Implementation

To use CTEs in Java, you need to use JDBC. Neither the JPA query language (JPQL) or programmatic Criteria API support CTEs, nor does any of the main other ORMs. Regardless of ORM framework used, it’s always fine to use JDBC along the side.

Configuring the Datasource

To leverage CTEs the best way you want to run them as implicit transactions, as in auto-commit mode (default). It's however common to disable auto-commit and have the transaction manager and it's interceptor apply the explicit transaction markers based on the propagation attributes of the @Transactional annotations. To mix both implicit and explicit modes, its better to stick with the default (auto-commit = true).

In this example, we set auto-commit to true 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); // The default
        ds.addDataSourceProperty("reWriteBatchedInserts", "true");
        ds.addDataSourceProperty("ApplicationName", "Spring Boot CTE");
        return ds;
    }

That is pretty much all there is to it in terms of setup. The remaining parts of using CTEs is just using typical JDBC constructs.

Business Service

Spring provides the JdbcTemplate that makes using the verbose JDBC API easier. 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());
                });
    }

Notice 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 already in progress, it will be surfaced through the assertion. You can run CTEs in explicit transaction mode as well, but in this demo we want implicit.

Conclusions

Common Table Expressions (CTEs) in CockroachDB offer a powerful tool for simplifying complex queries, enabling recursive operations, and optimizing database interactions by reducing network roundtrips. While not natively supported in JPA or Hibernate, developers can still leverage data-modifying CTEs through JDBC, to enhance performance and maintainability of database operations.