I need to store dynamic properties (definition can change at runtime) for multiple other entities.
My basic data model:
- "property" table with columns (id, property_name, entity_name, property_type)
- "property_value" table (id, entity_id, property_id, value) // for now value is a String
Now i create a materialized view (using native SQL) for each entity.
For Example:
- in the "property" table there are entries (1, "test", "Book", "String"), (2, "testNum", "Book", "Number")
- the view looks like
select pv."entity_id" as "id",
cast(max(case when p."name" = 'test' then pv.value else null end) as Varchar(20)) as "test",
cast(max(case when p."name" = 'testNum' then pv.value else null end) as Integer) as "test",
from property p
left join property_value pv on pv.property_id = p.id
where p.entity_name = 'Book'
group by pv.entity_id
When i have a JPA Entity like Book
@Entity
class Book {
Long id;
String name;
@Transient
Map<String, Object> properties;
}
I want the spring boot JPARepository/Hibernate to join the properties and put those into the Map. The sql could look like
select b.id, b.name, b_p.test, b_p.testNum
from book b
left join book_properties b_p on b.id = b_p.id
Then the Map should have entries for "test" and "testNum" with the appropriate column value.
I there any way i can tell Hibernate to do that without losing all the other benefits. I really don't want to write native queries for paging and sorting and filtering...
The sorting should also work on the properties.
If only there was a JPA/Hibernate Annotation like
@JoinAllColumns(table = "book_properties")
Map<String, Object> properties;
which always joins the given table (native sql table name) and puts all column values into the map.
