Using a NamedQuery with a compsite class

MikeyJudd · **Joined:** Mon Oct 06, 2008 10:40 am **Posts:** 12

I have the following class defined in my mapping XML file:

Code:

<class name="com.data.StateRefData" table="STATE_REF">
   <composite-id name="primaryKey" class="com.data.StateRefPkData"> 
       <key-property name="countryCode">
           <column name="COUNTRY_CODE"  />
        </key-property>

        <key-property name="state">
            <column name="STATE"  />
        </key-property>
    </composite-id>
   
   <property name="dialingCode" column="DIALING_CODE"></property>
   <property name="isActive" column="IS_ACTIVE"></property>
   <property name="localeCode" column="LOCALE_CODE"></property>
   <property name="stName" column="ST_NAME"></property>
</class>

I am trying to define a NameQuery for this class. What I tried was:

Code:

<query name="findState">
    <![CDATA[ from
      com.data.StateRefData
        WHERE primaryKey = :primaryKey
    ]]>
 </query>

The Java code that I use to call this is:

Code:

Query stateQuery = null;
List<StateRefData> stateList = null;
StateRefPkData primaryKey = new StateRefPkData();
StateRefData filter = new StateRefData();

primaryKey.setCountryCode(inCountryCode);
primaryKey.setState(inStateProvince);
filter.setPrimaryKey(primaryKey);
         
stateQuery = session.getNamedQuery("findState");
stateQuery.setProperties(filter);
stateList = stateQuery.list();

However, I get these errors right at the "stateQuery.list()" part:

Code:

[ERROR JDBCExceptionReporter:72] Line 1: Incorrect syntax near ','.
org.hibernate.exception.SQLGrammarException: could not execute query

        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1596)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

Any idea on how to use a NamedQuery with a composite?

mmerder · **Posted:** Tue Feb 03, 2009 4:50 pm

Don't wrap the primary key in your object, put in the query directly:

Code:

stateQuery.setParameter("primaryKey", primaryKey);

MikeyJudd · **Joined:** Mon Oct 06, 2008 10:40 am **Posts:** 12

I changed the Java code to:

Code:

stateQuery = session.getNamedQuery("findState");
stateQuery.setParameter("primaryKey", primaryKey);
stateList = stateQuery.list();

And I am still getting the same error. Am I doing it wrong?

mmerder · **Posted:** Tue Feb 03, 2009 4:58 pm

Try to change your query:

Code:

from com.data.StateRefData d
        WHERE d.primaryKey = :primaryKey 

MikeyJudd · **Joined:** Mon Oct 06, 2008 10:40 am **Posts:** 12

Man, I guess this was more tough than I thought :(

This did not work either.

mmerder · **Posted:** Tue Feb 03, 2009 5:08 pm

could you turn on logging of sql and org.hibernate.hql.ast.AST and post the relevant parts?

MikeyJudd · **Joined:** Mon Oct 06, 2008 10:40 am **Posts:** 12

I am a n00b apparently... how do I do that?

mmerder · **Posted:** Wed Feb 04, 2009 10:40 am

Put a log4j.properties into the root of your classpath as described here.
It could look like this:

Code:

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n

### set log levels - for more verbose logging change 'info' to 'debug' ###

log4j.rootLogger=warn, stdout

log4j.logger.org.hibernate=warn


### log HQL query parser activity
log4j.logger.org.hibernate.hql.ast.AST=all

### log just the SQL
log4j.logger.org.hibernate.SQL=debug

### log JDBC bind parameters ###
#log4j.logger.org.hibernate.type=warn
log4j.logger.org.hibernate.type=warn

### log schema export/update ###
log4j.logger.org.hibernate.tool.hbm2ddl=warn

### log HQL parse trees
#log4j.logger.org.hibernate.hql=debug

MikeyJudd · **Joined:** Mon Oct 06, 2008 10:40 am **Posts:** 12

Here she is...

Code:

[DEBUG AST:223] --- HQL AST ---
 \-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  \-[FROM] 'from'
    |     \-[RANGE] 'RANGE'
    |        +-[DOT] '.'
    |        |  +-[DOT] '.'
    |        |  |  +-[DOT] '.'
    |        |  |  |  +-[DOT] '.'
    |        |  |  |  |  +-[IDENT] 'com'
    |        |  |  \-[IDENT] 'data'
    |        |  \-[IDENT] 'StateRefData'
    |        \-[ALIAS] 'd'
    \-[WHERE] 'WHERE'
       \-[EQ] '='
          +-[DOT] '.'
          |  +-[IDENT] 'd'
          |  \-[IDENT] 'primaryKey'
          \-[COLON] ':'
             \-[IDENT] 'primaryKey'

[DEBUG AST:193] --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (STATE_REF)
    +-[SELECT_CLAUSE] SelectClause: '{derived select clause}'
    |  +-[SELECT_EXPR] SelectExpressionImpl: 'statere0_.COUNTRY_CODE as COUNTRY1_, statere0_.STATE as STATE' {FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=d,role=null,tableName=STATE_REF,tableAlias=statere0_,colums={,className=com.data.StateRefData}}}
    |  \-[SQL_TOKEN] SqlFragment: 'statere0_.DIALING_CODE as DIALING3_60_, statere0_.IS_ACTIVE as IS4_60_, statere0_.LOCALE_CODE as LOCALE5_60_, statere0_.ST_NAME as ST6_60_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[d], fromElementByTableAlias=[statere0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'STATE_REF statere0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=d,role=null,tableName=STATE_REF,tableAlias=statere0_,colums={,className=com.data.StateRefData}}
    \-[WHERE] SqlNode: 'WHERE'
       \-[EQ] SqlNode: '='
          +-[DOT] DotNode: '(statere0_.COUNTRY_CODE, statere0_.STATE)' {propertyName=primaryKey,dereferenceType=2,propertyPath=primaryKey,path=d.primaryKey,tableAlias=statere0_,className=com.data.StateRefData,classAlias=d}
          |  +-[ALIAS_REF] IdentNode: '(statere0_.COUNTRY_CODE, statere0_.STATE)' {alias=d, className=com.data.StateRefData, tableAlias=statere0_}
          |  \-[IDENT] IdentNode: 'primaryKey' {originalText=primaryKey}
          \-[NAMED_PARAM] SqlNode: '?'

mmerder · **Posted:** Wed Feb 04, 2009 11:25 am

hmm, doesn't help much...
Have you tried to rename "primaryKey", maybe its a keyword?

MikeyJudd · **Joined:** Mon Oct 06, 2008 10:40 am **Posts:** 12

Yes. I changed "primaryKey" to "statePK". Still does not work.

I changed my Java Code to:

Code:

filter.setStatePK(primaryKey);
stateQuery = session.getNamedQuery("findState");
stateQuery.setParameter("statePK", primaryKey);

and then I changed the XML to:

Code:

<class name="com.data.StateRefData" table="STATE_REF">
   <composite-id name="statePK" class="com.sematree.ecs.data.StateRefPkData"> 
       <key-property name="countryCode">
           <column name="COUNTRY_CODE"  />
        </key-property>

        <key-property name="state">
            <column name="STATE"  />
        </key-property>
    </composite-id>
   
   <property name="dialingCode" column="DIALING_CODE"></property>
   <property name="isActive" column="IS_ACTIVE"></property>
   <property name="localeCode" column="LOCALE_CODE"></property>
   <property name="stName" column="ST_NAME"></property>
</class>

<query name="findState">
    <![CDATA[ from 
       com.data.StateRefData d 
       WHERE d.statePK = :statePK
    ]]>
</query>