Hibernate Community • View topic - HQL query returning unexpected type

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

HQL query returning unexpected type

Page 1 of 2

[ 25 posts ]

Go to page 1, 2 Next

Previous topic | Next topic

Author

Message

Scott

Post subject: HQL query returning unexpected type

Posted: Fri Jul 08, 2005 11:37 pm

Beginner

Joined: Sat May 01, 2004 2:44 am
Posts: 32
Location: Brisbane, Australia

Hi all,

I am having a strange problem with a HQL query I am running.

My object model looks (simplified) like this:

Code:

CustomerQuoteRate:
  Customer customer;
  FreightClass freightClass;
  {etc}

The query I am trying to run is as follows:

Code:

       "SELECT DISTINCT cqr.freightClass "
    + "FROM " + CustomerQuotedRate.class.getName() + " AS cqr "
    + "WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) "
    + "AND cqr.freightClass IS NOT NULL ";

This query is supposed to find and return a distinct list of freight classes, where such a freight class exists in the customer quoted rate table, for the customer in question (or the null customer).

Mr problem is, this query actually returns a list of CustomerQuotedRate instances, not a list of FreightClass instances. It is almost like the HQL parser is ignoring the SELECT portion of the query.

Any help would be appreciated.

regards,
Scott Russell

Hibernate version: 3.0.5

Mapping documents:

Code:

    <class name="CustomerQuotedRate" table="oxcqurte">
        <!--cache usage="read-only"/-->
        <cache usage="nonstrict-read-write"/>

        <id name="docNo" column="doc_no" type="integer">
            <generator class="identity"/>
        </id>

        <property name="fromDate"   column="from_date"    type="date"/>
        <property name="expiryDate" column="exp_date"     type="date"/>

        <property name="minimumCharge"  column="min_charge"    type="double-null"/>
        <property name="pickupCharge"   column="pickup_charge" type="double-null"/>
        <property name="deliveryCharge" column="del_charge"    type="double-null"/>

        <property name="glCode"     column="gl_code"   type="string-trimmed"/>
        <property name="deptCode"   column="dept_code" type="string-trimmed"/>

        <many-to-one name="taxCode" column="tax_code" lazy="false"/>

        <property name="updateID"   column="update_id"   type="string-trimmed"/>
        <property name="updateDate" column="update_date" type="date"/>

        <property name="wip"       column="wip"        type="wipType"/>
        <property name="breakFlag" column="break_flag" type="breakFlagType"/>

        <property name="contact" column="contact" type="string-trimmed"/>
        <property name="phone"   column="phone"   type="string-trimmed"/>
        <property name="status"  column="status"  type="string-trimmed"/>
        <property name="negBy"   column="neg_by"  type="string-trimmed"/>
        <property name="quoteDescription" column="quote_desc" type="string-trimmed"/>

        <property name="subbyPercent"  column="subby_percent" type="double-null"/>
        <property name="paymentBase"   column="pmt_base"      type="string-trimmed"/>
        <property name="nominalWeight" column="nominal_wt"    type="double-null"/>
        <property name="factor"        column="factor"        type="double-null"/>

        <many-to-one name="freightClass" column="fr_class"     lazy="false"/>
        <many-to-one name="customer"     column="cust_code"    />
        <many-to-one name="jobType"      column="job_type"     lazy="false"/>
        <many-to-one name="serviceType"  column="service_type" lazy="false"/>
        <many-to-one name="vehicleClass" column="veh_class"    lazy="false"/>

        <many-to-one name="fromLocation" column="from_loc" lazy="false"/>
        <many-to-one name="toLocation"   column="to_loc"   lazy="false"/>

        <property name="fromType" column="from_type" type="string-trimmed"/>
        <property name="toType"   column="to_type"   type="string-trimmed"/>

        <list name="quantityBreaks" table="oxcqurtd" lazy="true" cascade="all">
            <key column="doc_no"/>
            <list-index column="line_no" base="1"/>
            <composite-element class="CQRQuantityBreak">
                <parent name="parent"/>
                <property name="quantityBreak"    column="qty_break"    type="double-null"/>
                <property name="rate"             column="rate"         type="double-null"/>
                <property name="breakDescription" column="break_desc"   type="string-trimmed"/>
                <property name="breakNotes"       column="break_notes"  type="string-trimmed"/>
                <property name="basicCharge"      column="basic_charge" type="double-null"/>
            </composite-element>
        </list>

    </class>

Code between sessionFactory.openSession() and session.close():

