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


Full stack trace of any exception that occurs:
GRAVE: could not initialize proxy - no Session
org.hibernate.LazyInitializationException: could not initialize proxy - no Session
at org.hibernate.proxy.AbstractLazyInitializer.initialize(AbstractLazyInitializer.java:57)
...
... usual stuff related to lazy initialization


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.

I've to questions:
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). But if 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 native query? Btw I need to fecth the "Client" which owns the membership.