Hibernate version:
3.2.4

Mapping documents:
MEMBERSHIP
<hibernate-mapping package="mx.com.diaca.persistent">
<class name="Membership" table="MEMBERSHIP">
<id name="membershipID" type="long" column="MEMBERSHIP_ID">
<generator class="native" />
</id>
<property name="type" type="byte" column="TYPE" />
<many-to-one name="client" column="CLIENT_ID" class="Client" not-null="true" />
<property name="expireDate" type="date" column="EXPIRE_DATE" />
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
1 session.beginTransaction();
2 int b=1;
3 String qry=
4 "select m2.* from "+
5 "(select * from "+
6 " (select * from membership order by expire_date desc) as m1 "+
7 " group by m1.client_id) as m2 "+
8 " where DATEDIFF(m2.EXPIRE_DATE, CURRENT_DATE())<=60 AND DATEDIFF(m2.EXPIRE_DATE, CURRENT_DATE())>0";

9 Query q1 = session.createSQLQuery(qry).addEntity(Membership.class);

10 List mbspCloseToExpire = q1.list();
11 session.getTransaction().commit();

12 for(int i=0; i<mbspCloseToExpire.size(); i++) {
13 Afiliacion m1 = (Membership) mbspCloseToExpire.get(i);
14 System.out.println(" > "+m1.getMembershipID()+": "+m1.getClient().getName()+"; membership expires next: "+m1.getExpireDate());
15 }
16

Name and version of the database you are using:
MySQL 5

Problem?
Hi,
I've a table called Membership where I store client's memberships. Client table contains client's info such as name, address, phone, etc. When a membership has expired or is close to expire, a new one can be requested (created). Old memberships are kept stored. I need to retrieve memberships that are close to expire (within next 60 days); in lines 3-8 I've the query that retrieves me those memberships.

1. Is there a way to have this native sql query into a criteria query?

2. When executing the code above I'm receiving the "Abstract Lazy Initializer" exception (I do commit before iterating through the list), I'm aware of this lazy relationship, so when I commit in line 16 instead of line 11 I don't get any exception and query works ok. How do I can do a "join fetch" in this native query? Btw I need to fecth the "Client" which owns the membership.

Hi,

if I understand you correctly, you can use the SQLCriterion class. After creating the Criteria object you can add following sql restriction:



It could be done - of course - much nicer, but it would works.

best regards,
michi