Code:

    public List<FreightClass> getFreightClassesForCustomer(String customerCode){
        return getHibernateTemplate().find(findCustomerFreightClassesString, customerCode);
    }

Full stack trace of any exception that occurs: N/A

Name and version of the database you are using: Informix 9.4

The generated SQL (show_sql=true):

Code:

HQL: SELECT DISTINCT cqr.freightClass FROM com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate AS cqr WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) AND cqr.freightClass IS NOT NULL 

SQL: select distinct customerqu0_.doc_no as doc1_, customerqu0_.from_date as from2_11_, customerqu0_.exp_date as exp3_11_, customerqu0_.min_charge as min4_11_, customerqu0_.pickup_charge as pickup5_11_, customerqu0_.del_charge as del6_11_, customerqu0_.gl_code as gl7_11_, customerqu0_.dept_code as dept8_11_, customerqu0_.tax_code as tax9_11_, customerqu0_.update_id as update10_11_, customerqu0_.update_date as update11_11_, customerqu0_.wip as wip11_, customerqu0_.break_flag as break13_11_, customerqu0_.contact as contact11_, customerqu0_.phone as phone11_, customerqu0_.status as status11_, customerqu0_.neg_by as neg17_11_, customerqu0_.quote_desc as quote18_11_, customerqu0_.subby_percent as subby19_11_, customerqu0_.pmt_base as pmt20_11_, customerqu0_.nominal_wt as nominal21_11_, customerqu0_.factor as factor11_, customerqu0_.fr_class as fr23_11_, customerqu0_.cust_code as cust24_11_, customerqu0_.job_type as job25_11_, customerqu0_.service_type as service26_11_, customerqu0_.veh_class as veh27_11_, customerqu0_.from_loc as from28_11_, customerqu0_.to_loc as to29_11_, customerqu0_.from_type as from30_11_, customerqu0_.to_type as to31_11_ from oxcqurte customerqu0_ where (customerqu0_.cust_code is null or customerqu0_.cust_code=?) and (customerqu0_.fr_class is not null)

Debug level Hibernate log excerpt:

Code:

2005-07-09 13:14:38,336 DEBUG [http-8080-Processor25] transaction.JDBCTransaction - begin
2005-07-09 13:14:38,336 DEBUG [http-8080-Processor25] transaction.JDBCTransaction - current autocommit status: true
2005-07-09 13:14:38,336 DEBUG [http-8080-Processor25] transaction.JDBCTransaction - disabling autocommit
2005-07-09 13:14:38,346 DEBUG [http-8080-Processor25] impl.SessionImpl - find: SELECT DISTINCT cqr.freightClass FROM com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate AS cqr WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) AND cqr.freightClass IS NOT NULL 
2005-07-09 13:14:38,406 DEBUG [http-8080-Processor25] engine.QueryParameters - parameters: [DUCATI]
2005-07-09 13:14:38,406 DEBUG [http-8080-Processor25] engine.QueryParameters - named parameters: {}
2005-07-09 13:14:38,416 DEBUG [http-8080-Processor25] ast.QueryTranslatorImpl - parse() - HQL: SELECT DISTINCT cqr.freightClass FROM com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate AS cqr WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) AND cqr.freightClass IS NOT NULL 
2005-07-09 13:14:38,446 DEBUG [http-8080-Processor25] ast.AST - --- HQL AST ---
 \-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  +-[FROM] 'FROM'
    |  |  \-[RANGE] 'RANGE'
    |  |     +-[DOT] '.'
    |  |     |  +-[DOT] '.'
    |  |     |  |  +-[DOT] '.'
    |  |     |  |  |  +-[DOT] '.'
    |  |     |  |  |  |  +-[DOT] '.'
    |  |     |  |  |  |  |  +-[IDENT] 'com'
    |  |     |  |  |  |  |  \-[IDENT] 'itglovebox'
    |  |     |  |  |  |  \-[IDENT] 'smartmove'
    |  |     |  |  |  \-[IDENT] 'tpt'
    |  |     |  |  \-[IDENT] 'rating'
    |  |     |  \-[IDENT] 'CustomerQuotedRate'
    |  |     \-[ALIAS] 'cqr'
    |  \-[SELECT] 'SELECT'
    |     +-[DISTINCT] 'DISTINCT'
    |     \-[DOT] '.'
    |        +-[IDENT] 'cqr'
    |        \-[IDENT] 'freightClass'
    \-[WHERE] 'WHERE'
       \-[AND] 'AND'
          +-[OR] 'OR'
          |  +-[IS_NULL] 'is null'
          |  |  \-[DOT] '.'
          |  |     +-[IDENT] 'cqr'
          |  |     \-[IDENT] 'customer'
          |  \-[EQ] '='
          |     +-[DOT] '.'
          |     |  +-[DOT] '.'
          |     |  |  +-[IDENT] 'cqr'
          |     |  |  \-[IDENT] 'customer'
          |     |  \-[IDENT] 'id'
          |     \-[PARAM] '?'
          \-[IS_NOT_NULL] 'is not null'
             \-[DOT] '.'
                +-[IDENT] 'cqr'
                \-[IDENT] 'freightClass'

