OneToOne JPA / Hibernate eager loading cause N+1 select
- by Alexandre Lavoie
I created a method to have multilingual text on different objects without creating field for each languages or tables for each objects types.
Now the only problem I've got is N+1 select queries when doing a simple loading.
Tables schema :
CREATE TABLE `testentities` (
`keyTestEntity` int(11) NOT NULL,
`keyMultilingualText` int(11) NOT NULL,
PRIMARY KEY (`keyTestEntity`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
CREATE TABLE `common_multilingualtexts` (
`keyMultilingualText` int(11) NOT NULL auto_increment,
PRIMARY KEY (`keyMultilingualText`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;
CREATE TABLE `common_multilingualtexts_values` (
`languageCode` varchar(5) NOT NULL,
`keyMultilingualText` int(11) NOT NULL,
`value` text,
PRIMARY KEY (`languageCode`,`keyMultilingualText`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
MultilingualText.java
@Entity
@Table(name = "common_multilingualtexts")
public class MultilingualText implements Serializable
{
private Integer m_iKeyMultilingualText;
private Map<String, String> m_lValues = new HashMap<String, String>();
public void setKeyMultilingualText(Integer p_iKeyMultilingualText)
{
m_iKeyMultilingualText = p_iKeyMultilingualText;
}
@Id
@GeneratedValue
@Column(name = "keyMultilingualText")
public Integer getKeyMultilingualText()
{
return m_iKeyMultilingualText;
}
public void setValues(Map<String, String> p_lValues)
{
m_lValues = p_lValues;
}
@ElementCollection(fetch = FetchType.EAGER)
@CollectionTable(name = "common_multilingualtexts_values", joinColumns = @JoinColumn(name = "keyMultilingualText"))
@MapKeyColumn(name = "languageCode")
@Column(name = "value")
public Map<String, String> getValues()
{
return m_lValues;
}
public void put(String p_sLanguageCode, String p_sValue)
{
m_lValues.put(p_sLanguageCode,p_sValue);
}
public String get(String p_sLanguageCode)
{
if(m_lValues.containsKey(p_sLanguageCode))
{
return m_lValues.get(p_sLanguageCode);
}
return null;
}
}
And it is used like this on a object (having a foreign key to the multilingual text) :
@Entity
@Table(name = "testentities")
public class TestEntity implements Serializable
{
private Integer m_iKeyEntity;
private MultilingualText m_oText;
public void setKeyEntity(Integer p_iKeyEntity)
{
m_iKeyEntity = p_iKeyEntity;
}
@Id
@GeneratedValue
@Column(name = "keyEntity")
public Integer getKeyEntity()
{
return m_iKeyEntity;
}
public void setText(MultilingualText p_oText)
{
m_oText = p_oText;
}
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "keyText")
public MultilingualText getText()
{
return m_oText;
}
}
Now, when doing a simple HQL query : from TestEntity, I get a query selecting TestEntity's and one query for each MultilingualText that need to be loaded on each TestEntity. I've searched a lot and found absolutely no solutions. I have tested :
@Fetch(FetchType.JOIN)
optional = false
@ManyToOne instead of @OneToOne
Now I am out of idea!
