FAQ
We're looking at moving from Hibernate to Cayenne but I'm having trouble
finding out how to perform queries that return arbitrary data, maybe
they are sometimes called report queries. Something like Hibernate's HQL
queries that can return arbitrary data (including aggregate functions or
simply a bunch of columns from different tables that may have formal or
informal relationships).



I can't seem to find any examples of this. All of the query classes seem
to require a specific class (or some specific entity) and the
SQLTemplateQuery doesn't take Expression qualifiers or interpret POJO
property paths.



I assume that there is a not too complicated way to perform arbitrary
queries, without having to go to raw SQL. Is that true?



Thanks,

Tony





NOTICE - This message and any attached files may contain information that is confidential, legally privileged or proprietary. It is intended only for use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error. Any dissemination, copying, use or re-transmission of this message or attachment, or the disclosure of any information therein, is strictly forbidden. BlueScope Steel Limited does not represent or guarantee that this message or attachment is free of errors, virus or interference.

If you have received this message in error please notify the sender immediately and delete the message. Any views expressed in this email are not necessarily the views of BlueScope Steel Limited.

Search Discussions

  • Andrus Adamchik at Apr 8, 2009 at 5:46 am
    Cayenne 3.0 includes support for EJBQLQuery which seem like you what
    you need here:

    http://cayenne.apache.org/doc/ejbqlquery.html

    We are following the JPA syntax per JSR-220 (http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html
    ). At does support aggregates, separate columns, subqueries, etc.
    Anything not supported by the EJBQLQuery will indeed require a
    SQLTemplate, which is not as scary as it sounds.

    Hope this helps.

    Andrus
    On Apr 8, 2009, at 8:26 AM, Weddle, Anthony wrote:

    We're looking at moving from Hibernate to Cayenne but I'm having
    trouble
    finding out how to perform queries that return arbitrary data, maybe
    they are sometimes called report queries. Something like Hibernate's
    HQL
    queries that can return arbitrary data (including aggregate
    functions or
    simply a bunch of columns from different tables that may have formal
    or
    informal relationships).



    I can't seem to find any examples of this. All of the query classes
    seem
    to require a specific class (or some specific entity) and the
    SQLTemplateQuery doesn't take Expression qualifiers or interpret POJO
    property paths.



    I assume that there is a not too complicated way to perform arbitrary
    queries, without having to go to raw SQL. Is that true?



    Thanks,

    Tony





    NOTICE - This message and any attached files may contain information
    that is confidential, legally privileged or proprietary. It is
    intended only for use by the intended recipient. If you are not the
    intended recipient or the person responsible for delivering the
    message to the intended recipient, be advised that you have received
    this message in error. Any dissemination, copying, use or re-
    transmission of this message or attachment, or the disclosure of any
    information therein, is strictly forbidden. BlueScope Steel Limited
    does not represent or guarantee that this message or attachment is
    free of errors, virus or interference.

    If you have received this message in error please notify the sender
    immediately and delete the message. Any views expressed in this
    email are not necessarily the views of BlueScope Steel Limited.
  • Malcolm Edgar at Apr 8, 2009 at 9:05 am
    Hi Anthony,

    I think the feature you are after in Cayenne is Named Queries. In the
    CayenneModeler click on the "Create Query" icon, then in dialog
    "Select New Query Type" select the "Raw Query" option and then
    "Create" button.

    This will take you to a screen where you can define the query name and
    specify other options. In the "SQL Scripts" tab you enter in your SQL
    query and can specify different SQL scripts for the different
    databases, e.g. Oracle vs SQL Server.

    In your script you can specify parameters and use Velocity for
    dynamically creating conditional statements. Very powerful.

    In your Java you create a named and pass in any parameters.

    protected List performNamedQuery(String queryName, Map parameters) {
    return getDataContext().performQuery(queryName, parameters, true);
    }

    By default this will return you a list of RowMap objects, with each
    map representing a row, with the column names as the key.

    regards Malcolm Edgar
    On Wed, Apr 8, 2009 at 3:46 PM, Andrus Adamchik wrote:
    Cayenne 3.0 includes support for EJBQLQuery which seem like you what you
    need here:

    http://cayenne.apache.org/doc/ejbqlquery.html

    We are following the JPA syntax per JSR-220
    (http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html). At does
    support aggregates, separate columns, subqueries, etc. Anything not
    supported by the EJBQLQuery will indeed require a SQLTemplate, which is not
    as scary as it sounds.

    Hope this helps.

    Andrus
    On Apr 8, 2009, at 8:26 AM, Weddle, Anthony wrote:

    We're looking at moving from Hibernate to Cayenne but I'm having trouble
    finding out how to perform queries that return arbitrary data, maybe
    they are sometimes called report queries. Something like Hibernate's HQL
    queries that can return arbitrary data (including aggregate functions or
    simply a bunch of columns from different tables that may have formal or
    informal relationships).



    I can't seem to find any examples of this. All of the query classes seem
    to require a specific class (or some specific entity) and the
    SQLTemplateQuery doesn't take Expression qualifiers or interpret POJO
    property paths.



    I assume that there is a not too complicated way to perform arbitrary
    queries, without having to go to raw SQL. Is that true?



    Thanks,

    Tony





    NOTICE - This message and any attached files may contain information that
    is confidential, legally privileged or proprietary.  It is intended only for
    use by the intended recipient. If you are not the intended recipient or the
    person responsible for delivering the message to the intended recipient, be
    advised that you have received this message in error. Any dissemination,
    copying, use or re-transmission of this message or attachment, or the
    disclosure of any information therein, is strictly forbidden. BlueScope
    Steel Limited does not represent or guarantee that this message or
    attachment is free of errors, virus or interference.

    If you have received this message in error please notify the sender
    immediately and delete the message.  Any views expressed in this email are
    not necessarily the views of BlueScope Steel Limited.
  • Weddle, Anthony at Apr 8, 2009 at 7:27 pm
    Thanks, Andrus. I'll take a look at EJBQL and may have to revisit
    SQLTemplates. It's not that the latter is scary, just that it appears to
    be native SQL, rather than being able to used property paths and class
    or object entity names. We're using an ORM framework to avoid native SQL
    wherever we can, so wouldn't want to switch to a framework that forced
    us back to more of it.

    One of the main problems I'm having is that there is so little
    documentation, books and articles about Cayenne. Is there a fairly
    comprehensive list of publications (not necessarily books) on Cayenne?

    Tony

    -----Original Message-----
    From: Andrus Adamchik
    Sent: Wednesday, 8 April 2009 5:46 p.m.
    To: user@cayenne.apache.org
    Subject: Re: General queries

    Cayenne 3.0 includes support for EJBQLQuery which seem like you what
    you need here:

    http://cayenne.apache.org/doc/ejbqlquery.html

    We are following the JPA syntax per JSR-220
    (http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html
    ). At does support aggregates, separate columns, subqueries, etc.
    Anything not supported by the EJBQLQuery will indeed require a
    SQLTemplate, which is not as scary as it sounds.

    Hope this helps.

    Andrus




    NOTICE - This message and any attached files may contain information that is confidential, legally privileged or proprietary. It is intended only for use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error. Any dissemination, copying, use or re-transmission of this message or attachment, or the disclosure of any information therein, is strictly forbidden. BlueScope Steel Limited does not represent or guarantee that this message or attachment is free of errors, virus or interference.

    If you have received this message in error please notify the sender immediately and delete the message. Any views expressed in this email are not necessarily the views of BlueScope Steel Limited.
  • Robert Zeigler at Apr 8, 2009 at 7:50 pm
    Well, Cayenne doesn't "force" you to use SQL.
    What it /does/ do is to /allow/ you to use SQL.
    An app I wrote awhile back had the requirement to generate reports
    from tables with on the order of a million records.
    There was no substitute for hand crafted, carefully optimized
    queries. Cayenne makes it easy to do that, if the need arises, via
    SQLTemplate.
    But SQLTemplate offers more than "just" SQL. You can script it (via
    velocity [http://velocity.apache.org] directives and variables); you
    can write customized queries for different backends (MySQL, HSQL,
    Oracle, etc.) which then "just work" based on which backend you're
    connected to (ie: in your code, you don't have to think about which
    backend you're connected to).

    But, if you're interested in sticking to object paths and
    relationships, EJBQLQuery provides a nice alternative.

    Robert
    On Apr 8, 2009, at 4/82:27 PM , Weddle, Anthony wrote:

    Thanks, Andrus. I'll take a look at EJBQL and may have to revisit
    SQLTemplates. It's not that the latter is scary, just that it
    appears to
    be native SQL, rather than being able to used property paths and class
    or object entity names. We're using an ORM framework to avoid native
    SQL
    wherever we can, so wouldn't want to switch to a framework that forced
    us back to more of it.

    One of the main problems I'm having is that there is so little
    documentation, books and articles about Cayenne. Is there a fairly
    comprehensive list of publications (not necessarily books) on Cayenne?

    Tony

    -----Original Message-----
    From: Andrus Adamchik
    Sent: Wednesday, 8 April 2009 5:46 p.m.
    To: user@cayenne.apache.org
    Subject: Re: General queries

    Cayenne 3.0 includes support for EJBQLQuery which seem like you what
    you need here:

    http://cayenne.apache.org/doc/ejbqlquery.html

    We are following the JPA syntax per JSR-220
    (http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html
    ). At does support aggregates, separate columns, subqueries, etc.
    Anything not supported by the EJBQLQuery will indeed require a
    SQLTemplate, which is not as scary as it sounds.

    Hope this helps.

    Andrus




    NOTICE - This message and any attached files may contain information
    that is confidential, legally privileged or proprietary. It is
    intended only for use by the intended recipient. If you are not the
    intended recipient or the person responsible for delivering the
    message to the intended recipient, be advised that you have received
    this message in error. Any dissemination, copying, use or re-
    transmission of this message or attachment, or the disclosure of any
    information therein, is strictly forbidden. BlueScope Steel Limited
    does not represent or guarantee that this message or attachment is
    free of errors, virus or interference.

    If you have received this message in error please notify the sender
    immediately and delete the message. Any views expressed in this
    email are not necessarily the views of BlueScope Steel Limited.
  • Andrus Adamchik at Apr 8, 2009 at 8:05 pm

    On Apr 8, 2009, at 10:27 PM, Weddle, Anthony wrote:

    One of the main problems I'm having is that there is so little
    documentation, books and articles about Cayenne. Is there a fairly
    comprehensive list of publications (not necessarily books) on Cayenne?
    Unfortunately no. Cayenne developer community has always been lacking
    in the area of self promotion. All the effort (intentionally or
    subconsciously) went into the code and the bundled documentation. I
    wrote a TSS introductory article many years ago. There was a print
    article in a Japanese edition of JavaWorld ca. the same time. That's
    the stuff I am aware of. Also there were a number of blog posts over
    the years that I haven't been tracking too thoroughly. I would've
    loved to see the wider community to fill this information gap, which
    clearly exists and is hurting us (nested contexts, ROP, rich object
    model... many people have no idea what they are missing, but I digress).

    Anyways, you are free to ask any Cayenne-related questions here. This
    mailing list has always been a reliable resource for support and
    Cayenne ideas.

    Cheers,
    Andrus
  • Weddle, Anthony at Apr 8, 2009 at 11:24 pm
    Well, it looks like EJBQL could be the way to go. It's not as powerful
    as HQL (or maybe it just doesn't have quite the same features) but it
    seems to do a lot of what we might need. It does have some issues
    though.

    This is part of one HQL query that we use in one report:

    select ios,
    (select nvl(sum(iosbu.curQty),0)
    from Iosbu as iosbu
    where iosbu.iosb.ios = ios
    and iosbu.iosb.iosbs.iosbStat = 'A'
    and (iosbu.iosb.useByDt is null
    or iosbu.iosb.useByDt > sysdate)) as currentPaintQty,
    (select nvl(sum(iosbu.curQty),0)
    from Iosbu as iosbu
    where iosbu.iosb.ios = ios
    and (iosbu.iosb.iosbs.iosbStat != 'A'
    or iosbu.iosb.useByDt < sysdate)) as heldPaintQty
    from Ios as ios "

    EJBQL doesn't support subselects in the select clause or have something
    equivalent to the NVL function, so we'd have to do things differently.
    However, I wondered if it could cope with the first subselect and so
    ended up with this EJBQL query (accounting for different relationship
    names in Cayenne):

    select iosbu.toIosb.toIos, sum(iosbu.curQty)
    from Iosbu as iosbu, Ios as ios
    where iosbu.toIosb.toIosbs.id = 'A'
    and (iosbu.toIosb.useByDt is null
    or iosbu.toIosb.useByDt > CURRENT_DATE)
    group by iosbu.toIosb.toIos

    I know I didn't need the "from Ios" but cayenne didn't convert that
    properly, anyway. It created joins for the various relationships, adding
    the joined table between Iosbu and Ios, in the from clause, but without
    adding another comma. So it ended up with:

    SELECT t1.PART_CODE, SUM(t0.CUR_QTY) AS sc0 FROM IOSBU t0, INNER JOIN
    IOSB t1 ON (t0.IOSB_ID = t1.IOSB_ID) INNER JOIN IOSBS t3 ON
    (t1.IOSB_STAT = t3.IOSB_STAT) IOS t2 WHERE t3.IOSB_STAT = ? AND
    t1.USE_BY_DT IS NULL OR t1.USE_BY_DT > {fn CURDATE()} GROUP BY
    t1.PART_CODE

    Notice the "IOS t2" without a preceeding comma.

    As I didn't need to specify Ios in the from clause, I took it out. Then
    I got another problem. Note that selecting the relationship (ending in
    "toIos") results in Cayenne selecting the key for that related table
    (I'm assuming that Cayenne would subsequently use the key to retrieve
    the whole object) but it gets the type wrong. The key is a String but
    Cayenne seems to think it's a Long and tries to retrieve a Long from the
    result set, which fails.

    When I changed the query to select the id of the relationship, then it
    worked fine. So this was the final query:

    select iosbu.toIosb.toIos.id, sum(iosbu.curQty
    from Iosbu as iosbu
    where iosbu.toIosb.toIosbs.id = 'A'
    and (iosbu.toIosb.useByDt is null
    or iosbu.toIosb.useByDt > CURRENT_DATE)
    group by iosbu.toIosb.toIos.id

    I'm getting there!

    Cheers,
    Tony

    -----Original Message-----
    From: Andrus Adamchik
    Sent: Wednesday, 8 April 2009 5:46 p.m.
    To: user@cayenne.apache.org
    Subject: Re: General queries

    Cayenne 3.0 includes support for EJBQLQuery which seem like you what
    you need here:

    http://cayenne.apache.org/doc/ejbqlquery.html

    We are following the JPA syntax per JSR-220
    (http://jcp.org/aboutJava/communityprocess/final/jsr220/index.html
    ). At does support aggregates, separate columns, subqueries, etc.




    NOTICE - This message and any attached files may contain information that is confidential, legally privileged or proprietary. It is intended only for use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error. Any dissemination, copying, use or re-transmission of this message or attachment, or the disclosure of any information therein, is strictly forbidden. BlueScope Steel Limited does not represent or guarantee that this message or attachment is free of errors, virus or interference.

    If you have received this message in error please notify the sender immediately and delete the message. Any views expressed in this email are not necessarily the views of BlueScope Steel Limited.
  • Andrus Adamchik at Apr 13, 2009 at 10:19 am

    On Apr 9, 2009, at 2:24 AM, Weddle, Anthony wrote:
    EJBQL doesn't support subselects in the select clause or have
    something
    equivalent to the NVL function, so we'd have to do things differently. Correct.
    However, I wondered if it could cope with the first subselect and so
    ended up with this EJBQL query (accounting for different relationship
    names in Cayenne):

    select iosbu.toIosb.toIos, sum(iosbu.curQty)
    from Iosbu as iosbu, Ios as ios
    where iosbu.toIosb.toIosbs.id = 'A'
    and (iosbu.toIosb.useByDt is null
    or iosbu.toIosb.useByDt > CURRENT_DATE)
    group by iosbu.toIosb.toIos ...
    SELECT t1.PART_CODE, SUM(t0.CUR_QTY) AS sc0 FROM IOSBU t0, INNER JOIN
    IOSB t1 ON (t0.IOSB_ID = t1.IOSB_ID) INNER JOIN IOSBS t3 ON
    (t1.IOSB_STAT = t3.IOSB_STAT) IOS t2 WHERE t3.IOSB_STAT = ? AND
    t1.USE_BY_DT IS NULL OR t1.USE_BY_DT > {fn CURDATE()} GROUP BY
    t1.PART_CODE

    Notice the "IOS t2" without a preceeding comma.
    Looks like a bug. Care to open a Jira with this example?

    As I didn't need to specify Ios in the from clause, I took it out.
    Then
    I got another problem. Note that selecting the relationship (ending in
    "toIos") results in Cayenne selecting the key for that related table
    (I'm assuming that Cayenne would subsequently use the key to retrieve
    the whole object) but it gets the type wrong. The key is a String but
    Cayenne seems to think it's a Long and tries to retrieve a Long from
    the
    result set, which fails.
    I guess I can comment on why this happened if you provide a sample
    DataMap that includes mapping of all these relationships. I won't
    exclude a possibility of a bug. So maybe you can attach a (subset of)
    DataMap to the Jira above and mention this problem as well.

    Thanks,
    Andrus
  • Weddle, Anthony at Apr 13, 2009 at 10:00 pm
    I've reduced the data map to just what was necessary to replicate the
    problem and have opened a new Jira issue, CAY-1208. I wasn't sure about
    the severity, so left it as major. If I hit these problem during
    production development, they would be show-stoppers, but I'm only
    evaluating.

    Cheers,
    Tony

    -----Original Message-----
    From: Andrus Adamchik
    Sent: Monday, 13 April 2009 10:19 p.m.
    To: user@cayenne.apache.org
    Subject: Re: General queries

    However, I wondered if it could cope with the first subselect and so
    ended up with this EJBQL query (accounting for different relationship
    names in Cayenne):

    select iosbu.toIosb.toIos, sum(iosbu.curQty)
    from Iosbu as iosbu, Ios as ios
    where iosbu.toIosb.toIosbs.id = 'A'
    and (iosbu.toIosb.useByDt is null
    or iosbu.toIosb.useByDt > CURRENT_DATE)
    group by iosbu.toIosb.toIos ...
    SELECT t1.PART_CODE, SUM(t0.CUR_QTY) AS sc0 FROM IOSBU t0, INNER JOIN
    IOSB t1 ON (t0.IOSB_ID = t1.IOSB_ID) INNER JOIN IOSBS t3 ON
    (t1.IOSB_STAT = t3.IOSB_STAT) IOS t2 WHERE t3.IOSB_STAT = ? AND
    t1.USE_BY_DT IS NULL OR t1.USE_BY_DT > {fn CURDATE()} GROUP BY
    t1.PART_CODE

    Notice the "IOS t2" without a preceeding comma.
    Looks like a bug. Care to open a Jira with this example?

    As I didn't need to specify Ios in the from clause, I took it out.
    Then
    I got another problem. Note that selecting the relationship (ending in
    "toIos") results in Cayenne selecting the key for that related table
    (I'm assuming that Cayenne would subsequently use the key to retrieve
    the whole object) but it gets the type wrong. The key is a String but
    Cayenne seems to think it's a Long and tries to retrieve a Long from
    the
    result set, which fails.
    I guess I can comment on why this happened if you provide a sample
    DataMap that includes mapping of all these relationships. I won't
    exclude a possibility of a bug. So maybe you can attach a (subset of)
    DataMap to the Jira above and mention this problem as well.

    Thanks,
    Andrus







    NOTICE - This message and any attached files may contain information that is confidential, legally privileged or proprietary. It is intended only for use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error. Any dissemination, copying, use or re-transmission of this message or attachment, or the disclosure of any information therein, is strictly forbidden. BlueScope Steel Limited does not represent or guarantee that this message or attachment is free of errors, virus or interference.

    If you have received this message in error please notify the sender immediately and delete the message. Any views expressed in this email are not necessarily the views of BlueScope Steel Limited.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedApr 8, '09 at 5:41a
activeApr 13, '09 at 10:00p
posts9
users4
websitecayenne.apache.org

People

Translate

site design / logo © 2022 Grokbase