Problem with sql-query

msenin · **Posted:** Thu Jul 28, 2005 5:30 pm

I have a SQL query that returns several columns, i.e.
instead of

Code:

from com.business.sem.Statistics where ...

I have

Code:

select column1, column2 from Table.

When I specify <sql-query name..><return class="myValueObject">,
Hibernate complains that there's noi persistent entity called myValueObject. As a result, query.list() always returns empty collection.

What I want is:
1. ideally, I'd like Hibernate to generate value object for me based on the column names in the SQL query.
2. if that is not possible, I will provide a Java Bean with properties matching column names in the SELECT, and I'd like Hibernate to use reflection to populate these beans with values when I do query.list()
And I don't want to create <class>-style mapping for these value objects because they are read-only.

Is that possible? If not, what's the best solution to wrap columns in SELECT statement into objects?

Hibernate version: 3.0.5
Mapping documents:

Code:

    <sql-query name="com.business.sem.Statistics.report.for.accountId_dates" cacheable="true" >
       <return alias="StatsReportObject" class="com.business.sem.StatsReportRow" />
       <![CDATA[
      SELECT
         C.id AS {StatsReportObject.campaignId},
         C.vendorIdString AS {StatsReportObject.campaignVID},
         C.name AS {StatsReportObject.campaignName},
         AG.id AS {StatsReportObject.adGroupId},
         AG.vendorIdString AS {StatsReportObject.adGroupVID},
         AG.name AS {StatsReportObject.adGroupName},
         K.searchString AS {StatsReportObject.searchString},
         S.* AS {StatsReportObject.*},
      FROM
         KeywordStats S
         JOIN VendorKeyword VK ON VK.id = S.vendorKeywordId
         JOIN AdGroup AG       ON AG.id = VK.adGroupId
         JOIN Campaign C       ON  C.id = AG.campaignId
         JOIN VendorAccount VA ON VA.id = C.vendorAccountId
      WHERE   VK.status = AG.status
          AND AG.status = C.status
         AND  C.status = 'A'
         AND VA.id = ?
         AND S.startDate >= ?
         AND S.endDate <= ?
       ]]>
    </sql-query>

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:
SQLServer 2000

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

You have to define <class ..> object for Hibernate to do the mapping. You can use <class mutable="false"> to tell hibernate that you dont want to allow update or delete of this object.

Are you actually using Hibernate elsewhere? If you are using SQL and dont want to use XML mappings, then maybe another tool would be better.

Jakarta Commons BeanUtils allows you to auto-populate a bean or a list of beans from a SQL query and wraps away all of the JDBC stuff.
http://jakarta.apache.org/commons/dbutils/

cbredesen · **Posted:** Wed Aug 31, 2005 3:53 pm

Are you aware you can instruct Hibernate to instantiate new unmapped value objects liks such (you do have to provide a suitable constructor, so reflection won't work):

Code:

select new Family(mother, mate, offspr)
from DomesticCat as mother
    join mother.mate as mate
    left join mother.kittens as offspr

This is from Chapter 15.4 of the manual, which is here:

http://www.hibernate.org/hib_docs/v3/re ... hql-select

I'm searching for a solution to another issue and I thought this might help you out. There is also some discussion of this on page 271 of HIA.

-Chris