Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Hibernate mapping for sql join

Reply
Thread Tools

Hibernate mapping for sql join

 
 
Peter Horlock
Guest
Posts: n/a
 
      03-11-2011
Hi,

I have a sql statement joining over various tables. For performance
reasons (~100000 results) I want to fill the direct result into a
Pojo, without referencing any other tables / Pojos. If possible, I
would like to go without HQL.
The simplified statement looks like that:
select e.name, d.depName from employee e join deparment d on (e.depNo
= d.depNo).
The simplied Pojo looks like this:

MyPojo
- employeeName
- deparmentName

How can I fill results of several joined tables into the same Pojo?
Currently I am using
<return-scalar column="name" type="string"/>
<return-scalar column="depName" type="string"/>

To improve performance I want to use a ScrollableResult, which again I
want to encapsulate into a Iterable, so that I can easily iterate over
its elements. However this will only work on a pojo not on a set of
single values.

Any idea how I could get this done?

Thanks,

Peter
 
Reply With Quote
 
 
 
 
Arved Sandstrom
Guest
Posts: n/a
 
      03-11-2011
On 11-03-11 05:54 AM, Peter Horlock wrote:
> Hi,
>
> I have a sql statement joining over various tables. For performance
> reasons (~100000 results) I want to fill the direct result into a
> Pojo, without referencing any other tables / Pojos. If possible, I
> would like to go without HQL.
> The simplified statement looks like that:
> select e.name, d.depName from employee e join deparment d on (e.depNo
> = d.depNo).
> The simplied Pojo looks like this:
>
> MyPojo
> - employeeName
> - deparmentName
>
> How can I fill results of several joined tables into the same Pojo?
> Currently I am using
> <return-scalar column="name" type="string"/>
> <return-scalar column="depName" type="string"/>
>
> To improve performance I want to use a ScrollableResult, which again I
> want to encapsulate into a Iterable, so that I can easily iterate over
> its elements. However this will only work on a pojo not on a set of
> single values.
>
> Any idea how I could get this done?
>
> Thanks,
>
> Peter


Consider a JPQL constructor expression. It's intended for the use case
of filling up bespoke POJOS (not entities) with result sets like yours.
I've done exactly what you are talking about in EclipseLink, for the
same reason - seeing as how JPQL ctor expressions are by the spec, not
provider-specific.

AHS
--
The user's going to pick dancing pigs over security every time.
-- Bruce Schneier

 
Reply With Quote
 
 
 
 
Peter Horlock
Guest
Posts: n/a
 
      03-11-2011
Thanks,

however, could you be a bit more specific?
Throwing this at Google:
http://www.google.de/search?q=JPQL+c...ient=firefox-a
Gives me its hugh reference:
http://openjpa.apache.org/builds/1.2...a_langref.html
However doesn't (easily) answer my problem at hand.
Scanning the ref, it seems like the jpa is just another way of doing
sql in (hibernate) not using hql.
How does that solve my problem that I want a plain and flat(!) pojo to
be filled from different tables?

Thanks,

Peter
 
Reply With Quote
 
Tom Anderson
Guest
Posts: n/a
 
      03-11-2011
On Fri, 11 Mar 2011, Peter Horlock wrote:

> however, could you be a bit more specific?
> Throwing this at Google:
> http://www.google.de/search?q=JPQL+c...ient=firefox-a
> Gives me its hugh reference:
> http://openjpa.apache.org/builds/1.2...a_langref.html


This might be marginally preferable:

http://openjpa.apache.org/builds/lat...ef_constructor

> However doesn't (easily) answer my problem at hand.


Huh? The example in the document you posted is this:

SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price)
FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00

Your desired query is this:

select e.name, d.depName from employee e join deparment d on (e.depNo = d.depNo)

Are you saying you don't see any resemblance at all?

How about if i say:

select new ck.horlo.MyPojo(e.name, d.deptName)
from Employee e join e.deptNo d

?

tom

--
Voltan tires of life upon Super Secret Sea-Base Beta. Perhaps this
Holloway Road of which you speak is the solution. Voltan shall investigate
it during Voltans campaign to overrun London. (This is but a part of
Voltans plan for world domination.) -- Voltan
 
Reply With Quote
 
Stefanie Ertheld
Guest
Posts: n/a
 
      03-12-2011
On 11 Mrz., 20:50, Tom Anderson <(E-Mail Removed)> wrote:
> On Fri, 11 Mar 2011, Peter Horlock wrote:
> > however, could you be a bit more specific?
> > Throwing this at Google:
> >http://www.google.de/search?q=JPQL+c...on&ie=utf-8&oe...
> > Gives me its hugh reference:
> >http://openjpa.apache.org/builds/1.2....2.0/docs/manu...