2005-07-09 13:14:38,446 DEBUG [http-8080-Processor25] ast.ErrorCounter - throwQueryException() : no errors
2005-07-09 13:14:38,446 DEBUG [http-8080-Processor25] antlr.HqlSqlBaseWalker - query() << begin, level = 1
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromElement - FromClause{level=1} :  com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate (cqr) -> customerqu0_
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr -> customerqu0_.doc_no
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - getDataType() : freightClass -> org.hibernate.type.ManyToOneType(com.itglovebox.smartmove.tpt.ref.FreightClass)
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - dereferenceShortcut() : property freightClass in com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate does not require a join.
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr.freightClass -> customerqu0_.fr_class
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr -> customerqu0_.doc_no
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - getDataType() : customer -> org.hibernate.type.ManyToOneType(com.itglovebox.smartmove.ar.Customer)
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - dereferenceShortcut() : property customer in com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate does not require a join.
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr.customer -> customerqu0_.cust_code
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr -> customerqu0_.doc_no
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - getDataType() : customer -> org.hibernate.type.ManyToOneType(com.itglovebox.smartmove.ar.Customer)
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - dereferenceShortcut() : property id in com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate does not require a join.
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - Unresolved property path is now 'customer.id'
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr.customer -> customerqu0_.cust_code
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - getDataType() : customer.id -> org.hibernate.type.CustomType@13aac5f
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr.customer.id -> customerqu0_.cust_code
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr -> customerqu0_.doc_no
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - getDataType() : freightClass -> org.hibernate.type.ManyToOneType(com.itglovebox.smartmove.tpt.ref.FreightClass)
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.DotNode - dereferenceShortcut() : property freightClass in com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate does not require a join.
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.FromReferenceNode - Resolved :  cqr.freightClass -> customerqu0_.fr_class
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] antlr.HqlSqlBaseWalker - query() : finishing up , level = 1
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.HqlSqlWalker - processQuery() :  ( SELECT ( {select clause} DISTINCT ( customerqu0_.fr_class customerqu0_.doc_no freightClass ) ) ( FromClause{level=1} oxcqurte customerqu0_ ) ( WHERE ( AND ( OR ( is null ( customerqu0_.cust_code customerqu0_.doc_no customer ) ) ( = ( customerqu0_.cust_code ( customerqu0_.cust_code customerqu0_.doc_no customer ) id ) ? ) ) ( is not null ( customerqu0_.fr_class customerqu0_.doc_no freightClass ) ) ) ) )
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] ast.JoinProcessor - Using FROM fragment [oxcqurte customerqu0_]
2005-07-09 13:14:38,456 DEBUG [http-8080-Processor25] antlr.HqlSqlBaseWalker - query() >> end, level = 1
2005-07-09 13:14:38,466 DEBUG [http-8080-Processor25] ast.AST - --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (oxcqurte)
    +-[SELECT_CLAUSE] SelectClause: '{select clause}'
    |  +-[DISTINCT] SqlNode: 'DISTINCT'
    |  +-[DOT] DotNode: 'customerqu0_.doc_no as doc1_' {propertyName=freightClass,dereferenceType=ROOT_LEVEL,propertyPath=freightClass,path=cqr.freightClass,tableAlias=customerqu0_,className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate,classAlias=cqr}
    |  |  +-[ALIAS_REF] IdentNode: 'customerqu0_.doc_no' {alias=cqr, className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate, tableAlias=customerqu0_}
    |  |  \-[IDENT] IdentNode: 'freightClass' {originalText=freightClass}
    |  \-[SQL_TOKEN] SqlFragment: 'customerqu0_.from_date as from2_11_, customerqu0_.exp_date as exp3_11_, customerqu0_.min_charge as min4_11_, customerqu0_.pickup_charge as pickup5_11_, customerqu0_.del_charge as del6_11_, customerqu0_.gl_code as gl7_11_, customerqu0_.dept_code as dept8_11_, customerqu0_.tax_code as tax9_11_, customerqu0_.update_id as update10_11_, customerqu0_.update_date as update11_11_, customerqu0_.wip as wip11_, customerqu0_.break_flag as break13_11_, customerqu0_.contact as contact11_, customerqu0_.phone as phone11_, customerqu0_.status as status11_, customerqu0_.neg_by as neg17_11_, customerqu0_.quote_desc as quote18_11_, customerqu0_.subby_percent as subby19_11_, customerqu0_.pmt_base as pmt20_11_, customerqu0_.nominal_wt as nominal21_11_, customerqu0_.factor as factor11_, customerqu0_.fr_class as fr23_11_, customerqu0_.cust_code as cust24_11_, customerqu0_.job_type as job25_11_, customerqu0_.service_type as service26_11_, customerqu0_.veh_class as veh27_11_, customerqu0_.from_loc as from28_11_, customerqu0_.to_loc as to29_11_, customerqu0_.from_type as from30_11_, customerqu0_.to_type as to31_11_'
    +-[FROM] FromClause: 'FROM' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[cqr], fromElementByTableAlias=[customerqu0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'oxcqurte customerqu0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=cqr,role=null,tableName=oxcqurte,tableAlias=customerqu0_,colums={,className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate}}
    \-[WHERE] SqlNode: 'WHERE'
       \-[AND] SqlNode: 'AND'
          +-[OR] SqlNode: 'OR'
          |  +-[IS_NULL] SqlNode: 'is null'
          |  |  \-[DOT] DotNode: 'customerqu0_.cust_code' {propertyName=customer,dereferenceType=ROOT_LEVEL,propertyPath=customer,path=cqr.customer,tableAlias=customerqu0_,className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate,classAlias=cqr}
          |  |     +-[ALIAS_REF] IdentNode: 'customerqu0_.doc_no' {alias=cqr, className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate, tableAlias=customerqu0_}
          |  |     \-[IDENT] IdentNode: 'customer' {originalText=customer}
          |  \-[EQ] SqlNode: '='
          |     +-[DOT] DotNode: 'customerqu0_.cust_code' {propertyName=id,dereferenceType=4,propertyPath=customer.id,path=cqr.customer.id,tableAlias=customerqu0_,className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate,classAlias=cqr}
          |     |  +-[DOT] DotNode: 'customerqu0_.cust_code' {propertyName=id,dereferenceType=ROOT_LEVEL,propertyPath=customer.id,path=cqr.customer,tableAlias=customerqu0_,className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate,classAlias=cqr}
          |     |  |  +-[ALIAS_REF] IdentNode: 'customerqu0_.doc_no' {alias=cqr, className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate, tableAlias=customerqu0_}
          |     |  |  \-[IDENT] IdentNode: 'customer' {originalText=customer}
          |     |  \-[IDENT] IdentNode: 'id' {originalText=id}
          |     \-[PARAM] SqlNode: '?'
          \-[IS_NOT_NULL] SqlNode: 'is not null'
             \-[DOT] DotNode: 'customerqu0_.fr_class' {propertyName=freightClass,dereferenceType=ROOT_LEVEL,propertyPath=freightClass,path=cqr.freightClass,tableAlias=customerqu0_,className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate,classAlias=cqr}
                +-[ALIAS_REF] IdentNode: 'customerqu0_.doc_no' {alias=cqr, className=com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate, tableAlias=customerqu0_}
                \-[IDENT] IdentNode: 'freightClass' {originalText=freightClass}

2005-07-09 13:14:38,466 DEBUG [http-8080-Processor25] ast.ErrorCounter - throwQueryException() : no errors
2005-07-09 13:14:38,466 DEBUG [http-8080-Processor25] ast.QueryTranslatorImpl - HQL: SELECT DISTINCT cqr.freightClass FROM com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate AS cqr WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) AND cqr.freightClass IS NOT NULL 
2005-07-09 13:14:38,466 DEBUG [http-8080-Processor25] ast.QueryTranslatorImpl - SQL: select distinct customerqu0_.doc_no as doc1_, customerqu0_.from_date as from2_11_, customerqu0_.exp_date as exp3_11_, customerqu0_.min_charge as min4_11_, customerqu0_.pickup_charge as pickup5_11_, customerqu0_.del_charge as del6_11_, customerqu0_.gl_code as gl7_11_, customerqu0_.dept_code as dept8_11_, customerqu0_.tax_code as tax9_11_, customerqu0_.update_id as update10_11_, customerqu0_.update_date as update11_11_, customerqu0_.wip as wip11_, customerqu0_.break_flag as break13_11_, customerqu0_.contact as contact11_, customerqu0_.phone as phone11_, customerqu0_.status as status11_, customerqu0_.neg_by as neg17_11_, customerqu0_.quote_desc as quote18_11_, customerqu0_.subby_percent as subby19_11_, customerqu0_.pmt_base as pmt20_11_, customerqu0_.nominal_wt as nominal21_11_, customerqu0_.factor as factor11_, customerqu0_.fr_class as fr23_11_, customerqu0_.cust_code as cust24_11_, customerqu0_.job_type as job25_11_, customerqu0_.service_type as service26_11_, customerqu0_.veh_class as veh27_11_, customerqu0_.from_loc as from28_11_, customerqu0_.to_loc as to29_11_, customerqu0_.from_type as from30_11_, customerqu0_.to_type as to31_11_ from oxcqurte customerqu0_ where (customerqu0_.cust_code is null or customerqu0_.cust_code=?) and (customerqu0_.fr_class is not null)
2005-07-09 13:14:38,516 DEBUG [http-8080-Processor25] ast.ErrorCounter - throwQueryException() : no errors
2005-07-09 13:14:38,516 DEBUG [http-8080-Processor25] def.AbstractFlushingEventListener - flushing session
2005-07-09 13:14:38,516 DEBUG [http-8080-Processor25] def.AbstractFlushingEventListener - processing flush-time cascades

