Annotation to join all columns into a map

[lorenzbaier]

An annotation which joins all columns of the given table and loads them into the map eagerly by using an sql join.
The columns of the joined table might change during runtime.

@JoinAllColumns(table = "book_properties") Map<String, Object> properties;

[beikov]

This is not really possible with SQL, because Hibernate ORM needs to know the names and types of the columns. What you can do easily though, is to use a JSON or XML type.

@JdbcTypeCode(SqlTypes.JSON)
Map<String, Object> properties;

which will use JSONB or Jackson for serialization, whichever is on your classpath.

[yrodiere]

Alternatively, if you can list all the columns at compilation time, you can map an entity to a java.util.Map using an XML mapping definition: https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#dynamic-model

[lorenzbaier]

I cant really use a JSON column because i need sorting and filtering and those properties can potentially change at runtime.

I would need to restart the service to load the new XML Mapping which might be an option if other options fail. Will i need to define all Mappings via XML or just the new properties because i already have the JPA annotations?

Right now i am evaluating jOOQ for querying which gives me the amount of freedom i need with a bit more overhead because i need to write the whole queries.

[yrodiere]

FWIW you can run native SQL queries with JPA/Hibernate: https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#sql

(jOOQ is fine if you need it of course)

[lorenzbaier]

Yes i know i can run raw sql, that's what i did initially in order to create the dynamic "book_properties" view i want to join. But i wanted some help generating the select statements hence choosing jOOQ. (I need to support Postgres, Oracle and SQLServer...)

[beikov]

I cant really use a JSON column because i need sorting and filtering and those properties can potentially change at runtime.

Guess what, you can query parts of the JSON document with functions and operate on that data just like on relational data ;)

You can use e.g. the json_value function to access state: select json_value(t.properties, '$.myDynamicColumn') from MyEntity t