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 5

the query above is wrong....this is the query

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";


same error occurs as stated above.

i stand corrected on the exception output....this is the exception thrown:


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)

the exception thrown sounds like a bug to me ...

I'm going to try a hack and turn my many-to-many table into a not so "many-to-many" by adding a pk. I'll then run the hibernate tools to create the pojo and see if doing that makes a difference. If it does, I guess there might be a bug.

I can't get this to work...can anyone help me on this?

Max, do you know why I'm getting this error?

Minh

Sigh.....there is a bug. Sadly, this issue was posted a while ago and only now did I really find an answer. Check the jira issue for createSQLQuery. There is a post by that has a patch. I've tried and it worked. Too bad the the repository gives me an older version of Hibernate to work with so now I'll have to port the src from the 3.2.1ga release over and modify that src.

Hope people don't have to go through the pains I went through.

Minh