dimanche 8 mars 2015

Is JPA bad choice to deal with EAV Data models?

I have been trying to use JPA/Hibernate to CRUD operate an EAV data model and have been facing lot of performance hit. I am looking for ideas to efficiently manage EAV data models.



**(A)**So here is an example of what I am trying to deal with, I have three tables :
UserTable
AttributesTable
UserAttributeValueTable

**(B)**And apart from these three, I have other relations like :
ItemTable
UserItemsRelationTable


So basically, AttributesTable is a table that contains all the attributes, may it be User's or Item's. And UserAttributeValueTable is the table that joins UserId, AttributeId, and a value.


Now, let's say I want to pull all the users and their attributes.


Here is how I mapped them :



@Entity
UserTable{
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="USER_ID")
private long userId;

@OneToMany(targetEntity=UserAttributeValueTable.class, mappedBy="userTable", fetch=FetchType.LAZY)
private List<UserAttributeValueTable> userAttributeValues;

//Other Columns

}

@Entity
UserAttributeValueTable{

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="USER_ATTRIBUTE_VALUE_ID")
private long userAttributeValueId;


@Column(name="\"VALUE\"")
private String value;

//bi-directional many-to-one association to AttributesTable
@ManyToOne(targetEntity=AttributesTable .class ,fetch=FetchType.LAZY)
@JoinColumn(name="ATTRIBUTE_ID")
private AttributesTable attributes;

//bi-directional many-to-one association to UserTable
@ManyToOne(targetEntity=UserTable.class,fetch=FetchType.LAZY)
@JoinColumn(name="USER_ID")
@JsonIgnore
private UserTable user;

//Other columns

}


@Entity
AttributeTable{

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="ATTRIBUTE_ID")
private long attrId;

@Column(name="ATTRIBUTE_NAME")
private String attrName;

//Other columns
}


Problems : 1. I ran into an infinite loop when I did a findAll , and that was obvious,so I used @JsonIgnore so that it would ignore the UsersTable. However, to retrieve 300 User's(User Attribute Values), it is taking a lot of time and I am not looking for every column either. All I want is to flatten it this way:



User :[{ id:"1", name:"John", otherAttributes={attr1:"value1",attr2:"value2"},{ id:"2", name:"Joey", otherAttributes={attr1:"value1",attr2:"value2"}]



  1. Mapping complexity, when mapping with other kind of relations like (B)


I just felt after writing simple SQL queries would be more easier in these kind of dynamic models where the column is a not a field instead it is a property. Any suggestions ?


I am using Spring Data JPA.


Aucun commentaire:

Enregistrer un commentaire