CODEWITHSUNDEEP
sqlhibernatejpajboss
John Moore
John Moore

Reputation: 161

JPA generates additional sql's for ManyToOne eager mapping

I have a java object query (hibernate jpa2) which runs fine in JBoss 7.1

select s FROM Sense s where .... etc

this SQL appears in the Jboss console:

select
sense0_.id as id12_,
sense0_.domain as domain12_,
sense0_.lemma as lemma12_,
sense0_.part_of_speech as part5_12_,
sense0_.sense_number as sense2_12_
from sense sense0_
where ...

it's correct

but, in the Jboss console appears also plenty of query related with 'Sense' attributes, these fields are the references to other entities, such as Domain, Word, PartOfSpeach.

select domain0_.id as id5_0_, domain0_.description as descript2_5_0_,
domain0_.name as name5_0_ from domain ....
select word0_.id as id19_2_, word0_.id_lexicon as id3_19_2_ from word
word0_ ....
select partofspee0_.id as id9_2_, domains1_.id_pos as id1_9_4_ from
part_of_speech partofspee0 .....

code of entity Sense mapping:

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="domain", referencedColumnName="id", nullable = false)
private Domain domain;

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="lemma", referencedColumnName="id", nullable = false)
private Word lemma;

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="part_of_speech", referencedColumnName="id", nullable = false)
private PartOfSpeech partOfSpeech;

Why JPA generates these additional SQLs for mapped references field? Those fields have EAGER fetch type but I expect that I should get only one SQL (the first one with Sense) and with left joins with other EAGER entities. In this way when I'm asking for many Sense objects I get plenty of useless SQLs which takes plenty of time to execute.

UPDATE: Changing to LAZY helped, but leaving LAZY is not what I want - this would force me to change all of my dao's code.

Upvotes: 0

Views: 1220

Answers (1)

V G
V G

Reputation: 19002

If you want to solve the problem, change a bit you query, in which you make yourself the INNER/LEFT JOINS in the query, something like:

SELECT s FROM Sense s INNER JOIN FETCH s.domain LEFT JOIN FETCH s.word where...

Use Inner/LEFT joins depending on your entity relationships (mandatory/optional).

Some explanations: EAGER FETCH means that those relationships are also fetched, before the entity is returned to your code, but that does not mean it makes a single query.

Upvotes: 1

Related Questions