Hi,

I am hoping that this question makes sense...

I was wondering if there is any way of using the setMaxResult and setFirstResult on the Query object (or any other technic) to restrict the number of results returned on the left side of a join instead of the result returned by the inner join itself.

I have a one-to-many mapping (entry to property) and I would like to return n entries while joing entry to property - I have a named query that returns back n rows (I would like n entries):

from Entry e
join fetch e.properties property
where e.accountId = :ac
and lower(e.name.firstName) > lower(:fn)
order by lower(e.name.firstName)

I am not a whiz at SQL but this works:

select entry0_.*, properties1_.*
from entry entry0_
inner join property properties1_
on entry0_.entryId=properties1_.entryId
where entry0_.entryId in (
select entry0_.entryId as entryId0_
from entry entry0_
where entry0_.isCurrent=1
and rownum <= 35
and entry0_.accountId=61
and lower(entry0_.firstName) > lower('%')
);

I guess I could have this as my named query?

Hibernate version:
2.1.7c

Mapping documents:

<class name="Entry"
table="entry"
where="isCurrent=1">

<id
name="entryId"
type="java.lang.Integer"
column="entryId">
</id>

<property
name="accountId"
type="int"
column="accountId"
not-null="true">
<property
name="isCurrent"
type="boolean"
column="isCurrent"
not-null="true"
length="1">

<component name="name"
class="Name">
<property
name="firstName"
type="java.lang.String"
column="firstName"
length="75">
</property>
<property
name="lastName"
type="java.lang.String"
column="lastName"
length="75">
</property>
...
</component>

<set name="properties"
lazy="false"
inverse="true"
cascade="all-delete-orphan">

<key>
<column name="entryId" />
</key>
<one-to-many class="Property"/>
</set>

</class>


Code between sessionFactory.openSession() and session.close():

Query q = session.getNamedQuery(
"getEntriesForAccountLikeNameIgnoreDatasource");

q.setInteger("ac", searchCriteria.getAccountId());
q.setString("fn", searchCriteria.getFirstName());
q.setFirstResult(searchCriteria.getFirstResult());
q.setMaxResults(searchCriteria.getMaxResult());

return q.list();

Name and version of the database you are using:
Oracle 9.0.2

interesting.
No you can't restrict the subquery result.
Use a binded SQL query instead.

Emmanuel,

Thanks for your reply.

Currently I have chosen to use the batch-size to reduce the number of queries against the database while returning the right number of entries. I am not totally happy about this but it kind of does what I want - I did think about using a named query and binding the value but I didn't want to put rownum in the query as we need to support MySQL as well as Oracle.

Unless there is a way I can build a query using HQL that uses something like setMax that gets translated into Oracle:Rownum or MySQL:Limit?

Regards,

Paul