How do I avoid generating N+1 queries in Hibernate? I am using a simple entity with no relationships. I want to find all Users for a password expiry date <= a given date. I am using P6Spy to check the queries generated and I find N+1 queries being fired.

Here is the DAO code
-------------------------
public Collection findByExpiryDate(Date edate){
ArrayList users = new ArrayList();
Session session = null;
final String sql_str = "from User as user where user.passwordExpiryDate <= :edate";

try {
session = sf.openSession();
Query query = session.createQuery(sql_str);
query.setParameter("edate",edate);
for(Iterator it=query.iterate();it.hasNext();){
User user = (User)it.next();
users.add(user);
}
session.close();
} catch (Exception e) {
System.out.println("Error in findByExpiryDate " + e.getMessage());
e.printStackTrace();
} finally {
try {
session.close();
} catch (Exception e) {
System.out.println("Hibernate Exception " + e.getMessage());
e.printStackTrace();
}
}
return users;
}



Here is the config
--------------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="hibernate.connection.datasource">datasource-Test</property>
<property name="hibernate.dialect">net.sf.hibernate.dialect.OracleDialect</property>
<mapping resource="user.hbm.xml"/>
</session-factory>
</hibernate-configuration>

Here is the mapping
-----------------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.infosys.j2ee.cmptest.model.User" table="UserEJBTable">
<id name="primaryKey">
<generator class="sequence">
<param name="sequence">test2_seq</param>
</generator>
</id>
<property name="userName">
<column name="USERNAME" sql-type="varchar(150)"/>
</property>
<property name="passwordExpired">
<column name="PASSWORDEXPIRED" sql-type="boolean"/>
</property>
<property name="disabled">
<column name="DISABLED" sql-type="boolean"/>
</property>
<property name="password">
<column name="PASSWORD" sql-type="varchar(150)"/>
</property>
<property name="passwordExpiryDate">
<column name="PASSWORDEXPIRYDATE" sql-type="date"/>
</property>

</class>

</hibernate-mapping>


Here is my model
--------------------
package com.infosys.j2ee.cmptest.model;

import java.io.Serializable;
import java.sql.Date;
import java.util.Locale;

public class User implements Serializable {
private String userName;
private String password;
private boolean disabled;
private boolean passwordExpired;
private Date passwordExpiryDate;
private Locale locale;
private Integer primaryKey;

public User() {}

public User(String name, String password, boolean dflag, boolean pflag, Date pdate, Locale locale){
this.userName = name;
this.password = password;
this.disabled = dflag;
this.passwordExpired = pflag;
this.passwordExpiryDate = pdate;
this.locale = locale;
}

public Integer getPrimaryKey() { return this.primaryKey; }
public void setPrimaryKey(Integer key){ this.primaryKey = key;}
public Locale getLocale() { return locale; }
public String getPassword() {return password;}
public Date getPasswordExpiryDate() {return passwordExpiryDate;}
public String getUserName() {return userName;}
public void setLocale(Locale locale) {this.locale = locale;}
public void setPassword(String string) {password = string;}
public void setPasswordExpiryDate(Date date) {passwordExpiryDate = date;}
public void setUserName(String string) {userName = string;}
public boolean isDisabled() {return disabled;}
public boolean isPasswordExpired() {return passwordExpired;}
public void setDisabled(boolean b) {disabled = b;}
public void setPasswordExpired(boolean b) {passwordExpired = b;}
}

Use query.list() instead of query.iterator().