I'm having problems getting the createSQLQuery to work with my tables. I have a country table, a state table and a country to state table that maps states to countries (many-to-many).
What I'm trying to do is load all the country objects into memory with all their state collections without using eager fetching because I want to avoid a cartesian product.
My code below keeps on throwing errors and I can't seem to figure out why. I just need someone to point me in the right direction and why what I'm doing is wrong.
Thanks,
Minh
Hibernate version:3.2.1
Mapping documents:
<hibernate-mapping>
<class name="xxx.UserCountry" table="user_country" catalog="xx">
<comment>
</comment>
<id name="userCountryId" type="int">
<column name="user_country_id" />
<generator class="assigned" />
</id>
<property name="countryName" type="string">
<column name="country_name" length="30" not-null="true">
<comment>
</comment>
</column>
</property>
<set name="userProfiles" inverse="true">
<key>
<column name="user_country_id" not-null="true">
<comment>
</comment>
</column>
</key>
<one-to-many class="xxUserProfile" />
</set>
<set name="userStates" inverse="true" table="user_country_to_user_state">
<key>
<column name="user_country_id" not-null="true">
<comment>
</comment>
</column>
</key>
<many-to-many entity-name="xxx.UserState">
<column name="user_state_id" not-null="true">
<comment>
</comment>
</column>
</many-to-many>
</set>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class name="xxx.UserState" table="user_state" catalog="xxx">
<comment>
</comment>
<id name="userStateId" type="int">
<column name="user_state_id" />
<generator class="assigned" />
</id>
<property name="stateName" type="string">
<column name="state_name" length="40" not-null="true">
<comment>
</comment>
</column>
</property>
<property name="stateNameAbbr" type="string">
<column name="state_name_abbr" length="5">
<comment>
</comment>
</column>
</property>
<set name="userProfiles" inverse="true">
<key>
<column name="user_state_id" not-null="true">
<comment>
</comment>
</column>
</key>
<one-to-many class="xxx.UserProfile" />
</set>
<set name="userCountries" inverse="true" table="user_country_to_user_state">
<key>
<column name="user_state_id" not-null="true">
<comment>
</comment>
</column>
</key>
<many-to-many entity-name="xxx..UserCountry">
<column name="user_country_id" not-null="true">
<comment>
</comment>
</column>
</many-to-many>
</set>
</class>
</hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
Code:
private static final String GET_USER_COUNTRY_WITH_STATES =
"select {uc.*}, {us.*} from user_country uc " +
"left join user_country_to_user_state uctus on uc.user_country_id = uctus.user_country_id " +
"left join user_state us on uctus.user_state_id = us.user_state_id";
list = session.createSQLQuery(TEST)
.addEntity("uc", UserCountry.class)
.addJoin("us", "uc.userStates")
.list();
System.out.println(Arrays.toString(stat.getQueries()));
Full stack trace of any exception that occurs:
java.lang.NullPointerException
at org.hibernate.loader.custom.sql.SQLQueryParser.resolveProperties(SQLQueryParser.java:182)
at org.hibernate.loader.custom.sql.SQLQueryParser.resolveCollectionProperties(SQLQueryParser.java:135)
at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:98)
at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:51)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:110)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:43)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:114)
at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:137)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:150)
at xxx.UserCountryModel.getAllUserCountryWithUserState(UserCountryModel.java:49)
at xxx.UserCountryModel.main(UserCountryModel.java:66)
Exception in thread "main" java.lang.NullPointerException
at xxx.UserCountryModel.main(UserCountryModel.java:68)
Name and version of the database you are using:Mysql 5Code: