Java EE/JPA: Applying key-value table techniques

Question

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?

score 6 · Accepted Answer · edited May 23 '17 at 12:08

6

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

edited May 23 '17 at 12:08

Community

1
1

answered Feb 20 '15 at 12:58

Zielu

8,312
4
28
41

Thanks a lot for your help. But what about pivoting of the table? I mean look at my query. What when I need, e.g. the entities name, descr, category within a single statement? – John Rumpel Feb 20 '15 at 13:27
The last select in my anwer rertrieves them, as if you want to know which one is than you have to split the select using AS and UNION (probably). But it is JPA it is all about objects. You want to model your object with unknown number of properties, so you do it, but then, you should just fetch the object with all its properties and use the ones you need, not access the properties individually. If it was simply object with many fields, this is what you would do (if you are using java and JPA you should not think too much of underlying DB asn the specific selects) – Zielu Feb 20 '15 at 13:33
Hmmm but lets assume I have 10k objects each referencing 1k (key,value)-pairs. Now I want to create a dynamic table containing only 10 of the 1k keys as columns. Using the Map I would select all entities, fetching all maps from database (having 10 billion entries in memory) and manually 'translate' my query to programmatic selection/checks of the map entries, right? – John Rumpel Feb 20 '15 at 14:13
Well using the Attribue entity way (so no simple string map), you do the select as described above set (WHERE a.name IN :names_of_interest), each attribute has its name and value properties so you know what they are representing and you can construct your dynami table (add ManyToOne on atrritue to ComplexEntity) to know fto which they are belong if you retrieve for more than one. The map is lazy so it wont't be read till needed. And finally if you don't care about the object view of your CoplexEntity, just don't map it at all in JPA and manage the table manualy. – Zielu Feb 20 '15 at 14:21
If you don't mind @Zielu, can you explain where c.id comes from in your query above? I am assuming its an id field we didn't define within ComplexEntity. Is it something I "should" generate? – D-Klotz Jun 30 '16 at 13:50
1

@D-Klotz, the c.id is the id of CompleEntity, all JPA entities have to have an id, it is not included in the example above, but the class ComplexEntity should declare it as well (class ComplexEntity { '@ID' id, '@OneToMany' ...). With OneToMany annotation on attributes value of this id is being used to refer to the ATTR_TABLE. – Zielu Jun 30 '16 at 18:19

Java EE/JPA: Applying key-value table techniques

1 Answers1