>
> This might be marginally preferable:
>
> http://openjpa.apache.org/builds/lat..._langref.html#...
>
> > However doesn't (easily) answer my problem at hand.

>
> Huh? The example in the document you posted is this:
>
> SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price)
> * * *FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00
>
> Your desired query is this:
>
> select e.name, d.depName from employee e join deparment d on (e.depNo =d.depNo)
>
> Are you saying you don't see any resemblance at all?


Okay, I guess now I got it!
However, it seems like you need an "EventManager" instance for it to
work,
and I read it was something like the Session / SessionManager, or
something "above" it.
Currently we have Spring managing the Hibernate Session - and it was
said the EventManager could be
wirred into Spring too - but is it possible to have both Session and
EventManager parallely?
When I said I don't want to do it in HQL, I meant because I know pure
SQL better.
But now that you come up with JPL - this seems even more complicated.
Couldn't it be done with HQL too? The Pojo I am using i have wired
into Hibernate with a mapping -
it's just a special, flat mapping without any reference to other
entities.

THanks,

Peter
 
Reply With Quote
 
Lew
Guest
Posts: n/a
 
      03-12-2011
Lew wrote:
>> Couldn't it be done with HQL too? The Pojo I am using i [div] have wired


That "[div]" was supposed to be a "[sic]". I sic a "sic" on myself.

--
Lew
Honi soit qui mal y pense.
 
Reply With Quote
 
Arved Sandstrom
Guest
Posts: n/a
 
      03-12-2011
On 11-03-12 09:22 AM, Stefanie Ertheld wrote:
> On 11 Mrz., 20:50, Tom Anderson <(E-Mail Removed)> wrote:
>> On Fri, 11 Mar 2011, Peter Horlock wrote:
>>> however, could you be a bit more specific?
>>> Throwing this at Google:
>>> http://www.google.de/search?q=JPQL+c...on&ie=utf-8&oe...
>>> Gives me its hugh reference:
>>> http://openjpa.apache.org/builds/1.2....2.0/docs/manu...

>>
>> This might be marginally preferable:
>>
>> http://openjpa.apache.org/builds/lat..._langref.html#...
>>
>>> However doesn't (easily) answer my problem at hand.

>>
>> Huh? The example in the document you posted is this:
>>
>> SELECT NEW com.company.PublisherInfo(pub.id, pub.revenue, mag.price)
>> FROM Publisher pub JOIN pub.magazines mag WHERE mag.price > 5.00
>>
>> Your desired query is this:
>>
>> select e.name, d.depName from employee e join deparment d on (e.depNo = d.depNo)
>>
>> Are you saying you don't see any resemblance at all?

>
> Okay, I guess now I got it!
> However, it seems like you need an "EventManager" instance for it to
> work,
> and I read it was something like the Session / SessionManager, or
> something "above" it.
> Currently we have Spring managing the Hibernate Session - and it was
> said the EventManager could be
> wirred into Spring too - but is it possible to have both Session and
> EventManager parallely?
> When I said I don't want to do it in HQL, I meant because I know pure
> SQL better.
> But now that you come up with JPL - this seems even more complicated.
> Couldn't it be done with HQL too? The Pojo I am using i have wired
> into Hibernate with a mapping -
> it's just a special, flat mapping without any reference to other
> entities.
>
> THanks,
>
> Peter


OK, I see where you're coming from a bit more clearly. For background,
JPA is the standard Java EE persistence API, and it describes/defines
JPQL - the Java Persistence Query Language. Think of JPA as an ORM
abstraction that persistence providers like Hibernate and
Toplink/EclipseLink support. Hibernate and EclipseLink still retain
their own native ORM APIs.

JPQL is actually a subset of HQL as of Hibernate 3, and in any case has
always looked like HQL a lot.

Now, if you're using Hibernate, but _not_ the JPA layer supported by
Hibernate (since it is a JPA provider as well), that's cool. I'm no
expert on Hibernate, and in fact when I use EclipseLink I don't use the
native APIs unless there's a really good reason to. So I don't know all
the nooks and crannies of the Hibernate native APIs. But one possibility
is using native SQL in your Hibernate queries and applying a result
transformer. This is similar to iBatis result maps as near as I can tell.

As a side note, I'm guessing Hibernate Session here means a client (as
opposed to server) session, in which case *if* you were using JPA - and
I'm not saying you should - a Session would back up a JPA EntityManager.

AHS
--
The user's going to pick dancing pigs over security every time.
-- Bruce Schneier

 
Reply With Quote
 
Arved Sandstrom
Guest
Posts: n/a
 
      03-12-2011
On 11-03-12 10:38 AM, Lew wrote:
[ SNIP ]

