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);
ds.setMinimumIdle(25);
ds.setAutoCommit(true);
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.