Top

jweiskotten

Post subject:

Posted: Sat Jul 09, 2005 12:57 am

Beginner

Joined: Fri Jul 08, 2005 12:38 pm
Posts: 41
Location: Massachusetts, USA

It's doing the right thing, based on what you're asking for in the SQL.

Code:

"SELECT DISTINCT cqr.freightClass "
+ "FROM " + CustomerQuotedRate.class.getName() + " AS cqr "
+ "WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) "
+ "AND cqr.freightClass IS NOT NULL ";

This is going to give you back a CustomerQuotedRate, because that's the table in your FROM clause.

If you want the freight classes, you can do something like this with a subquery:

Code:

SELECT * FROM FreightClass WHERE freightClass IN 
(
  SELECT cqr.freightClass 
      FROM CustomerQuotedRate.class.getName() cqr
      WHERE (cqr.customer IS NULL OR cqr.customer.id = ?)
)

Note that I removed the DISTINCT keyword because that won't do anything but slow down your query in this case. This probably won't work verbatim, but it's one path that will work.

Top

Scott

Post subject:

Posted: Sat Jul 09, 2005 1:11 am

Beginner

Joined: Sat May 01, 2004 2:44 am
Posts: 32
Location: Brisbane, Australia

Below is quoted from the hibernate documentation.

