I'm a Hibernate newbie and have read documentation, done research, and I think the answer to my question is "No". But I'm asking the experts just in case. Is it possible to do complex native SQL queries using Oracle Analytics and also have an object of a specific type returned? My query below works fine if I define it as returning scalars. But I'd rather not use scalars because the result is returned as an Object array instead of a specific, typed object.

Here is the query that works in Oracle Developer and I define it externally in commandQueries.hbm with 3 variables:
select sector_name as sectorName, sector_nbr as sectorNbr, recorded_on_dt as recordedOnDt,
'cell_soft_handoff_suc_pct' as columnName,
dense_rank() over (order by cell_soft_handoff_suc_pct asc, total_tx_megabytes desc) as rankInCollection
from (select * from bss_daily where
recorded_on_dt = to_date('10/08/2008','MM/DD/YYYY') and total_tx_megabytes >= .01
order by cell_soft_handoff_suc_pct asc, total_tx_megabytes desc)
where rownum < 11 order by cell_soft_handoff_suc_pct asc, total_tx_megabytes desc

This SQL query is external in my commandQueries.hbm and I will use scalars if I have to, appreciate your advice.

Hibernate3.3.1

Oracle 10G

It's actually reasonably easy, if you're willing to fiddle about getting the names right!!! Just use
nativeQuery.addEntity(Entity.class);

As long as the field names are exact, it should work. My example uses annotations however, so not sure how this pans out in standard hibernate.

An example, access code first...



public List jon(){
Session session = (Session) getJpaTemplate().getEntityManagerFactory().createEntityManager().getDelegate();

SQLQuery q = session.createSQLQuery(" select aus_rateplan, aus_description, aus_flag from aus_rateplan where 1 = 1 ");
q.addEntity(AusRateplan.class);

List l = q.list();

System.out.println(l.size());

return l;
}

And AusRateplan....


public class AusRateplan implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "aus_rateplan", nullable = false)
private String ausRateplan;
@Column(name = "aus_description")
private String ausDescription;
@Column(name = "aus_flag", nullable = false)
private char ausFlag;

...etc...


-------
Hope this helps

Thanks for your reply. If anyone has a requirement to do complex native SQL queries, I think it is best then to use the approach you mentioned. Unfortunately, I have already taken an entirely different approach and followed http://www.ibm.com/developerworks/java/ ... icdao.html .

This is approach is really generic and was working wonderfully for me up until the time that my queries became complex. I made all queries external prefixed by "FindBy", and my DAOS are sparse. Looks like I need my traditional DAO back, and a not so generic way of accessing it.

Please note that I spent 2 days researching documentation, googling, and trying different things to get the externalized query in XML form <sql-query> <return class="someClass" alias="someAlias"> to work, and kept getting "column not found". For benefit of all, if anyone has a working example of how to reference native SQL embedded in XML file commandQuery.hbm.xml using H 3.3, please post. My domain class does not even have a property called "rowNum" in it, nor should it. Moving on :)

Map your object with column names that match the column aliases you're returning in your SQL (sectorName, etc.) and use a mapping something like this:



The key is the curly braces around the elements in the select clause.

Hi, I tried a simple native Oracle query using the style in the above example:

<sql-query name="BssDailyStat.findByBetweenRecordedOnDtColumnSectorCount2">
<return class="com.service.persist.domain.BssDailyStat" alias="bss"/>
<![CDATA[select {bss.*} from (select count(*) as nbrOfOccurrences from bss_top_n_statistics where recorded_on_dt >= ? and recorded_on_dt <= ? and column_name = ? and sector_name = ? and sector_nbr = ? and collection_size = ?) bss ]]>
</sql-query>

I got a different error this time, Oracle cannot recognize it:

*********** text of error ************************************

ERROR [main] (JCLLoggerAdapter.java:454) - ORA-00904: "BSS"."RANK_IN_COLLECTION": invalid identifier

org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

**************** end error ***************************

RANK_IN_COLLECTION is a valid column in table which is mapped, but in this particular query I don't care about it.

