Hibernate version:
3.1.2
Mapping documents:
EmailAddress
Code:
<class name="org.tecas.model.EmailAddress" table="EMAIL_ADDRESS">
<id name="id" column="ID" type="long" unsaved-value="null" >
<generator class="native"/>
</id>
<property name="address" column="ADDRESS" unique="true"/>
<property name="addedDate" column="ADDED_DATE"/>
<property name="confirmed" column="CONFIRMED" type="boolean"/>
<property name="dailySummary" column="DAILY_SUMMARY"/>
<property name="delimiter" column="DELIMITER"/>
<property name="filter" column="FILTER"/>
<list name="emailCodes" cascade="all,delete-orphan" lazy="false" fetch="join">
<key column="EMAIL_ADDRESS_ID"/>
<index column="POS"/>
<one-to-many class="org.tecas.model.EmailCode"/>
</list>
<property name="monthlySummary" column="MONTHLY_SUMMARY"/>
<property name="nextDailySummaryDate" column="NEXT_DAILY_SUMMARY_DATE"/>
<property name="statBlacklistEmails" column="STAT_BLACKLIST_EMAILS"/>
<property name="statConfirmedEmails" column="STAT_CONFIRMED_EMAILS"/>
<property name="statDeletedEmails" column="STAT_DELETED_EMAILS"/>
<property name="statManuallyConfirmedEmails" column="STAT_MANUALLY_CONFIRMED_EMAILS"/>
<property name="statManuallyDeletedEmails" column="STAT_MANUALLY_DELETED_EMAILS"/>
<property name="statStoredEmails" column="STAT_STORED_EMAILS"/>
<property name="statValidCodeEmails" column="STAT_VALID_CODE_EMAILS"/>
<property name="statWhitelistEmails" column="STAT_WHITELIST_EMAILS"/>
<set name="storedEmail" table="STORED_EMAIL" cascade="all,delete-orphan" lazy="true" fetch="join" inverse="true">
<key column="EMAIL_ADDRESS_ID"/>
<one-to-many class="org.tecas.model.StoredEmail"/>
</set>
<property name="storedEmailCount" formula="(select count(*) from STORED_EMAIL storedEmail WHERE storedEmail.EMAIL_ADDRESS_ID = id and storedEmail.deliver = 0)"/>
<many-to-one name="user" column="USER_ID"/>
<property name="weeklySummary" column="WEEKLY_SUMMARY"/>
</class>
Code:
private Long id;
private String address;
private Date addedDate;
private Boolean confirmed;
private User user;
private List<EmailCode> emailCodes;
private Set<StoredEmail> storedEmail;
private Long storedEmailCount;
private String delimiter;
private boolean filter = true;
private boolean dailySummary = false;
private boolean monthlySummary = false;
private boolean weeklySummary = false;
private Date nextDailySummaryDate;
// stats
private Long statBlacklistEmails;
private Long statConfirmedEmails;
private Long statDeletedEmails;
private Long statManuallyConfirmedEmails;
private Long statManuallyDeletedEmails;
private Long statStoredEmails;
private Long statValidCodeEmails;
private Long statWhitelistEmails;
StoredEmail Code:
<class name="org.tecas.model.StoredEmail" table="STORED_EMAIL">
<id name="id" column="ID" type="long" unsaved-value="null">
<generator class="native"/>
</id>
<property name="bodyText" column="BODY_TEXT"/>
<property name="deliver" column="DELIVER"/>
<many-to-one name="emailAddress" column="EMAIL_ADDRESS_ID" not-null="true"/>
<property name="email" column="EMAIL"/>
<property name="senderAddress" column="SENDER_ADDRESS"/>
<property name="size" column="SIZE"/>
<property name="storedDate" column="STORED_DATE"/>
<property name="subject" column="SUBJECT"/>
</class>
Code:
private Long id;
private EmailAddress emailAddress;
private Date storedDate;
private String bodyText;
private String email;
private String senderAddress;
private String subject;
private Integer size;
private boolean deliver = false;
Code between sessionFactory.openSession() and session.close():Code:
Criteria crit = session.createCriteria(EmailAddress.class);
crit.add(Expression.eq("weeklySummary", true));
crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
crit.setFirstResult(firstResult);
crit.setMaxResults(maxResults);
crit.addOrder(Order.asc("id"));
return crit.list();
I have also tried the following HQL:
"from org.tecas.model.EmailAddress emailAddress
left join fetch emailAddress.storedEmail left join fetch emailAddress.emailCodes
where emailAddress.weeklySummary = :weeklySummary order by emailAddress.id"
Name and version of the database you are using:
MySQL 4.1.8
The generated SQL (show_sql=true):
From the Criteria:
Hibernate: select top ? this_.ID as ID0_2_, this_.ADDRESS as ADDRESS0_2_, this_.ADDED_DATE as ADDED3_0_2_, this_.CONFIRMED as CONFIRMED0_2_, this_.DAILY_SUMMARY as DAILY5_0_2_, this_.DELIMITER as DELIMITER0_2_, this_.FILTER as FILTER0_2_, this_.MONTHLY_SUMMARY as MONTHLY8_0_2_, this_.NEXT_DAILY_SUMMARY_DATE as NEXT9_0_2_, this_.STAT_BLACKLIST_EMAILS as STAT10_0_2_, this_.STAT_CONFIRMED_EMAILS as STAT11_0_2_, this_.STAT_DELETED_EMAILS as STAT12_0_2_, this_.STAT_MANUALLY_CONFIRMED_EMAILS as STAT13_0_2_, this_.STAT_MANUALLY_DELETED_EMAILS as STAT14_0_2_, this_.STAT_STORED_EMAILS as STAT15_0_2_, this_.STAT_VALID_CODE_EMAILS as STAT16_0_2_, this_.STAT_WHITELIST_EMAILS as STAT17_0_2_, this_.USER_ID as USER18_0_2_, this_.WEEKLY_SUMMARY as WEEKLY19_0_2_, (select count(*) from STORED_EMAIL storedEmail WHERE storedEmail.EMAIL_ADDRESS_ID = this_.id and storedEmail.deliver = 0) as formula0_2_, emailcodes2_.EMAIL_ADDRESS_ID as EMAIL4_4_, emailcodes2_.ID as ID4_, emailcodes2_.POS as POS4_, emailcodes2_.ID as ID1_0_, emailcodes2_.CODE as CODE1_0_, emailcodes2_.NAME as NAME1_0_, storedemai3_.EMAIL_ADDRESS_ID as EMAIL4_5_, storedemai3_.ID as ID5_, storedemai3_.ID as ID4_1_, storedemai3_.BODY_TEXT as BODY2_4_1_, storedemai3_.DELIVER as DELIVER4_1_, storedemai3_.EMAIL_ADDRESS_ID as EMAIL4_4_1_, storedemai3_.EMAIL as EMAIL4_1_, storedemai3_.SENDER_ADDRESS as SENDER6_4_1_, storedemai3_.SIZE as SIZE4_1_, storedemai3_.STORED_DATE as STORED8_4_1_, storedemai3_.SUBJECT as SUBJECT4_1_ from EMAIL_ADDRESS this_ left outer join EMAIL_CODE emailcodes2_ on this_.ID=emailcodes2_.EMAIL_ADDRESS_ID left outer join STORED_EMAIL storedemai3_ on this_.ID=storedemai3_.EMAIL_ADDRESS_ID where this_.WEEKLY_SUMMARY=? order by this_.ID asc
Question:
So my situation is I may have thousands of EmailAddresses that will be returned from this query so I need to page it and deal with maybe 100 at a time. I also need the class to be populated with at a minimum the storedEmail set, because I can't be executing a load query for the storedEmail set on every one of the EmailAddresses. However I have tried working with .setFirstResult and when I set it to 1 it still returns only the first result rather than the second, and when I try setting firstResult to 0 and maxResults to 5 it only returns one result when there should be 5. I realize this may be a problem with having the joins in the same select, can anyone suggest a way for me to do this?
Question 2:
Assuming someone can help me solve the first question, would it be possible to further restrict the storedEmail set that is fetched for the parent EmailAddress class based on the storedEmail.storedDate property? IE stored within a certain date range?
Thanks!
Login
FAQ
Search
