javahibernatejpahibernate-mapping

Define an entity via a custom SQL query in Hibernate 5.1

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

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?

Solution

  • 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