Code:

15.4. The select clause

 The select clause picks which objects and properties to return in the query result set. Consider: 

select mate 
from Cat as cat 
    inner join cat.mate as mate

The query will select mates of other Cats. Actually, you may express this query more compactly as: 

select cat.mate from Cat cat

Queries may return properties of any value type including properties of component type: 

select cat.name from DomesticCat cat
where cat.name like 'fri%'

My query in essence is no different to 'select cat.mate from Cat cat'. In the analogy, this would be returning cats and not cat.mates as requested.

The point here is that the from clause only indicates the table/type to select from. If no select element is specified, then the default is to return the whole object instance. But the documentation indicates that one can select individual fields from within the instance, rather than the whole instance itself. And I have done so successfully in the past, albeit with Hibernate 2.1. Hence why I cannot understand that such a seemingly simple query is acting differently to my expectation.

-Scott

Top

gavin

Post subject:

Posted: Sat Jul 09, 2005 3:50 am

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

I must admit I find this extremely difficult to believe.

Top

Scott

Post subject:

Posted: Sat Jul 09, 2005 4:06 am

Beginner

Joined: Sat May 01, 2004 2:44 am
Posts: 32
Location: Brisbane, Australia

gavin wrote:

I must admit I find this extremely difficult to believe.

Your right, I wrote all that logging output myself because I was bored and had nothing better to do ;-)

No, really, what part don't you believe? If you look at the logging info above that was generated, you will see the HQL and the SQL that has been generated from it.

