Hibernate with H2 - setFirstResult()/setMaxResults() not LIMIT/OFFSET

[marque]

Hello!

I do not know if this is the correct forum..

I use Hibernate with H2 and JPA. If I create a JPA Query and use the
Methods setFirstResult() and setMaxResults() this will not mirrored in
the query as LIMIT and OFFSET as I would expect. And the query (with
LEFT JOIN FETCH) is very slow in this case. If I do not use FETCH it
is fast and if I have a criteria that reduce the resultset it is fast,
too.

Is it possible to reflect the methods in LIMIT and OFFSET at SQL query
level?

best regards, marque

[Thomas Mueller]

Hi,

> I do not know if this is the correct forum..

I don't think, but I'm not sure.

> I use Hibernate with H2 and JPA. If I create a JPA Query and use the
> Methods setFirstResult() and setMaxResults() this will not mirrored in
> the query as LIMIT and OFFSET as I would expect.

How does the query look like, and what do you expect?

> Is it possible to reflect the methods in LIMIT and OFFSET at SQL query
> level?

I don't understand - what do you mean with reflect?

Regards,
Thomas

[marque]

Hello!

> How does the query look like, and what do you expect?

The query generated from hibernate is:

select distinct task0_.id as id6_0_, unit2_.id as id5_1_,
additional4_.id as id1_2_, additional6_.id as id1_3_, name8_.id as
id1_4_, descriptio10_.id as id1_5_, history12_.id as id0_6_,
task0_.action as action6_0_, task0_.allocated as allocated6_0_,
task0_.creation as creation6_0_, task0_.expiration as expiration6_0_,
task0_.locked as locked6_0_, task0_.module as module6_0_,
task0_.process as process6_0_, task0_.serverURL as serverURL6_0_,
task0_.testMode as testMode6_0_, task0_.timeoutError as
timeout11_6_0_, task0_.wait as wait6_0_, unit2_.name as name5_1_,
unit2_.type as type5_1_, units1_.Task_id as Task1_0__,
units1_.units_id as units2_0__, additional4_.language as language1_2_,
additional4_.index as index3_2_, additional4_.name as name3_2_,
additional4_.value as value4_2_, additional3_.Task_id as Task1_1__,
additional3_.additionalStringFields_id as addition2_1__,
additional6_.language as language1_3_, additional6_.index as
index3_3_, additional6_.name as name3_3_, additional6_.value as
value8_3_, additional5_.Task_id as Task1_2__,
additional5_.additionalNumberFields_id as addition2_2__,
name8_.language as language1_4_, name8_.name as name2_4_,
names7_.Task_id as Task1_3__, names7_.names_id as names2_3__,
descriptio10_.language as language1_5_, descriptio10_.description as
descript1_7_5_, descriptio9_.Task_id as Task1_4__,
descriptio9_.descriptions_id as descript2_4__, history12_.target as
target0_6_, history12_.timestamp as timestamp0_6_, history12_.type as
type0_6_, history12_.user as user0_6_, histories11_.Task_id as
Task1_5__, histories11_.histories_id as histories2_5__ from Task
task0_ left outer join Task_Unit units1_ on task0_.id=units1_.Task_id
left outer join Unit unit2_ on units1_.units_id=unit2_.id left outer
join Task_AdditionalStringField additional3_ on
task0_.id=additional3_.Task_id left outer join AdditionalStringField
additional4_ on additional3_.additionalStringFields_id=additional4_.id
left outer join Task_AdditionalNumberField additional5_ on
task0_.id=additional5_.Task_id left outer join AdditionalNumberField
additional6_ on additional5_.additionalNumberFields_id=additional6_.id
left outer join Task_Name names7_ on task0_.id=names7_.Task_id left
outer join Name name8_ on names7_.names_id=name8_.id left outer join
Task_Description descriptio9_ on task0_.id=descriptio9_.Task_id left
outer join Description descriptio10_ on
descriptio9_.descriptions_id=descriptio10_.id left outer join
Task_History histories11_ on task0_.id=histories11_.Task_id left outer
join History history12_ on histories11_.histories_id=history12_.id
where task0_.action=?

And my (JPA) query before was:

SELECT DISTINCT xTask FROM Task xTask LEFT JOIN FETCH xTask.units
xunits LEFT JOIN FETCH xTask.additionalStringFields
xadditionalStringFields LEFT JOIN FETCH xTask.additionalNumberFields
xadditionalNumberFields LEFT JOIN FETCH xTask.names xnames LEFT JOIN
FETCH xTask.descriptions xdescriptions LEFT JOIN FETCH
xTask.histories xhistories WHERE xTask.action=:f0


what I expect is a an offset and limit at the end, because I have used
(where q is my query)

Query query = entityManager.createQuery(q);
...
query = query.setFirstResult(10);
query = query.setMaxResults(5);

I would expect, that in the hibernate query a limit and offset will be
added. But it seems, that a full resultset will be requested and only
the operations on the resultset will reflect the setFirstResult()/
setMaxResults().

I read, that in other DBs this will be done:

http://www.java-forum.org/data-tier/71021-java-persistence-api-query-setfirstresult-query-setmaxresults-intern.html

best regards, marque

[Thomas Mueller]

Hi,

The H2 dialect (org.hibernate.dialect.H2Dialect) supports this
option... What dialect do you use?

Regards,
Thomas

[marque]

Hi!

> The H2 dialect (org.hibernate.dialect.H2Dialect) supports this
> option... What dialect do you use?

I use the H2 Dialect. My persistence.xml:

<persistence-unit name="Task" transaction-type="RESOURCE_LOCAL">
<properties>
<property name="hibernate.connection.driver_class"
value="org.h2.Driver"/>
<property name="hibernate.connection.url" value="jdbc:h2:/home/
mp/javadev/src/com/inubit/ibis/test/ibis_root/log/dbh2/
tasksHibernate;AUTO_SERVER=TRUE"/>
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value=""/>
<property name="hibernate.dialect"
value="org.hibernate.dialect.H2Dialect"/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.hbm2ddl.auto" value="create"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>
</properties>
</persistence-unit>

regards, marc.

[Thomas Mueller]

Hi,

Could you try with a different dialect? For example HSQLDB or
PostgreSQL. In most cases, this should work. If this works, then it
could be a problem of the H2 dialect, and I would like to fix it.
Could you post a simple, standalone test case that reproduces the
problem? It would be great if the test case does not have any
dependencies except Hibernate and the H2 jar file.

Regards,
Thomas

[marque]

Hi Thomas!

I have found that the problem is in the combination with join, which
is not possible in hibernate:

http://java.dzone.com/articles/hibernate-tuning-queries-using?page=0,1

So nothing wrong with H2 or the dialect. Sorry for this and thanks for
the great H2.

regards, marc.