What is best way to build Entity Attribute Value (EAV) using Hibernate

Hi,

I have a requirement to create REST endpoint for all CRUD operations (Controller, Service, Model & Repository) for an EAV ( Entity Atttribute Value) data model. Please suggest a best way in Spring Data JPA.

DDLs:

CREATE TABLE HM_M_ANY_ENTITY (
  any_entity_id INT NOT NULL PRIMARY KEY,
  any_entity_name VARCHAR(255),
  any_entity_type VARCHAR(255),
  lookup_entity_id INT
)
CREATE TABLE HM_R_SE_ATTRIBUTES (
  ae_id INT NOT NULL REFERENCES HM_M_ANY_ENTITY(any_entity_id),
  ae_attr_id INT NOT NULL,
  ae_attr_name VARCHAR(255),
  ae_attr_type VARCHAR(255),
  ae_attr_family VARCHAR(255),
  ae_attr_constraints VARCHAR(255),
  ae_attr_validation_rules VARCHAR(255),
  --ae_is_unique BOOLEAN,
  --ae_is_nullable BOOLEAN
  PRIMARY KEY (ae_id,ae_attr_id)
)
CREATE TABLE HM_T_SE_ATTR_VALUES (
  ae_id INT REFERENCES HM_M_ANY_ENTITY(any_entity_id),
  ae_attr_id INT ,
  ae_entry_id INT NOT NULL,
  --ae_field_id INT,
  ae_attr_value VARCHAR(255),
  FOREIGN KEY (ae_id, ae_attr_id) REFERENCES HM_R_SE_ATTRIBUTES (ae_id, ae_attr_id),
  PRIMARY KEY(ae_id,ae_attr_id,ae_entry_id)
)

Data
ENTITY

any_entity_id any_entity_name any_entity_type lookup_entity_id
1 Single-location retailers SINGLE NULL
2 Chain stores CHAIN NULL
3 Grocery stores, supermarkets and hypermarkets MART NULL
4 Warehouse stores WAREHOUSE NULL

ATTRIBUTES

ae_id ae_attr_id ae_attr_name ae_attr_type ae_attr_family ae_attr_constraints ae_attr_validation_rules
3 1 grocerystorename VARCHAR
STORE_NAME UNIQUE Alphanumeric and 200 max length
3 2 grocerystorepincode NUMBER STORE_LOCATION NULL NULL
3 3 gorcerystoretype VARCHAR STORE_TYPE NULL NULL
3 4 grocerystoresize NUMBER STORE_CAPACITY NULL NULL
3 5 grocerystorelocation VARCHAR STORE_LOCATION NULL NULL
4 1 warehousestorename VARCHAR STORE_NAME UNIQUE Alphanumeric and 200 max length
4 2 warehousepincode NUMBER STORE_LOCATION NULL NULL
4 3 warehousecapacity` NUMBER STORE_CAPACITY NULL NULL

VALUES

ae_id ae_attr_id ae_entry_id ae_attr_value
3 1 1 peoplegrocerystore
3 2 1 123456
3 3 1 supermarket
3 4 1 2400
3 5 1 NORTH
4 1 1 peoplewarehouse
4 2 1 678901
4 3 1 8000
3 1 2 petstore
3 2 2 888888
3 3 2 petshop
3 4 2 800
3 5 2 WEST

Note: For each entity there could be a set of attributes and for the combination of an Entity & an Attribute there could be a set of values. as like above.

API must support all CRUD operation through REST endpoints via POST, GET, PUT & DELETE ensuring the proper sequence & relationship being maintained.

PS: Even after a DELETE the sequence must be carried out in a MAX(existing SEQUENCE) + 1 way.

Please help on establishing creating JAVA layers comprising all features like OneToMany, Composite Key (Embeddable way if needed), EntityGraph for pulling data without N+1 query problem. I can even share my Java & Spring Boot code built until Entity Layer.

Thank you in advance !

This is an Hibernate forum, not a Spring Data JPA forum - if you have any doubts about Hibernate functionality we’ll be more than happy to help you, but we won’t be able to tell you how to design your application based on a completely separate framework.

One suggestion I could give is to look into the newly implemented Jakarta Data 1.0 specification, with Hibernate-native DAO-style repositories available from version 6.6. Find out more about them here and here.