I tend to forget how to combine Spring Data JPA with Materialized Views 🤦 That’s why this post is for me, so I can check later how I did it in the past.
I’m using PostgreSQL for this example.
Tables and Views
Transactions Table: It records the Transactions you receive with the Asset (€, $, …).
Account Balance: It gives the sum of all the Transactions to give the Account Current Balance per Asset.
Materialized View Schema
The Account Balance Materialized View looks like:
CREATE MATERIALIZED VIEW IF NOT EXISTS account_balance
AS
SELECT
name,
account_id,
asset,
sum(value) as balance
FROM
transactions
GROUP BY name, account_id, asset
WITH NO DATA;See source code here
Spring @Entity that consumes the View
In order to consume this view via Spring Boot Data JPA, we need to declare as the ID of the @Entity the fields used in the GROUP BY.
class AccountBalanceId(
val accountId: UUID = UUID.randomUUID(),
val name: String = "",
val asset: String = "",
): Serializable
@Entity
@IdClass(AccountBalanceId::class)
@Immutable
class AccountBalance(
@Id
val accountId: UUID,
@Id
val name: String,
@Id
val asset: String,
val balance: Double
)
interface AccountBalanceRepository: CrudRepository<AccountBalance, AccountBalanceId> {
fun findByAccountId(accountId: UUID): List<AccountBalance>
}See @Entity here and @Repository here.
Full Source Code
Working solution here https://github.com/aleixmorgadas/poc-spring-reactive-timescale
