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

Sep 18

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.