Define an entity via a custom SQL query in Hibernate 5.1

Question

I'm working with a non normalized 3rd party database meaning I cannot change the schema. I'm trying to map the tables to JPA entities using Hibernate 5.1

There are 2 simple tables A and B:

| A_ID(pk) | | B_ID(pk) | ------------- ------------- | 1 | | 1 | ------------- | 2 | -------------

Table C has a composite primary key and has a Many-To-One relation to Table A:

| A_ID(pk&fk) | QUANTITY(pk) | VALID_FROM(pk) | --------------------------------------------------- | 1 | 1 | 2017-05-21 | | 1 | 1 | 2018-01-01 | | 1 | 2 | 2017-05-21 |

Table D has a composite primary key:

| A_ID(pk&fk) | QUANTITY(pk) | VALID_FROM(pk) | B_ID(pk&fk) | -------------------------------------------------------------------- | 1 | 1 | 2018-01-21 | 1 | | 1 | 2 | 2018-01-21 | 1 | | 1 | 2 | 2018-05-01 | 2 |

the VALID_FROM column is not part of the join condition between the tables and can take up any value.

I'm trying to set up a relation between Table C and D but because of the VALID_FORM primary key component they cannot be modelled with Many-To-One. And since there is no join table they cannot be modelled with Many-To-Many either.

The best solution would be to create a view like

CREATE VIEW C_NORM AS SELECT DISTINCT A_ID, QUANTITY FROM TABLE_C;

which would produce view C_NORM:

 | A_ID(pk&fk) | QUANTITY(pk) | ---------------------------------- | 1 | 1 | | 1 | 2 |

Creating the C_NORM entity on this view could have

a One-To-Many relation with Table C
and another One-To-Many relation with Table D

but I cannot change the schema thus I cannot create a new view.

Is there any way to define an entity as a class with annotations that is basically based on a native SQL query rather than a view or table in the DB?

Simon Martinelli · Accepted Answer · 2018-05-09 14:21:49Z

No that's not possible and it doesn't make sense.

Entities are for update, insert and delete. If you don't want to do any of these operations you shouldn't use entities.

You can use the @SqlResultSetMapping to map a result of a native query to a class

Query q = em.createNativeQuery( "SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS avgOrder " + "FROM Customer c " + "JOIN Orders o ON o.cid = c.id " + "GROUP BY c.id, c.name", "CustomerDetailsResult"); @SqlResultSetMapping(name="CustomerDetailsResult", classes={ @ConstructorResult(targetClass=com.acme.CustomerDetails.class, columns={ @ColumnResult(name="id"), @ColumnResult(name="name"), @ColumnResult(name="orderCount"), @ColumnResult(name="avgOrder", type=Double.class)}) })

Or alternatively use QLRM: https://github.com/simasch/qlrm

sanya · Accepted Answer · 2024-07-30 07:20:39Z

Answering my own question after 6 years: the Hibernate annotation @Subselect is designed exactly to resolve the above problem.

A nice guide can be found on Baeldung

Collectives™ on Stack Overflow

Define an entity via a custom SQL query in Hibernate 5.1

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related