CODEWITHSUNDEEP
javahibernatehqlselect-n-plus-1
Maxime ARNSTAMM
Maxime ARNSTAMM

Reputation: 5314

Avoid N+1 select with native sqlQuery?

Here's what I have :

Entity A -> oneToMany -> Entity B -> manyToOne -> Entity C

And because I have to do an inner join without foreign keys between A and another entity X, I have to use createSqlQuery and not createQuery. (obviously I can't change the database)

So, all I was able to do is a nice 2N+1 select. (with fetch=EAGER or by hand, it's the same).

Does someone have any idea?

EDIT: with a @BatchSize I reduced the number of selects from A to B. I have now a N+2 select.

EDIT 2: I can't use the inner join (with the comma) because the database is an old DB2, and it crashes.

Upvotes: 0

Views: 729

Answers (3)

Nathanphan
Nathanphan

Reputation: 957

To avoid N+1, you can use the following code in your map field

@Fetch(FetchMode.JOIN)

Hope this will help.

Upvotes: 1

axtavt
axtavt

Reputation: 242786

You can use something like this, but I'm not sure how would it work with complex query:

s.createSQLQuery(
    "SELECT {a.*}, {b.*}, {c.*} " +
    "FROM X x JOIN A a ON ... JOIN B b ON ... JOIN C c ON ...")
    .addEntity(A.class, "a")
    .addJoin(B.class, "a.b")
    .addJoin(C.class, "a.b.c")

See also:

Upvotes: 0

jpkroehling
jpkroehling

Reputation: 14061

Sorry for the vague answer, I really never experienced this. I would try to approach this problem using ResultTransformers:

http://docs.jboss.org/hibernate/core/3.6/javadocs/org/hibernate/transform/ResultTransformer.html

Unfortunately, there's little documentation about it, so, your best option is to look at the test suite and see how it's used.

Upvotes: 0

Related Questions