# Native query with calculated column is used all operations

**URL:** <https://discourse.hibernate.org/t/native-query-with-calculated-column-is-used-all-operations/8634>\
**Category:** Hibernate ORM\
**Created:** [November 30, 2023, 1:46pm UTC](https://discourse.hibernate.org/t/native-query-with-calculated-column-is-used-all-operations/8634 "2023-11-30T13:46:07Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![sweet\_patato](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@sweet\_patato](https://discourse.hibernate.org/u/sweet_patato)\
**Post date:** [November 30, 2023, 1:46pm UTC](https://discourse.hibernate.org/t/native-query-with-calculated-column-is-used-all-operations/8634/1 "2023-11-30T13:46:07Z")

</div>

Hello, I am using spring data jpa and hibernate 6.2 to interact with my tables and I have a specific case which I cannot solve.

I am using a native query to select records and I would like in that native query to also include a calculated column. Only that select query has this addition. In any other case and actions I would like to ignore it.

What I have tried to far:  
I extended the query with my calculated value (I’m using spring repository and the `@Query` annotation for that) and I added a new field in my @Entity class.

This immediately creates problems since when I try to insert this new field in the class, is not mapped to a real column, only my custom native query uses it. I added the `@Column(insertable = false, updatable = false)` annotation but when I do a deleteAll() again it fails as this field doesn’t exist in the select (hibernate performs a select and then deletes the records.)

I tried the `@Formula` and again selects fail since that field is not mapped to a column.

I tried the `@SqlResultSetMapping` as described [here](https://stackoverflow.com/questions/36704516/how-to-handle-an-extra-field-in-a-named-native-query-in-hibernate) but in my case I am using spring and not running queries the “manual” way.

At the end my final solution I think is to create another entity same as the old with this additional field and only use this single native query.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [November 30, 2023, 2:40pm UTC](https://discourse.hibernate.org/t/native-query-with-calculated-column-is-used-all-operations/8634/2 "2023-11-30T14:40:59Z")

</div>

Please share the entity mapping and the SQL expression that you want to use for this additional calculated value.  
Whenever you introduce another field, all your native queries that try to materialize this entity will have to have a select item named like the persistent attribute, so you would have to extend all SQL to e.g. `select t.*, null as myCalculatedField from my_table t ...` to make it possible to read the entity.  
A better alternative would be that you use a DTO mapping e.g.

```java
List<MyDto> list = session.createNativeQuery("select t.id, t.name, my_calculation() from my_table t")
    .setTupleTransformer( (tuple, aliases) -> new MyDto( (Long) tuple[0], (String) tuple[1], (BigDecimal) tuple[2] ) )
    .list();

```

If you can use HQL instead of SQL, you can also use the constructor syntax:

```java
List<MyDto> list = session.createQuery("select new MyDto(t.id, t.name, my_calculation()) from MyEntity t")
    .list();

```

Or if you prefer a fully declarative way you can use [Blaze-Persistence Entity-Views](https://github.com/Blazebit/blaze-persistence#entity-view-usage).