Native query with calculated column is used all operations

Hibernate ORM
1

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 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.

2

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.

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:

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.