Here is the relevant section:

Code:

HQL: SELECT DISTINCT cqr.freightClass FROM com.itglovebox.smartmove.tpt.rating.CustomerQuotedRate AS cqr WHERE (cqr.customer IS NULL OR cqr.customer.id = ?) AND cqr.freightClass IS NOT NULL 
 
SQL: select distinct customerqu0_.doc_no as doc1_, customerqu0_.from_date as from2_11_, customerqu0_.exp_date as exp3_11_, customerqu0_.min_charge as min4_11_, customerqu0_.pickup_charge as pickup5_11_, customerqu0_.del_charge as del6_11_, customerqu0_.gl_code as gl7_11_, customerqu0_.dept_code as dept8_11_, customerqu0_.tax_code as tax9_11_, customerqu0_.update_id as update10_11_, customerqu0_.update_date as update11_11_, customerqu0_.wip as wip11_, customerqu0_.break_flag as break13_11_, customerqu0_.contact as contact11_, customerqu0_.phone as phone11_, customerqu0_.status as status11_, customerqu0_.neg_by as neg17_11_, customerqu0_.quote_desc as quote18_11_, customerqu0_.subby_percent as subby19_11_, customerqu0_.pmt_base as pmt20_11_, customerqu0_.nominal_wt as nominal21_11_, customerqu0_.factor as factor11_, customerqu0_.fr_class as fr23_11_, customerqu0_.cust_code as cust24_11_, customerqu0_.job_type as job25_11_, customerqu0_.service_type as service26_11_, customerqu0_.veh_class as veh27_11_, customerqu0_.from_loc as from28_11_, customerqu0_.to_loc as to29_11_, customerqu0_.from_type as from30_11_, customerqu0_.to_type as to31_11_ from oxcqurte customerqu0_ where (customerqu0_.cust_code is null or customerqu0_.cust_code=?) and (customerqu0_.fr_class is not null) 

You can see from the comparison of these two (both of which were appear following each other in the log output) that the HQL is selecting

cqr.freightClass,

whereas the SQL is selecting

customerqu0_.doc_no as doc1_, customerqu0_.from_date as from2_11_, customerqu0_.exp_date as exp3_11_, customerqu0_.min_charge as min4_11_, (etc, etc)

I would have expected something more like:
select distinct customerqu0_.fr_class as fr23_11_ from oxcqurte customerqu0_ where (customerqu0_.cust_code is null or customerqu0_.cust_code=?) and (customerqu0_.fr_class is not null)

... and then the hibernate engine would have looked in the second level cache for all the freight class objects matching the returned codes (because the association is mapped with lazy="false" and the FreightClass class is stored in the second level cache)

So, am I doing something wrong here? To me this HQL query appears to be very close in form and structure to the 'select cat.mate from Cat cat' example quoted in the documentation.

-Scott

Top

eagle79

Post subject:

Posted: Sat Jul 09, 2005 10:29 am

Senior

Joined: Tue Jun 21, 2005 10:18 am
Posts: 135
Location: South Carolina, USA

Isn't the type of Cat.mate Cat? So in essense, you're still selecting a Cat. But in your example, you're looking for a different type (FreightClass) than the type in the From (CustomerQuotedRate).

But then, I haven't read most of the HQL stuff yet (newbie), so maybe I'm wrong.

Top

jweiskotten

Post subject:

Posted: Sat Jul 09, 2005 5:08 pm

Beginner

Joined: Fri Jul 08, 2005 12:38 pm
Posts: 41
Location: Massachusetts, USA

eagle79 wrote:

Exactly.

The type of object returned by the query is based on what table/class you specify in the outermost "from" clause -- not the columns.

How would Hibernate know to construct a FreightClass object when you are only getting data from the CustomerQuotedRate table?

Top

eagle79

Post subject:

Posted: Sat Jul 09, 2005 5:31 pm

Senior

Joined: Tue Jun 21, 2005 10:18 am
Posts: 135
Location: South Carolina, USA

I can understand the original poster's confusion, though... Intuitively, in the following statement:

Code:

customer.freightClass FROM CustomerQuotedRate cqr

It seems like I asked for a FreightClass object, and got something very different. In SQL, we would have been asking for an element of the table in the FROM clause, and we would have gotten that element.

But then, the conceptual mistake there is probably that I am not working with SQL, and HQL has semantics that are quite different.

What I cannot understand is the poster's attitude.

Quote:

