Hibernate mapping for sql join

[Peter Horlock]

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

 

[Arved Sandstrom]

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

 

[Peter Horlock]

Thanks,

however, could you be a bit more specific?
Throwing this at Google:
http://www.google.de/search?q=JPQL+...&rls=org.mozilla:de:official&client=firefox-a
Gives me its hugh reference:
http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_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

 

[Tom Anderson]

however, could you be a bit more specific?
Throwing this at Google:
http://www.google.de/search?q=JPQL+...&rls=org.mozilla:de:official&client=firefox-a
Gives me its hugh reference:
http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_langref.html

This might be marginally preferable:

http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#jpa_langref_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

 

[Stefanie Ertheld]

This might be marginally preferable:

http://openjpa.apache.org/builds/latest/docs/manual/jpa_langref.html#...


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

 

[Lew]

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.

 

[Arved Sandstrom]

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

 

[Arved Sandstrom]

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

 

[Robert Klemme]

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/3.6/reference/en-US/html/batch.html#batch-statelesssession

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

 

[Lawrence D'Oliveiro]

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>.

 

[Arved Sandstrom]

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>.

Leaving aside the code to construct the parts of an arbitrary SELECT,
that Python code more or less translates - as one possibility - to a
JDBC executeQuery on a connection, that returns a ResultSet. The
java.sql.ResultSet provides an iterable set where fields can also be
addressed by name.

What the OP has in mind, and what the discussion was really about, is
the equivalent in your Python code of replacing the "dict(...)" in
"MapFn = lambda Row : dict(zip(FieldNames, Row))" in the return of the
GetEachRecord function, with the construction of objects (POJOs in the
Java case), where the fields of the object, rather than fields of a
dictionary, are properly initialised.

"POJO" in this specific case means an arbitrary Java class that does not
correspond to an entity class defined by ORM mappings, assuming an ORM
is in play. Which in this case it is: Hibernate.

If you are using an ORM - Hibernate, EclipseLink, iBatis, Spring JDBC
etc - there are in fact various mechanisms to, with or without JPA, to
do what the OP needs, essentially to load up a List of POJOs with the
values from a ResultSet. The OP made it interesting by expressing a
desire not to use JPA, if I understand him correctly, so this is what
makes things interesting. And what we were discussing.

AHS

 

[Lawrence D'Oliveiro]

What the OP has in mind, and what the discussion was really about, is
the equivalent in your Python code of replacing the "dict(...)" in
"MapFn = lambda Row : dict(zip(FieldNames, Row))" in the return of the
GetEachRecord function, with the construction of objects (POJOs in the
Java case), where the fields of the object, rather than fields of a
dictionary, are properly initialised.

You mean replace “d["k"]†with “d.k� That’s equally easy to do in a dynamic
language like Python.

In JavaScript, they are even the same thing.

 

[Arved Sandstrom]

What the OP has in mind, and what the discussion was really about, is
the equivalent in your Python code of replacing the "dict(...)" in
"MapFn = lambda Row : dict(zip(FieldNames, Row))" in the return of the
GetEachRecord function, with the construction of objects (POJOs in the
Java case), where the fields of the object, rather than fields of a
dictionary, are properly initialised.

You mean replace “d["k"]†with “d.k� That’s equally easy to do in a dynamic
language like Python.

In JavaScript, they are even the same thing.

More precisely an actual call to an object constructor. As you said,
there are languages where doing this ad hoc on the spot is succinct
compared to Java. No arguments from me about that. OTOH, it's no big
deal in Java either - the only real question here is how have the
various ORMs already bundled up this functionality. But a person could
always write their own ResultSet -> arbitrary POJO mapper.

AHS