---
type: "article"
title: "Spring Data JPA with Materialized Views"
summary: "What to do when the View doesn't contain an ID and you want to use @Entity for DB Mapping"
newsletter: "Engineering Strategy"
newsletter_handle: "engineeringstrategy"
newsletter_url: "https://usecommune.com/n/engineeringstrategy"
author: "Aleix Morgadas (@aleixmorgadas)"
published: "2021-09-18T08:53:53.525Z"
canonical_url: "https://usecommune.com/n/engineeringstrategy/a/spring-data-jpa-with-materialized-views"
markdown_url: "https://usecommune.com/n/engineeringstrategy/a/spring-data-jpa-with-materialized-views.md"
chat_url: "https://usecommune.com/n/engineeringstrategy/a/spring-data-jpa-with-materialized-views/chat"
body_source: "imported"
likes: 0
replies: 0
body_words: 230
---

# Spring Data JPA with Materialized Views

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

[![](https://bucketeer-e05bbc84-baa3-437e-9518-adb32be77984.s3.amazonaws.com/public/images/a301037e-1a6a-4a08-b99d-f5220c3f7484_1356x606.png)](https://substackcdn.com/image/fetch/$s_!klL-!,f_auto,q_auto:good,fl_progressive:steep/https%3A%2F%2Fbucketeer-e05bbc84-baa3-437e-9518-adb32be77984.s3.amazonaws.com%2Fpublic%2Fimages%2Fa301037e-1a6a-4a08-b99d-f5220c3f7484_1356x606.png)

*[Schemas here](https://github.com/aleixmorgadas/poc-spring-reactive-timescale/tree/main/src/main/resources/db/migration)*

- **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](https://github.com/aleixmorgadas/poc-spring-reactive-timescale/blob/main/src/main/resources/db/migration/V1__.sql#L14-L26)

# 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](http://thttps://github.com/aleixmorgadas/poc-spring-reactive-timescale/blob/main/src/main/resources/db/migration/V5__.sql) and [@Repository here](http://thttps://github.com/aleixmorgadas/poc-spring-reactive-timescale/blob/main/src/main/resources/db/migration/V5__.sql).

# [Full Source Code](https://github.com/aleixmorgadas/poc-spring-reactive-timescale)

[![](https://bucketeer-e05bbc84-baa3-437e-9518-adb32be77984.s3.amazonaws.com/public/images/6dc93edb-b871-4aa4-8ee5-073d5ddd88a8_480x469.gif)](https://github.com/aleixmorgadas/poc-spring-reactive-timescale)

Working solution here [https://github.com/aleixmorgadas/poc-spring-reactive-timescale](https://github.com/aleixmorgadas/poc-spring-reactive-timescale)

# Meme for you

[![](https://bucketeer-e05bbc84-baa3-437e-9518-adb32be77984.s3.amazonaws.com/public/images/5979e998-56c8-4b3b-a15d-dff6c74d01b8_650x617.jpeg)](https://substackcdn.com/image/fetch/$s_!7alr!,f_auto,q_auto:good,fl_progressive:steep/https%3A%2F%2Fbucketeer-e05bbc84-baa3-437e-9518-adb32be77984.s3.amazonaws.com%2Fpublic%2Fimages%2F5979e998-56c8-4b3b-a15d-dff6c74d01b8_650x617.jpeg)

*[Source](https://www.commitstrip.com/en/2015/11/02/thats-brutal/?)*

***

## Discussion

No replies yet.