

Discover more from Aleix's Learnings
Sharing my learnings on Engineering Strategy and Leadership
Continue reading
Spring Data JPA with Materialized Views
What to do when the View doesn't contain an ID and you want to use @Entity for DB Mapping
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
Spring Data JPA with Materialized Views
I am getting empty list after trying above changes . No error as well. Please suggest.