You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Borut Bolčina <bo...@gmail.com> on 2009/09/28 11:31:12 UTC
SQLTemplate with SQL MAX function
Hi,
Is there a nicer way of executing
SELECT max(ordering) FROM user_bookmark WHERE userid=293130
then this:
SQLTemplate template = new SQLTemplate(UserBookmark.class, "SELECT
max(ordering) FROM user_bookmark WHERE userid=$uid");
template.setParameters(Collections.singletonMap("uid", user.getId()));
template.setFetchingDataRows(true);
List<UserBookmark> res = context.performQuery(template);
Integer maxOrdering = null;
if (res != null) {
Map row = (Map)res.get(0);
if (row != null) {
maxOrdering = (Integer) row.get("max(ordering)");
}
}
if (maxOrdering == null) {
maxOrdering = 0;
}
userBookmark.setOrdering(maxOrdering + 1);
I took me some time to figure out the name of the key in the map. Note that
userid is a FK, so it is not mapped in the modeler.
Regards,
Borut
Re: SQLTemplate with SQL MAX function
Posted by Borut Bolčina <bo...@gmail.com>.
Hi Evgeny,
thanks for your answers and tips. The last solution you proposed aims at
creating SQLTemplates for tables with no attributes other than FKs (that is
join tables or cross tables). In my case it makes no difference, as my cross
table (user_bookmark) also have one meaningful attibute (ordering), so I
need to have it mapped. It is only one FK in that table that needs to be
part of SQLTemplate (userid).
Using alias makes the code more readable, thanks for that.
Cheers,
Borut
2009/10/2 Evgeny Ryabitskiy <ev...@gmail.com>
> If you still interested.. I found a solution of how to perform simple
> SQLTemplate query without mapping.
> In you example it will be like:
>
> DataMap someDataMap = ..... // get it from somewhere... e.g. from
> DataContext
>
> SQLTemplate template = new SQLTemplate(someDataMap, "SELECT
> max(ordering) as MAX_ORDERING FROM user_bookmark WHERE userid=$uid");
> template.setParameters(Collections.singletonMap("uid", user.getId()));
> template.setFetchingDataRows(true);
> List<DataRow> res = context.performQuery(template);
>
> Integer maxOrdering = null;
> if (res != null) {
> DataRow row =res.get(0);
> if (row != null) {
> maxOrdering = (Integer) row.get("MAX_ORDERING");
> }
> }
> if (maxOrdering == null) {
> maxOrdering = 0;
> }
>
> Hope it can help you...
>
> Best regards,
> Evgeny Ryabitskiy.
>
>
>
> 2009/10/2 Evgeny Ryabitskiy <ev...@gmail.com>:
> >> Unfortunately there is an empty page at
> >> http://cayenne.apache.org/doc/modeling-queries.html
> >
> >> I would also report a broken link on the front page for the Wiki (
> >> http://cayenne.apache.org/CAY/index.html)
> >
> >> I would also recomend a FAQ section on the main page which would answer
> >> common questions, like "How do I do SELECT count(*) FROM artist WHERE
> >> first_name LIKE '%abl%'
> >
> > Best way to get any Cayenne Improvement (Documentation or
> > Functionality) is to report an issue in JIRA.
> > https://issues.apache.org/jira/secure/BrowseProject.jspa
> > It's not hard.
> >
> > Once issue is reported you got every notify about every it's change in
> > progress. And the main advantage that since issue reported, it's not
> > going to be lost as in this mail list....
> > One problem is that JIRA not working properly last few days... hope it
> > will be fixed....
> >
> >
> >
> >> And that holds true for EJBQLQuery as well does it not? Maybe there
> should
> >> be an advice somewhere in the docs that one must use DataRows as soon as
> >> there is just one non-mapped attribute in the query.
> >
> > I'v created an JIRA issue for this
> > https://issues.apache.org/jira/browse/CAY-1287
> > The progress of fix should be faster, if you someday registry in JIRA
> > and vote for it... ;)
> >
> >
> > Best regards,
> > Evgeny Ryabitskiy.
> >
>
Re: SQLTemplate with SQL MAX function
Posted by Evgeny Ryabitskiy <ev...@gmail.com>.
If you still interested.. I found a solution of how to perform simple
SQLTemplate query without mapping.
In you example it will be like:
DataMap someDataMap = ..... // get it from somewhere... e.g. from DataContext
SQLTemplate template = new SQLTemplate(someDataMap, "SELECT
max(ordering) as MAX_ORDERING FROM user_bookmark WHERE userid=$uid");
template.setParameters(Collections.singletonMap("uid", user.getId()));
template.setFetchingDataRows(true);
List<DataRow> res = context.performQuery(template);
Integer maxOrdering = null;
if (res != null) {
DataRow row =res.get(0);
if (row != null) {
maxOrdering = (Integer) row.get("MAX_ORDERING");
}
}
if (maxOrdering == null) {
maxOrdering = 0;
}
Hope it can help you...
Best regards,
Evgeny Ryabitskiy.
2009/10/2 Evgeny Ryabitskiy <ev...@gmail.com>:
>> Unfortunately there is an empty page at
>> http://cayenne.apache.org/doc/modeling-queries.html
>
>> I would also report a broken link on the front page for the Wiki (
>> http://cayenne.apache.org/CAY/index.html)
>
>> I would also recomend a FAQ section on the main page which would answer
>> common questions, like "How do I do SELECT count(*) FROM artist WHERE
>> first_name LIKE '%abl%'
>
> Best way to get any Cayenne Improvement (Documentation or
> Functionality) is to report an issue in JIRA.
> https://issues.apache.org/jira/secure/BrowseProject.jspa
> It's not hard.
>
> Once issue is reported you got every notify about every it's change in
> progress. And the main advantage that since issue reported, it's not
> going to be lost as in this mail list....
> One problem is that JIRA not working properly last few days... hope it
> will be fixed....
>
>
>
>> And that holds true for EJBQLQuery as well does it not? Maybe there should
>> be an advice somewhere in the docs that one must use DataRows as soon as
>> there is just one non-mapped attribute in the query.
>
> I'v created an JIRA issue for this
> https://issues.apache.org/jira/browse/CAY-1287
> The progress of fix should be faster, if you someday registry in JIRA
> and vote for it... ;)
>
>
> Best regards,
> Evgeny Ryabitskiy.
>
Re: SQLTemplate with SQL MAX function
Posted by Evgeny Ryabitskiy <ev...@gmail.com>.
> Unfortunately there is an empty page at
> http://cayenne.apache.org/doc/modeling-queries.html
> I would also report a broken link on the front page for the Wiki (
> http://cayenne.apache.org/CAY/index.html)
> I would also recomend a FAQ section on the main page which would answer
> common questions, like "How do I do SELECT count(*) FROM artist WHERE
> first_name LIKE '%abl%'
Best way to get any Cayenne Improvement (Documentation or
Functionality) is to report an issue in JIRA.
https://issues.apache.org/jira/secure/BrowseProject.jspa
It's not hard.
Once issue is reported you got every notify about every it's change in
progress. And the main advantage that since issue reported, it's not
going to be lost as in this mail list....
One problem is that JIRA not working properly last few days... hope it
will be fixed....
> And that holds true for EJBQLQuery as well does it not? Maybe there should
> be an advice somewhere in the docs that one must use DataRows as soon as
> there is just one non-mapped attribute in the query.
I'v created an JIRA issue for this
https://issues.apache.org/jira/browse/CAY-1287
The progress of fix should be faster, if you someday registry in JIRA
and vote for it... ;)
Best regards,
Evgeny Ryabitskiy.
Re: SQLTemplate with SQL MAX function
Posted by Borut Bolčina <bo...@gmail.com>.
2009/9/28 Evgeny Ryabitskiy <ev...@gmail.com>
> Hello!
>
> To solve problem with key in map you can use aliases:
>
> String sql = "SELECT max(ordering) as MAX_ORDERING FROM user_bookmark WHERE
> userid=$uid");
> SQLTemplate template = new SQLTemplate(UserBookmark.class, sql);
>
> Now map contains key "MAX_ORDERING"
>
>
Thanks, makes sense.
> You can write you query to cayenne DomainMap.map.xml and use NamedQuery
> that
> returns List of DataRows.
>
Unfortunately there is an empty page at
http://cayenne.apache.org/doc/modeling-queries.html
>
> Unfortunately you can't use SQLTemplate without DataObject, it's very not
> comfortable cause sometimes there is no need in mapping while forming
> queries at runtime... Hope it will be fixed...
>
And that holds true for EJBQLQuery as well does it not? Maybe there should
be an advice somewhere in the docs that one must use DataRows as soon as
there is just one non-mapped attribute in the query.
I would also recomend a FAQ section on the main page which would answer
common questions, like "How do I do SELECT count(*) FROM artist WHERE
first_name LIKE '%abl%'
I would also report a broken link on the front page for the Wiki (
http://cayenne.apache.org/CAY/index.html)
> Best regards,
> Evgeny Ryabitskiy.
>
>
> 2009/9/28 Borut Bolčina <bo...@gmail.com>:
> > Hi,
> >
> > Is there a nicer way of executing
> > SELECT max(ordering) FROM user_bookmark WHERE userid=293130
> >
> > then this:
> > SQLTemplate template = new SQLTemplate(UserBookmark.class, "SELECT
> > max(ordering) FROM user_bookmark WHERE userid=$uid");
> > template.setParameters(Collections.singletonMap("uid", user.getId()));
> > template.setFetchingDataRows(true);
> > List<UserBookmark> res = context.performQuery(template);
> >
> > Integer maxOrdering = null;
> > if (res != null) {
> > Map row = (Map)res.get(0);
> > if (row != null) {
> > maxOrdering = (Integer) row.get("max(ordering)");
> > }
> > }
> > if (maxOrdering == null) {
> > maxOrdering = 0;
> > }
> >
> > userBookmark.setOrdering(maxOrdering + 1);
> >
> > I took me some time to figure out the name of the key in the map. Note
> that
> > userid is a FK, so it is not mapped in the modeler.
> >
> > Regards,
> > Borut
> >
>
Re: SQLTemplate with SQL MAX function
Posted by Evgeny Ryabitskiy <ev...@gmail.com>.
Hello!
To solve problem with key in map you can use aliases:
String sql = "SELECT max(ordering) as MAX_ORDERING FROM user_bookmark WHERE
userid=$uid");
SQLTemplate template = new SQLTemplate(UserBookmark.class, sql);
Now map contains key "MAX_ORDERING"
You can write you query to cayenne DomainMap.map.xml and use NamedQuery that
returns List of DataRows.
Unfortunately you can't use SQLTemplate without DataObject, it's very not
comfortable cause sometimes there is no need in mapping while forming
queries at runtime... Hope it will be fixed...
Best regards,
Evgeny Ryabitskiy.
2009/9/28 Borut Bolčina <bo...@gmail.com>:
> Hi,
>
> Is there a nicer way of executing
> SELECT max(ordering) FROM user_bookmark WHERE userid=293130
>
> then this:
> SQLTemplate template = new SQLTemplate(UserBookmark.class, "SELECT
> max(ordering) FROM user_bookmark WHERE userid=$uid");
> template.setParameters(Collections.singletonMap("uid", user.getId()));
> template.setFetchingDataRows(true);
> List<UserBookmark> res = context.performQuery(template);
>
> Integer maxOrdering = null;
> if (res != null) {
> Map row = (Map)res.get(0);
> if (row != null) {
> maxOrdering = (Integer) row.get("max(ordering)");
> }
> }
> if (maxOrdering == null) {
> maxOrdering = 0;
> }
>
> userBookmark.setOrdering(maxOrdering + 1);
>
> I took me some time to figure out the name of the key in the map. Note
that
> userid is a FK, so it is not mapped in the modeler.
>
> Regards,
> Borut
>