0

My team is looking to use the Hibernate ORM in a new Java server we are developing for our product.

We allow clients to extend our database schema by adding their own custom columns. The columns are added at runtime, and they are added directly to the underlying database table, i.e. the schema is changed. Data specifying the schema changes are stored in a separate table, detailing the affected tables/columns.

Ultimately, we cannot specify the custom columns via JPA annotations because they are not known at compile time, but we still need to CRUD these columns. We would like to use all of Hibernate’s underlying niceties to handle the reads and writes.

What’s the proper way to do this in modern Hibernate? We saw that in the old HBM file format there is a certain that (sort of) meets our needs, but we also understand that HBM is deprecated. We’d appreciate any pointers.

Albert
  • 1
  • Might have to utilize hibernates native sql functionality for your CRUD operations. Store the custom column names in a managed table and create your queries off from those columns. – locus2k Jul 15 '20 at 14:33
  • Look at [dynamic mapping models](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#mapping-model-dynamic). – SternK Jul 15 '20 at 14:41
  • @SternK The documentation there references an HBM file, which we are trying to avoid since they're deprecated. Also, if we were to add a new column to an entity, we would have to regenerate the XML file somehow. Do you know of a way to accomplish the dynamic model mapping without HBM files? – Albert Jul 15 '20 at 15:15
  • @locus2k If that were the case, then we'd want to hook into the SQL generation statements hibernate is building and overwrite them. Otherwise we'd require a additional update statement for the dynamic columns. Do you know where this hook could happen, or have you done it before? – Albert Jul 15 '20 at 15:17
  • @Albert Have you seen [this](https://stackoverflow.com/questions/47330810/jpa-hibernate-dynamic-entity-mapping-persistence-at-runtime)? It looks like this is not solvable task in the hibernate scope. – SternK Jul 15 '20 at 15:38
  • You'd have to write methods that generated the sql yourself as hibernate wouldn't be able to do that. On another note; can you provide an example on how they'd add custom columns? Could their custom columns or data be stored as a blob (not ideal) or some other format or as a reference table some how? – locus2k Jul 15 '20 at 16:24
  • @locus2k We have tables with a natural key then some set of decimal columns that are measures. The users will be able to modify the data model to add a decimal column to one of the tables. Do you think we could still utilize Hibernate's query building for where clauses and such, and just modify the select portion of the SQL? We saw the option to store a single column in the database with JSON structured to hold the dynamic columns (similar to the blob, if I'm understanding correctly) but that would not work with our reporting solution, so it's a no-go, unfortunately. – Albert Jul 15 '20 at 17:28
  • I think what you want might be more complex than what hibernate is able to do. What about giving your customers an API to interface with your software and then you can handle their custom stuff easier within the confides of hibernate constraints? – locus2k Jul 15 '20 at 18:05

0 Answers0