I've built the Cat example and am using a simple HQL query named "select.all.cats.order.by.weight" which I've stored in the mapping file. Everything works fine but when I turn on the SHOW-SQL flag in the hibernate.cfg.xml file and run my servlet, it appears that hibernate is executing a query for each row in the database. I expected a more efficient query such as "select * from cat", rather than multiple queries.


Here is my log file:
-----------------------------
.
.
.
Hibernate: select cat0_.id as x0_0_ from dbo.cat cat0_ order by cat0_.weight
Hibernate: select cat0_.id as id0_, cat0_.name as name0_, cat0_.sex as sex0_, cat0_.weight as weight0_ from dbo.cat cat0_ where cat0
_.id=?
Hibernate: select cat0_.id as id0_, cat0_.name as name0_, cat0_.sex as sex0_, cat0_.weight as weight0_ from dbo.cat cat0_ where cat0
_.id=?
Hibernate: select cat0_.id as id0_, cat0_.name as name0_, cat0_.sex as sex0_, cat0_.weight as weight0_ from dbo.cat cat0_ where cat0
_.id=?
.
.
.
-----------------

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

<hibernate-mapping package="com.bofa.rmt.core.business">
<class name="Cat" table="dbo.cat">
<!-- A 32 hex character is our surrogate key. It's automatically
generated by Hibernate with the UUID pattern. -->
<id name="id" type="string" unsaved-value="null" >
<column name="id" sql-type="char(32)" not-null="true"/>
<generator class="uuid.hex"/>
</id>

<!-- A cat has to have a name, but it shouldn' be too long. -->
<property name="name">
<column name="name" length="16" not-null="true"/>
</property>
<property name="sex"/>
<property name="weight"/>
</class>

<query name="select.all.cats.order.by.weight">
from Cat as cat
order by cat.weight
</query>

</hibernate-mapping>

-----------------

Is Hibernate actually sending those queries to the database and how can I optimize the HQL to execute a single query? I've also tried native SQL but I'm iterating and native SQL is not compatible with iterating.

Any ideas?

use query.list instead of query.iterate

Thanks. I've changed the mapping as follows:

-------------------------
.
.
.
<!-- query name="select.all.cats.order.by.weight" -->
<!--
from Cat as cat
order by cat.weight
-->
<!-- /query -->

<sql-query name="select.all.cats.order.by.weight">
<return alias="cat" class="Cat"/>
SELECT
{cat.*}
FROM dbo.cat {cat}
ORDER BY {cat.weight}
</sql-query>
.
.
.
-------------------------


Then I changed my iteration loop as follows:

-------------------------
.
.
.
// for (Iterator it = query.iterate(); it.hasNext();) {
for (Iterator it = query.list().iterator(); it.hasNext();) {
Cat cat = (Cat) it.next();
out.println("<tr>\n");
out.println("<td>" + cat.getWeight() + "</td>\n");
out.println("<td>" + cat.getName() + "</td>\n");
out.println("<td>" + cat.getSex() + "</td>\n");
out.println("</tr>\n");
}
.
.
.
-------------------------

It seems faster and I see only one query in the log so I'm assuming that it's doing less work, but I'll need to benchmark it to confirm.

don't forget to turn off log when benchmarking

oh and why don't you write
FROM dbo.cat cat
ORDER BY {cat.weight} ??
hql is easier to read