Java EE/JPA: Applying key-value table techniques
database, jakarta-ee, jpa
Solution
Millions of records it normal use case for the database. Just remeber to have index on your ref-id (if it is not already primary key). You may also do table partitioning by your ref-id (supported by almost all DB including MySQL), to improve performance, but it is DB optmimalization not JPA ones.
As for JPA, you can map Map<> value If your properties will be represented as Strings (name, value), then simply
class ComplexEntity {
@ElementCollection
@CollectionTable....
Map<String, String> attributes;
}
....
String descr = entity.getAttributes.get("descr")
see Storing a Map<String,String> using JPA
Or you can define new Entity: Attribute and map to it:
@Entity
class Attribute {
@ID
Long id;
@Column(name="name")
String name;
@Column(name="pvalue")
String value;
}
class ComplexEntity {
@OneToMany(cascade=CascadeType.ALL,orphanRemoval = true)
@MapKey(name="name")
@JoinTable(name = "ATTR_TABLE")
private Map<String,Attribute> attributes;
with this kind of mapping the attributes map keys can be actual field of its values (abover the attributes are indexed by Attribute.name value).
Be carefull and do not call columns/object fields KEY, VALUE, PARAM or any other potential sql/jpql key words as it often does not work in complecated queries but it hard to find the reason (I learn it the hard way).
As for querying, normal JPQL works, so you just grap you complicated entity and then access the interested values from the attributes map. or you query for them
SELECT a.value FROM ComplexEntity c INNER JOIN c.attributes a WHERE c.id = :id AND a.name IN :names
Problem
Using JPA I realize that I have dynamic growing entities, i.e. the number of properties might be vary for an entity. Knowing that there a some solutions based on key-value tables I would be interested in having more information about how to apply that techniques to JPA (JPQL). An example structure would look like: ``` REF-ID KEY VALUE 1000 name foo 1000 category basic 1001 name bar 1001 category advanced 1001 descr none --------------------|------------ PRIMARY | ``` The problem know is that this kind of table might grow enormously, up to millions of records. And the next question is how to map queries, something like ``` SELECT name, category, descr FROM KEYSTORE WHERE id=1000; ``` ... where descr might be available or not Is there any concept that I missing here to fit this requirements when using JPA or do I have to use other techniques here? What about performance?