Your right, I wrote all that logging output myself because I was bored and had nothing better to do ;-)

This may have been said in jest, but even if so, it was mean-spirited. I doubt we'll see Gavin on this thread again, and in that, the poster has lost a very valuable and knowlegeable resource. This is why the hibernate folks are so frustrated with the forums -- thanks a lot, Scott

Top

jweiskotten

Post subject:

Posted: Sat Jul 09, 2005 5:40 pm

Beginner

Joined: Fri Jul 08, 2005 12:38 pm
Posts: 41
Location: Massachusetts, USA

Quote:

This may have been said in jest, but even if so, it was mean-spirited. I doubt we'll see Gavin on this thread again, and in that, the poster has lost a very valuable and knowlegeable resource.

Hey, those Aussies are always at each other's throats!

Top

Scott

Post subject:

Posted: Sat Jul 09, 2005 8:36 pm

Beginner

Joined: Sat May 01, 2004 2:44 am
Posts: 32
Location: Brisbane, Australia

eagle79 wrote:

What I cannot understand is the poster's attitude.

Quote:

Your right, I wrote all that logging output myself because I was bored and had nothing better to do ;-)

Actually, I thought I made it quite clear I was joking by the smiley wink afterwards.

Besides which, how do you think I feel when I go to the trouble of describing a problem I'm having, and posting all of that logging info etc, only to get at best a flippant throw-away comment by Gavin dismissing my problem ?

gavin wrote:

I must admit I find this extremely difficult to believe.

That's useful, isn't it? That helps me resolve my problem. Giving reasons why he doesn't believe me might have been more useful.

I have been using Hibernate for over a year now on a number of different projects, and I rarely post on the forums because I try to resolve problems myself, by looking in the documentation and in the source code. So if I do go to the trouble of posting, its because I cannot resolve a seeming inconsistency between the documentation and what my code is doing, or I cannot comprehend enough of the Hibernate internals to resolve it myself. Sure, I can understand why Hibernate folks are frustrated with the forums, when people ask very basic "help me do this" questions when the answers are clearly available in the documentation. But that doesn't mean I appreciate the attitude when I post a reasonable question.

The documentation implies that one can reduce a inner join query on a many-to-one association to a simple select query for an entity's field:

Code:

select mate
from Cat as cat
    inner join cat.mate as mate

The query will select mates of other Cats. Actually, you may express this query more compactly as: 

select cat.mate from Cat cat

jweiskotten wrote:

The type of object returned by the query is based on what table/class you specify in the outermost "from" clause -- not the columns.

How would Hibernate know to construct a FreightClass object when you are only getting data from the CustomerQuotedRate table?

In my case, the FreightClass association in the CustomerQuotedRate mapping is explicitly marked lazy="false", and the FreightClass instances themselves are cached in the second level cache. So I was expecting HQL to select the FreightClass (fr_class) column from the CustomerQuotedRate (oxcqurte) table, and automatically resolve the object type the same way it resolves any other many-to-one association, by looking in the session, or by using a proxy, or looking in the second-level cache, or performing a select to load the referenced instance. If I had been loading CustomerQuotedRate instances in this query, the HQL engine would still have had to resolve the freightClass many-to-one association in some fashion, as described before. It seemed reasonable therefore to only ask for the field value itself be returned, rather than the entire entity.

regards,
Scott

Top

eagle79

Post subject:

Posted: Sat Jul 09, 2005 9:48 pm

Senior

Joined: Tue Jun 21, 2005 10:18 am
Posts: 135
Location: South Carolina, USA

Quote:

how do you think I feel when I go to the trouble of describing a problem I'm having, and posting all of that logging info etc, only to get at best a flippant throw-away comment by Gavin dismissing my problem ?

So you respond to a flippant attitude with a flippant comment of your own?

