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"}]
- 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