Greetings!
I had to move to native SQL to utilize some stored functions written on PL/pgSQL that return row data exactly the same way as tables do. I ran into the issues with associations. Since I am using custom SQL for loading it is desirable in my case to eagerly fetch all associations for particular instance of entity. However, every time I try to fetch collection by outer join with native SQL all I get is NPE from SQLQueryParser.
I've built a small application to reproduce the issue.
Entites: Category, Name
Category can have subcategories (many-to-one with itself) and can have one or more items. Each item can be in different categories (many-to-many with link table).
The question: how do eagerly fetch all associations of Category with native SQL? I've tried many variations of SQL's including the one following in "Mapping documents" section, but all of them throw NPE.
Thanks in advance.
Hibernate version:
Hibernate Core 3.3.1.GA
Mapping documents:
Code:
<hibernate-mapping>
<class name="net.whiteants.issuer.model.Category"
table="category">
<id name="id" column="id">
<generator class="identity"/>
</id>
<property name="name" column="name"/>
<many-to-one name="parent" column="parent_id"/>
<list name="items" table="category_item">
<key column="category_id"/>
<list-index column="listindex" base="0"/>
<many-to-many class="net.whiteants.issuer.model.Item" column="item_id"/>
</list>
</class>
<class name="net.whiteants.issuer.model.Item"
table="item">
<id name="id" column="id">
<generator class="identity"/>
</id>
<property name="name" column="name"/>
</class>
<!-- This SQL produces sensible results; but "items" property is fetched lazily (in separate SQL) -->
<!--<sql-query name="GetCategory">
<return alias="category" class="net.whiteants.issuer.model.Category"/>
<return-join alias="parent" property="category.parent"/>
select {category.*},
{parent.*}
from category as category
join category as parent on category.parent_id = parent.id
where category.id = :id
</sql-query>-->
<!-- This SQL does not run at all, NPE is thrown at query parsing stage.-->
<sql-query name="GetCategory">
<return alias="category" class="net.whiteants.issuer.model.Category"/>
<return-join alias="parent" property="category.parent"/>
<return-join alias="item" property="category.items"/>
select {category.*},
{parent.*},
{item.*}
from category as category
join category as parent on category.parent_id = parent.id
left outer join category_item as j on j.category_id = category.id
left outer join item as item on j.item_id = item.id
where category.id = :id
</sql-query>
</hibernate-mapping>
Code:
Session session = HibernateUtil.getCurrentSession();
session.getTransaction().begin();
Object[] tuples = (Object[])session.getNamedQuery("GetCategory").setLong("id",3l).uniqueResult();
Category c = (Category)tuples[0];
System.out.println("Fetched category: " + c);
System.out.println("Parent category: " + c.getParent());
System.out.println("Category items: " + c.getItems());
session.getTransaction().commit();
Full stack trace of any exception that occurs:Code:
Exception in thread "main" java.lang.ExceptionInInitializerError
at net.whiteants.issuer.HibernateUtil.<clinit>(HibernateUtil.java:24)
at net.whiteants.issuer.App.main(App.java:9)
Caused by: java.lang.NullPointerException
at org.hibernate.loader.custom.sql.SQLQueryParser.resolveProperties(SQLQueryParser.java:205)
at org.hibernate.loader.custom.sql.SQLQueryParser.resolveCollectionProperties(SQLQueryParser.java:158)
at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:121)
at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:74)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:133)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:67)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:136)
at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:476)
at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:384)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1327)
at net.whiteants.issuer.HibernateUtil.<clinit>(HibernateUtil.java:21)
... 1 more
Name and version of the database you are using:PostgreSQL 8.3-603 (JDBC3)
The generated SQL (show_sql=true):No SQL available if using uncommented "GetCategory" variant;
otherwise getting following output:
Code:
Hibernate: select category.id as id0_0_, category.name as name0_0_, category.parent_id as parent3_0_0_,
parent.id as id0_1_, parent.name as name0_1_, parent.parent_id as parent3_0_1_
from category as category
join category as parent on category.parent_id = parent.id
where category.id = ?
Fetched category: Second subcategory
Parent category: Simple category
Hibernate: select items0_.category_id as category1_1_, items0_.item_id as item2_1_, items0_.listindex as listindex1_, item1_.id as id2_0_, item1_.name as name2_0_ from category_item items0_ left outer join item item1_ on items0_.item_id=item1_.id where items0_.category_id=?
Category items: [Bar, Bar, Foo, FooBar]
All I need to do is to get rid of second query.
Debug level Hibernate log excerpt:
No Debug information available.
Login
FAQ
Search