-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 
Author Message
 Post subject: Specifying joined fetch in native sql?
PostPosted: Mon May 23, 2005 11:02 am 
Newbie

Joined: Fri Mar 26, 2004 1:01 pm
Posts: 12
Hibernate version: 3.0

I'm using Hibernate3 to fetch some data from a legacy database using a native sql query. The class I'm fetching has the usual pile of properties, and a collection of integers that I'd like to store in an int[] in my Java code. The data from this database is immutable.

What I cannot figure out is how to tell Hibernate that the SQL I've written includes the proper JOIN statements to fetch the integer array in the proper order: I noticed that Hibernate3 includes support for addScalar() and addJoin(), but there's no documentation for them, and I can't figure out how to get them to fetch a joined array of primitives.

Here's my query code:
Code:
            Query query = session.createSQLQuery(
               "select    bp.PSPR_CODE as {details.upc}, " +
               "        bp.PSPR_CODE_TYPE as {details.upcType}, " +                   
               "        v.DIST_DESC as {details.description}, " +               
               "        SZE.SIZE_DESC as {details.unit}, " +
               "       dc.VRNT_QUAN_DPAC as {pack} " +               
               "from    BITM_PSPR bp, " +
               "        DPAC_CONT dc, " +
               "        VRNT v, " +
               "        SZE, " +
               "        STIT " +
               "where   dc.BITM_NUMB_VRNT = bp.BITM_NUMB " +
               "        and dc.BITM_NUMB_VRNT = v.BITM_NUMB " +             
               "        and dc.BITM_NUMB_VRNT = stit.BITM_NUMB " +                  
               "        and stit.SIZE_NUMB = SZE.SIZE_NUMB " +                   
               "        and bp.PSPR_CODE = :upccode");


In the above snippet, the {pack} is the placeholder for my int[], but I'm not sure how to tell Hibernate to map the resulting data from that column to the int[] in my Java class.

Can anybody tell me the proper way to use addJoin, addEntity, etc. to fill in my int[]? Also, do I need to do anything special with my mapping (e.g., use array vs primitive-array vs bag?


Top
 Profile  
 
 Post subject: Re: Specifying joined fetch in native sql?
PostPosted: Thu May 26, 2005 12:14 pm 
Newbie

Joined: Fri Mar 26, 2004 1:01 pm
Posts: 12
Okay, following up to my own post:

Well, I was able to get it to work, but it raises an interesting issue. I ended up modifying my entity to have a different id, such that my collection of primitives was joined to my entity through that key. This is a bit of an inconvenience, since the id column isn't really the PK for the table, but it is the key through which my primitive array is joined.

Once I did that, I was able to get Hibernate to generate the SQL I wanted by using the {pack.*} notation in my select statement. I wish I knew what columns {pack.*} is comprised of for a collection of elements ({pack.key} and {pack.value}, perhaps?), since I'd like to add only the one column to my resultset, but whatever.

In any case, my sql-query ended up looking like this:

Code:
   <sql-query name="getPromotionItemDetails">
      <return alias="details" class="com.foo.entity.PromotionItemDetails"/>
      <return-join alias="pack" property="details.packs"/>
      select   bp.BITM_NUMB as {details.bitmNumb},
            bp.PSPR_CODE as {details.upc},
              bp.PSPR_CODE_TYPE as {details.upcType},            
              v.DIST_DESC as {details.description},            
              SZE.SIZE_DESC as {details.unit},
              {pack.*}
      from    BITM_PSPR bp,
             DPAC_CONT pack,
              VRNT v,
              SZE,
              STIT
      where   bp.BITM_NUMB = v.BITM_NUMB   
              and v.BITM_NUMB = stit.BITM_NUMB            
              and stit.SIZE_NUMB = SZE.SIZE_NUMB               
              and bp.PSPR_CODE = :id
              and pack.BITM_NUMB_VRNT = bp.BITM_NUMB
      order by pack.VRNT_QUAN_DPAC asc
   </sql-query>


I wish I could put the correct alias in the order by clause as well (again, would that be {pack.value}?), but again, it's no big deal.

So through all this I learned:
* You can have native sql queries that join to primitive collections
* You need to specify the primitive in the query as {primitive.*}, which will add two columns to your output
* Joining is pretty easy using <return-join>, you just specify the collection you're joining to through the alias of the entity you're joining from


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 2 posts ] 

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.