You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by "Weddle, Anthony" <An...@bluescopesteel.com> on 2009/04/08 07:26:47 UTC
General queries
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.
RE: General queries
Posted by "Weddle, Anthony" <An...@bluescopesteel.com>.
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 [mailto:andrus@objectstyle.org]
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.
Re: General queries
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
RE: General queries
Posted by "Weddle, Anthony" <An...@bluescopesteel.com>.
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 [mailto:andrus@objectstyle.org]
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.
Re: General queries
Posted by Andrus Adamchik <an...@objectstyle.org>.
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
Re: General queries
Posted by Robert Zeigler <ro...@gmail.com>.
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 [mailto:andrus@objectstyle.org]
> 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.
RE: General queries
Posted by "Weddle, Anthony" <An...@bluescopesteel.com>.
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 [mailto:andrus@objectstyle.org]
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.
Re: General queries
Posted by Malcolm Edgar <ma...@gmail.com>.
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 <an...@objectstyle.org> 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.
>
>
Re: General queries
Posted by Andrus Adamchik <an...@objectstyle.org>.
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.