[b]Hibernate version: 3.2.0.cr4[/b]

[b]Mapping documents: None - using JPA implementation[/b]

[b]Code between sessionFactory.openSession() and session.close():[/b]

I am using the following named query:

@NamedQuery(name=Country.COUNTRIES_AS_SELECT_ITEMS,
query="select new javax.faces.model.SelectItem(c, c.name) from Country c")

Which is called from the following method:

public List<SelectItem> getAllCountriesAsSelectItems() {
return entityManager.createNamedQuery(Country.COUNTRIES_AS_SELECT_ITEMS).getResultList();
}

[b]Name and version of the database you are using:Oracle 9.2 and HSQLDB 1.7.3.3 (same results with either database)[/b]

[b]The generated SQL (show_sql=true):[/b]

Hibernate: select country0_.id as col_0_0_, country0_.name as col_1_0_ from Country country0_

Hibernate: select country0_.id as id3_0_, country0_.name as name3_0_ from Country country0_ where country0_.id=?
Hibernate: select country0_.id as id3_0_, country0_.name as name3_0_ from Country country0_ where country0_.id=?
Hibernate: select country0_.id as id3_0_, country0_.name as name3_0_ from Country country0_ where country0_.id=?
Hibernate: select country0_.id as id3_0_, country0_.name as name3_0_ from Country country0_ where country0_.id=?
Hibernate: select country0_.id as id3_0_, country0_.name as name3_0_ from Country country0_ where country0_.id=?

Hibernate: select country0_.id as col_0_0_, country0_.name as col_1_0_ from Country country0_

[b]Comments:[/b]

Assuming that we have 5 countries, calling the method above would result in 7 queries. The 7th is just due to the JavaServer Faces lifecycle, and so I am ignoring that. The first query of course gets all of the ids, and the next 5 queries retrieve the objects by id.

However, if we change the query to:
"select new javax.faces.model.SelectItem(c.name) from Country c" and only select an attribute of country, instead of an attribute of the country and the country entity as well, it will only issue 1 query (or two, due to the JSF lifecycle).

As per issue HHH-544 Select new n+1 queries, Gavin responded that the parser treats all select new calls as shallow, as is manifest in the above example.

[b]What I am wondering is if there is a way to make select new calls non-shallow (avoiding the n+1 database queries), or if it would be a valid feature request?[/b]

Hi, i have the same problem, the only way i could avoid the N+1 queries is
setting
<property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>

in your hibernate.hbm.xml

that will make the query work using the old hibernate 2 query parser, but you loose some functionality that is provided by the AST parser in your queries,

i been trying for a while to migrate a big application from hibernate 2 to hibernate 3 and i have a lot of queries that use that type of query, if this is the only query that you have using select new query change the constructor from you SelectItem class to receive each one of the properties of the Country entity and change the query to pass each property to the constructor.

I been debugging the AST parser to see if i can fix the problem by myself, but is weird that this was working on HB2 and is not on HB3 , I’ll will like to know if some of the dev team could respond why in HB3 the select new calls are shallow ? cause i’m trying to fix it and ill like to know if i should not do it

Gabriel
Note: Sorry if I wrote something wrong, English is not my native language

Max Rydahl Andersen replied to this issue [13/Dec/06 11:23 AM] with the following solution:

createQuery("select c as c, c.name as name from Customer c").setResultTransformer(Transformers.aliasToBean(YourClass.class));

assuming YourClass has a setC and setName bean.