Aleix's Learnings

Share this post
Spring Data JPA with Materialized Views
learnings.aleixmorgadas.dev

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

Aleix Morgadas
Sep 18, 2021
Share this post
Spring Data JPA with Materialized Views
learnings.aleixmorgadas.dev

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

Schemas here
  • 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

Meme for you

Source
Share this post
Spring Data JPA with Materialized Views
learnings.aleixmorgadas.dev
Comments
TopNewCommunity

No posts

Ready for more?

© 2023 Aleix Morgadas
Privacy ∙ Terms ∙ Collection notice
Start WritingGet the app
Substack is the home for great writing