> I'm completely unconvinced that Spring brings anything to this party.

[ SNIP ]

I'm completely unconvinced that Spring brings anything to *any* party.
I've attempted to use it a number of times over the years, for a bunch
of different problems, and I invariably end up with a Spring approach
that is more verbose, more scattered, somewhat disconnected, and more
time-consuming than using Java EE. Not to mention upsized greatly by
reams and reams of Spring JARS.

And before anyone politely or rudely suggests that as a Spring tyro that
I'm probably not using Spring correctly, I'm talking about realizing all
of the above from simply slavishly following Spring tutorials - *those*
result in bloated, complex implementations. I freely concede that I
could make matters worse on my own, but I usually don't bother...not if
I see that the Spring tutorials already produce bad code.

Architecturally I think the Spring guys lost sight of the forest for the
trees a long time ago. Time and time again I'll read a Spring article
that proudly demonstrates XML configuration files and contexts and all
that other good garbage, and at the end of it you're left wondering why
didn't they just write a couple of lines of J2SE Java to do that? This
brings up a larger question - is everyone going overboard on dependency
injection? But that's another debate.

And seriously, even with Java EE 5 but absolutely with Java EE 6, you
need Spring why exactly? Spring is a monstrous ecosystem now - it's
genuinely tough to keep track of everything that SpringSource produces -
and for what? For years now *Java EE* has been the "lightweight"
container; Spring has been the "heavyweight". Not that SpringSource will
ever acknowledge that.

AHS

--
The user's going to pick dancing pigs over security every time.
-- Bruce Schneier

 
Reply With Quote
 
Robert Klemme
Guest
Posts: n/a
 
      03-12-2011
On 12.03.2011 15:51, Arved Sandstrom wrote:
> OK, I see where you're coming from a bit more clearly. For background,
> JPA is the standard Java EE persistence API, and it describes/defines
> JPQL - the Java Persistence Query Language. Think of JPA as an ORM
> abstraction that persistence providers like Hibernate and
> Toplink/EclipseLink support. Hibernate and EclipseLink still retain
> their own native ORM APIs.
>
> JPQL is actually a subset of HQL as of Hibernate 3, and in any case has
> always looked like HQL a lot.
>
> Now, if you're using Hibernate, but _not_ the JPA layer supported by
> Hibernate (since it is a JPA provider as well), that's cool. I'm no
> expert on Hibernate, and in fact when I use EclipseLink I don't use the
> native APIs unless there's a really good reason to. So I don't know all
> the nooks and crannies of the Hibernate native APIs. But one possibility
> is using native SQL in your Hibernate queries and applying a result
> transformer. This is similar to iBatis result maps as near as I can tell.
>
> As a side note, I'm guessing Hibernate Session here means a client (as
> opposed to server) session, in which case *if* you were using JPA - and
> I'm not saying you should - a Session would back up a JPA EntityManager.


In the scenario here what Peter probably wants is a stateless session,
because the result set seems to be large and you don't want all those
instances to go to the cache:

http://docs.jboss.org/hibernate/core...atelesssession

Of course one loses a lot of Hibernate's functionality that way but two
aspects (SQL abstraction, ORM) are still there.

I am not 100% sure whether JPA provides a similar mechanism, but
constructor expression does indeed look similar.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
 
Reply With Quote
 
Lawrence D'Oliveiro
Guest
Posts: n/a
 
      03-13-2011
In message <TLLep.81792$(E-Mail Removed)>, Arved Sandstrom wrote:

> For background, JPA is the standard Java EE persistence API, and it
> describes/defines JPQL - the Java Persistence Query Language. Think of JPA
> as an ORM abstraction that persistence providers like Hibernate and
> Toplink/EclipseLink support. Hibernate and EclipseLink still retain
> their own native ORM APIs.


Can’t you just put some simple SQL in and get a simple iterator out?

Something like this
<http://www.codecodex.com/wiki/Useful_MySQL_Routines#Queries>.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hibernate LazyInitializationException in web application Struts asMVC, Spring as IOC and Hibernate as ORM layer Amit Jain Java 7 04-27-2009 10:27 AM
HIBERNATE - java.lang.NoClassDefFoundError: org/hibernate/cfg/Configuration Eleanna Skouta Java 2 12-09-2008 02:09 AM
Hibernate cannot find hibernate.cfg.xml jstorta Java 1 01-19-2008 01:45 PM
Hibernate Syncronizer now generates code for Hibernate 3.0 msenin@covad.net Java 0 07-14-2005 05:47 AM
[HIBERNATE] [EVALUATION] - Gavin King Censors Hibernate Forum Ilias Lazaridis Java 0 12-27-2004 04:26 AM



Advertisments