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 !