I'm still not sure how Hibernate would even be able to insert a random property into my domain object BssDailyStat. For example, I could query for the max() on any given item and call it anything, but it seems like unless I have a specific property with that name on the object, it wouldn't work. Thanks again for any insight and comments.

Hibernate will replace the {bss.*} expression with a list of all the columns mapped by the BssDailyStat class. Each inner query (like bss in your example) needs to return a column for every property you've mapped in the BssDailyStat class, even if you don't care about it in this particular query. You can just return null renamed to the column in question if you like, something like this:



Hope this helps,
Oscar

Hi Oscar,
Thanks, your solution worked! I did have to play around with my query quite a bit and wanted to share a few observations on what I found out.

Best regards,
Lynette

(1) I am using database column "total_originated_calls" in my SQL where clause and had no use for it otherwise. But unless I specifically mapped it I got the error "IndexOutOfBoundsException: Remember that ordinal parameters are 1-based! " Took some detective work, that error message was misleading. So that error went away but then I got the error "ORA-00904 "Invalid Identifier". Turns out that I had to explicitly select it in the select clause even though I had no use for it. In native Oracle SQL, you don't have to select a column in a where clause if you don't need it.

(2) It is ok to reference Oracle rownum in the where clause, this worked fine. There is no need to map it at all and you don't have to have it in the select clause.

(3) In some cases what follows the "as" is a Java bean property name, but other times it is an actual Oracle database column name. I had made the mistake in my original query of doing this: select dense_rank() over (order by orig_call_rf_access_fail_pct desc, total_originated_calls desc) as rankInCollection, when what I needed was the acutal Oracle column name.

(4) I had to use an entity-name because I am using the same Javabean to map to 2 different Oracle tables. One table has detail data, another has summary level.

(5) I had to include the id field in my mapping even though I don't use it, this entity is read-only.

(6) For some reason, in Hibernate 3.3 I had to put my external query in a seperate file from the mapping (I kept getting errors if I didn't). I called it CommandQueries.hbm and referenced it in the hibernate config file.

The code below aggregates data from a detail table and populates a JavaBean. It uses Oracle Analytic functions which are nice for statistics.
Later in my code (not shown), I just take the JavaBean and call a simple Hibernate "Create" to add a summary row to a different table.


<sql-query name="BssDailyStat.findByRfAccessFailurePctTopN2">
<return entity-name="BssDailyStatLoader" alias="bss"/>
<![CDATA[select {bss.*} from (select bss_daily_id, total_originated_calls, recorded_on_dt, 'orig_call_rf_access_fail_pct' as column_name, sector_name, sector_nbr, dense_rank() over (order by orig_call_rf_access_fail_pct desc, total_originated_calls desc) as rank_in_collection, count(*) over ( ) as collection_size from (select * from bss_daily where recorded_on_dt = ? and total_originated_calls >= ? order by orig_call_rf_access_fail_pct desc, total_originated_calls desc) where rownum <= ? order by orig_call_rf_access_fail_pct desc, total_originated_calls desc ) bss]]>
</sql-query>

<class name="com.service.persist.domain.BssDailyStat" table="BSS_DAILY" entity-name="BssDailyStatLoader">
<id name="id" column="BSS_DAILY_ID">
<generator class="sequence">
<param name="sequence">bss_daily_seq</param>
</generator>
</id>
<property name="sectorName">
<column name="SECTOR_NAME"/>
</property>
<property name="sectorNbr">
<column name="SECTOR_NBR"/>
</property>
<property name="columnName">
<column name="COLUMN_NAME"/>
</property>
<property name="recordedOnDt">
<column name="RECORDED_ON_DT"/>
</property>
<property name="collectionSize">
<column name="COLLECTION_SIZE"/>
</property>
<property name="rankInCollection">
<column name="RANK_IN_COLLECTION"/>
</property>
<property name="totalOriginatedCalls">
<column name="TOTAL_ORIGINATED_CALLS"/>
</property>
</class>