While I would say that Gavin can at times be... er... well, rude (sorry, Gavin, but it's true), that's beside the point. If you respond to rudeness with more rudeness, the usual response is an escalation of incivility.

However, in this case, I have to ask why you assume gavin's response was dismissive? You said:

Quote:

the documentation indicates that one can select individual fields from within the instance, rather than the whole instance itself. And I have done so successfully in the past, albeit with Hibernate 2.1. Hence why I cannot understand that such a seemingly simple query is acting differently to my expectation.

Obviously, there's something in your assertion Gavin doesn't agree with. Considering Gavin had a significant part in writing the documentation (he literally (co)wrote the book on Hibernate), as well as (I assume) a significant portion of the source code itself, don't you think he might have a better understand of how it all works than you do? In your credit, at least you did ask what exactly Gavin was objecting to. But doing so without blowing him off might just have made him a bit more receptive to working with you. Your response begged for one of two responses 1) argue with you or 2)give up and ignore the thread. Given Gavin's recent choice to "open" the forums, I would imagine he's chosen option 2.

Perhaps I am reading too much into things and misrepresenting Gavin's position, but I doubt it. And if I am, I fully invite Gavin to correct my post.

If I were to answer your question, however,

Quote:

I cannot understand that such a seemingly simple query is acting differently to my expectation

I have already expressed solidarity with you on how intuitively, I would have expected something different. But I try to remember that HQL != SQL, and what I expect may reflect a deeper misunderstanding of what HQL is on my own part. Swallow your ego for a moment, and consider the possibility that your "expectation" is simply wrong.

Top

Scott

Post subject:

Posted: Sat Jul 09, 2005 10:47 pm

Beginner

Joined: Sat May 01, 2004 2:44 am
Posts: 32
Location: Brisbane, Australia

eagle79 wrote:

I think this is probably going further than the comment warranted. I put in a wink smiley after the comment to indicate not to take it seriously, and these smileys are intended to indicate such subtleties of speech that cannot be expressed in text. And the point re the flippant comment was that I would have appreciated a more informative response. Enough said.

Quote:

However, in this case, I have to ask why you assume gavin's response was dismissive?

'Cause he didn't supply any information to say why he thought that way.

Quote:

Well, as I said before, the comment wasn't intended to be taken seriously. I think you have misinterpreted it as 'blowing him off'.

Quote:

Swallow your ego for a moment, and consider the possibility that your "expectation" is simply wrong.

Clearly my expectation is wrong, I accept that, since it conflicts with the reality of what is happening in application. I'm not stupid enough to argue with Gavin over a product he authored and is thoroughly expert in, give me some credit. But this long conversation about forum manners hasn't really helped enlighten me as to why the expectation was wrong.

Anyway, I assume based on all this that the situation re HQL is -
You can SELECT individual value type fields, or individual entity type fields (where that entity field is of the same type as in the FROM clause) from an entity in the FROM clause without requiring a join.

regards,
Scott

Top

jweiskotten

Post subject:

Posted: Sat Jul 09, 2005 10:59 pm

Beginner

Joined: Fri Jul 08, 2005 12:38 pm
Posts: 41
Location: Massachusetts, USA

Scott wrote:

jweiskotten wrote:

(Back on topic...)

I understand the confusion, and why you expect that result. It's just not the way I understand Hibernate to work (and not the way I would expect or hope it to).

I'm relatively new to Hibernate though, so I could be wrong.

Were you able to get anywhere with my earlier suggestion?[/code]

Top

eagle79

Post subject:

Posted: Sat Jul 09, 2005 11:01 pm

Senior

Joined: Tue Jun 21, 2005 10:18 am
Posts: 135
Location: South Carolina, USA

Quote:

Enough said.

Hmm... agreed. I've probably stuck my nose a bit further into how you relate with Gavin than I should. I do apologize for that.

Quote:

You can SELECT individual value type fields, or individual entity type fields (where that entity field is of the same type as in the FROM clause) from an entity in the FROM clause without requiring a join.

That is the impression I get as well, though, like I said, I don't fully understand why HQL is conceptually set up like this. But I sincerely hope that I will after reading ch. 7 of HIA :-)

Top

Scott

Post subject:

Posted: Sat Jul 09, 2005 11:19 pm

Beginner

Joined: Sat May 01, 2004 2:44 am
Posts: 32
Location: Brisbane, Australia

jweiskotten wrote:

Were you able to get anywhere with my earlier suggestion?

Quoted below:

Quote:

If you want the freight classes, you can do something like this with a subquery:

Code:

SELECT * FROM FreightClass WHERE freightClass IN 
(
  SELECT cqr.freightClass 
      FROM CustomerQuotedRate.class.getName() cqr
      WHERE (cqr.customer IS NULL OR cqr.customer.id = ?)
)

In the end, to get around the problem, I accepted the List of CustomerQuotedRates returned from the query, and manually retrieved each FreightClass into a LinkedHashSet to distinct them. Not as elegant as I would have liked, but it did the trick. And at least I'll know to be aware of potential problems with this sort of query in the future.

-Scott

Top

Page 1 of 2

[ 25 posts ]

Go to page 1, 2 Next

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum