Hibernate mapping for sql join

Discussion in 'Java' started by Peter Horlock, Mar 11, 2011.

  1. 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
     
    Peter Horlock, Mar 11, 2011
    #1
    1. Advertising

  2. 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
     
    Arved Sandstrom, Mar 11, 2011
    #2
    1. Advertising

  3. 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
     
    Peter Horlock, Mar 11, 2011
    #3
  4. Peter Horlock

    Tom Anderson Guest

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

    --
    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
     
    Tom Anderson, Mar 11, 2011
    #4
  5. On 11 Mrz., 20:50, Tom Anderson <> 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 constructor expression&ie=utf-8&oe...
    > > Gives me its hugh reference:
    > >http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manu...

    >
    > This might be marginally preferable:
    >
    > http://openjpa.apache.org/builds/latest/docs/manual/jpa_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
     
    Stefanie Ertheld, Mar 12, 2011
    #5
  6. Peter Horlock

    Lew Guest

    [OT] "sic" (Was: Hibernate mapping for sql join)

    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.
     
    Lew, Mar 12, 2011
    #6
  7. On 11-03-12 09:22 AM, Stefanie Ertheld wrote:
    > On 11 Mrz., 20:50, Tom Anderson <> 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 constructor expression&ie=utf-8&oe...
    >>> Gives me its hugh reference:
    >>> http://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manu...

    >>
    >> This might be marginally preferable:
    >>
    >> http://openjpa.apache.org/builds/latest/docs/manual/jpa_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
     
    Arved Sandstrom, Mar 12, 2011
    #7
  8. 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
     
    Arved Sandstrom, Mar 12, 2011
    #8
  9. 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/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

    --
    remember.guy do |as, often| as.you_can - without end
    http://blog.rubybestpractices.com/
     
    Robert Klemme, Mar 12, 2011
    #9
  10. In message <TLLep.81792$>, 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>.
     
    Lawrence D'Oliveiro, Mar 13, 2011
    #10
  11. On 11-03-13 03:41 AM, Lawrence D'Oliveiro wrote:
    > In message <TLLep.81792$>, 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>.


    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
    --
    The user's going to pick dancing pigs over security every time.
    -- Bruce Schneier
     
    Arved Sandstrom, Mar 13, 2011
    #11
  12. In message <BG1fp.11128$>, Arved Sandstrom wrote:

    > 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.
     
    Lawrence D'Oliveiro, Mar 13, 2011
    #12
  13. On 11-03-13 08:13 PM, Lawrence D'Oliveiro wrote:
    > In message <BG1fp.11128$>, Arved Sandstrom wrote:
    >
    >> 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
    --
    The user's going to pick dancing pigs over security every time.
    -- Bruce Schneier
     
    Arved Sandstrom, Mar 14, 2011
    #13
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Ilias Lazaridis
    Replies:
    0
    Views:
    1,727
    Ilias Lazaridis
    Dec 27, 2004
  2. Replies:
    0
    Views:
    3,727
  3. jstorta
    Replies:
    1
    Views:
    11,774
  4. Eleanna Skouta
    Replies:
    2
    Views:
    8,441
    Arne Vajhøj
    Dec 9, 2008
  5. Amit Jain
    Replies:
    7
    Views:
    3,307
    Amit Jain
    Apr 27, 2009
Loading...

Share This Page