You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Tony Giaccone <to...@giaccone.org> on 2015/01/14 18:41:36 UTC
Named Queries and SQL that doesn't fetch mapped objects
I have a table that represents log data, and I want to group that data
time, by using SQL rather then pulling back 65000 rows and aggregating in
the java app.
The intent is to look at how many transactions occurred over time in a
bucket of transactions. So you might look at the hours worth of
transactions in 15, 10, or 5 minute windows.
To do that I have a fairly complex query with a subquery, and I'm trying
use a named query to pull back this data and so I have a NamedQuery in the
model..
I know the query works, because I've been able to successfully run it in a
query window in postgress admin
I have a few questions about how this works, and hopefully I can get this
cleared up..
1) If I have several parameters specified in the SQL and I don't provide
values for each in the map I pass in what happens to those values?
2) The code is throwing a parse exception. But it's not clear to me why
that is.
3) When I get the results back, they do not map to a specific entity in my
model. How do I extract them from the query results?
Thanks in Advance.
Tony Giaccone
Error parsing template 'select ?#result('year','String'),
?#result('month','String'), ?#result('day', 'String'),
?#result('hour','String'), ?#result('minute','String'),
?#result('count(*)',int) from ( select
"KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
"LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
"FUNCTION_CALL",year, month, day,hour, minute ) foo group by
year, month, day, hour, minute order by ? year, month, day,hour,
minute' : Invalid arg #1 in directive #resultselect
?#result('year','String'), ?#result('month','String'), ?#result('day',
'String'), ?#result('hour','String'), ?#result('minute','String'),
?#result('count(*)',int) from ( select
"KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
"LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
"FUNCTION_CALL",year, month, day,hour, minute ) foo group by
year, month, day, hour, minute order by ? year, month, day,hour,
minute[line 1, column 176]|
at org.apache.cayenne.access.jdbc.SQLTemplateProcessor.buildStatement(
SQLTemplateProcessor.java:149)
The query is named, FetchBatchCountsForTransaction, and it looks like this:
select
#result('year','String'),
#result('month','String'),
#result('day', 'String'),
#result('hour','String'),
#result('minute','String'),
#result('count(*)',int)
from
(
select "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME",
EXTRACT(year from "LOG_DATE") AS year,
EXTRACT(month from "LOG_DATE") AS month,
EXTRACT(day from "LOG_DATE") as day,
EXTRACT(hour from "LOG_TIME") as hour,
trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall)
order by "FUNCTION_CALL",year, month, day,hour, minute
) foo
group by
year, month, day, hour, minute
order by
year, month, day,hour, minute
Here's the java code I use to call it:
Map<String, Object> params = new HashMap<String, Object>();
params.put("divisor", ""+(60 / divisor));
params.put("functionCall","getTransactionHistory");
NamedQuery fetchGraphData = new NamedQuery("FetchBatchCountsForTransaction",
params);
ObjectContext ctx = BaseContext.getThreadObjectContext();
List dbData = ctx.performQuery(fetchGraphData);
Re: Named Queries and SQL that doesn't fetch mapped objects
Posted by do...@xsinet.co.za.
>> 1) If I have several parameters specified in the SQL and I don't provide
>> values for each in the map I pass in what happens to those values?
>
> Bad idea. Raw SQL is not like Expressions where you can omit parameters.
> A "bare" parameter will remain unparsed in the SQL and will be passed to
> the DB as "$xyz".
> Don't remember what happens if such a parameter is wrapped in a directive.
> But probably nothing good.
You can use #chain and #chunk to handle this, see Scripting SQLTemplate with
Velocity in the docs.
So in your case it would be something like:
from "LOG_DATA" #chain('WHERE') #chunk($functionCall) "FUNCTION_CALL" =
#bind($functionCall) #end #end
>> 3) When I get the results back, they do not map to a specific entity in
>> my model.
>> How do I extract them from the query results?
>
> You need to fetch them as "Data Rows". So you get them as a list
> containing maps of values, one map for each row.
Roughly like so:
SQLTemplate temp = ..... ;
temp.setFetchingDataRows( true );
List<DataRow> dataList = context.performQuery( temp );
for( DataRow row : dataList ) Object obj = row.get( "COLUMN_NAME" );
Regards
Jurgen
> On Jan 14, 2015, at 8:41 PM, Tony Giaccone <to...@giaccone.org> wrote:
>
> I have a table that represents log data, and I want to group that data
> time, by using SQL rather then pulling back 65000 rows and aggregating in
> the java app.
> The intent is to look at how many transactions occurred over time in a
> bucket of transactions. So you might look at the hours worth of
> transactions in 15, 10, or 5 minute windows.
>
> To do that I have a fairly complex query with a subquery, and I'm trying
> use a named query to pull back this data and so I have a NamedQuery in the
> model..
>
> I know the query works, because I've been able to successfully run it in a
> query window in postgress admin
>
> I have a few questions about how this works, and hopefully I can get this
> cleared up..
>
>
> 1) If I have several parameters specified in the SQL and I don't provide
> values for each in the map I pass in what happens to those values?
>
> 2) The code is throwing a parse exception. But it's not clear to me why
> that is.
>
> 3) When I get the results back, they do not map to a specific entity in my
> model. How do I extract them from the query results?
>
>
> Thanks in Advance.
>
>
> Tony Giaccone
>
> Error parsing template 'select ?#result('year','String'),
> ?#result('month','String'), ?#result('day', 'String'),
> ?#result('hour','String'), ?#result('minute','String'),
> ?#result('count(*)',int) from ( select
> "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
> "LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
> ?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
> hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
> "FUNCTION_CALL",year, month, day,hour, minute ) foo group by
> year, month, day, hour, minute order by ? year, month, day,hour,
> minute' : Invalid arg #1 in directive #resultselect
> ?#result('year','String'), ?#result('month','String'), ?#result('day',
> 'String'), ?#result('hour','String'), ?#result('minute','String'),
> ?#result('count(*)',int) from ( select
> "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
> "LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
> ?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
> hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
> "FUNCTION_CALL",year, month, day,hour, minute ) foo group by
> year, month, day, hour, minute order by ? year, month, day,hour,
> minute[line 1, column 176]|
>
> at org.apache.cayenne.access.jdbc.SQLTemplateProcessor.buildStatement(
> SQLTemplateProcessor.java:149)
>
>
> The query is named, FetchBatchCountsForTransaction, and it looks like
> this:
>
>
> select
> #result('year','String'),
> #result('month','String'),
> #result('day', 'String'),
> #result('hour','String'),
> #result('minute','String'),
> #result('count(*)',int)
> from
> (
> select "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME",
> EXTRACT(year from "LOG_DATE") AS year,
> EXTRACT(month from "LOG_DATE") AS month,
> EXTRACT(day from "LOG_DATE") as day,
> EXTRACT(hour from "LOG_TIME") as hour,
> trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall)
> order by "FUNCTION_CALL",year, month, day,hour, minute
> ) foo
> group by
> year, month, day, hour, minute
> order by
> year, month, day,hour, minute
>
>
> Here's the java code I use to call it:
>
> Map<String, Object> params = new HashMap<String, Object>();
>
> params.put("divisor", ""+(60 / divisor));
>
> params.put("functionCall","getTransactionHistory");
>
> NamedQuery fetchGraphData = new
> NamedQuery("FetchBatchCountsForTransaction",
> params);
>
> ObjectContext ctx = BaseContext.getThreadObjectContext();
>
> List dbData = ctx.performQuery(fetchGraphData);
Re: Named Queries and SQL that doesn't fetch mapped objects
Posted by Andrus Adamchik <an...@objectstyle.org>.
> 1) If I have several parameters specified in the SQL and I don't provide
> values for each in the map I pass in what happens to those values?
Bad idea. Raw SQL is not like Expressions where you can omit parameters. A "bare" parameter will remain unparsed in the SQL and will be passed to the DB as "$xyz". Don't remember what happens if such a parameter is wrapped in a directive. But probably nothing good.
> 2) The code is throwing a parse exception. But it's not clear to me why
> that is.
Could you trace the error to a specific row and column in the SQL? Also:
> #result('count(*)',int)
Should be 'int' I think.
> 3) When I get the results back, they do not map to a specific entity in my
> model. How do I extract them from the query results?
You need to fetch them as "Data Rows". So you get them as a list containing maps of values, one map for each row.
Andrus
> On Jan 14, 2015, at 8:41 PM, Tony Giaccone <to...@giaccone.org> wrote:
>
> I have a table that represents log data, and I want to group that data
> time, by using SQL rather then pulling back 65000 rows and aggregating in
> the java app.
> The intent is to look at how many transactions occurred over time in a
> bucket of transactions. So you might look at the hours worth of
> transactions in 15, 10, or 5 minute windows.
>
> To do that I have a fairly complex query with a subquery, and I'm trying
> use a named query to pull back this data and so I have a NamedQuery in the
> model..
>
> I know the query works, because I've been able to successfully run it in a
> query window in postgress admin
>
> I have a few questions about how this works, and hopefully I can get this
> cleared up..
>
>
> 1) If I have several parameters specified in the SQL and I don't provide
> values for each in the map I pass in what happens to those values?
>
> 2) The code is throwing a parse exception. But it's not clear to me why
> that is.
>
> 3) When I get the results back, they do not map to a specific entity in my
> model. How do I extract them from the query results?
>
>
> Thanks in Advance.
>
>
> Tony Giaccone
>
> Error parsing template 'select ?#result('year','String'),
> ?#result('month','String'), ?#result('day', 'String'),
> ?#result('hour','String'), ?#result('minute','String'),
> ?#result('count(*)',int) from ( select
> "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
> "LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
> ?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
> hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
> "FUNCTION_CALL",year, month, day,hour, minute ) foo group by
> year, month, day, hour, minute order by ? year, month, day,hour,
> minute' : Invalid arg #1 in directive #resultselect
> ?#result('year','String'), ?#result('month','String'), ?#result('day',
> 'String'), ?#result('hour','String'), ?#result('minute','String'),
> ?#result('count(*)',int) from ( select
> "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
> "LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
> ?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
> hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
> "FUNCTION_CALL",year, month, day,hour, minute ) foo group by
> year, month, day, hour, minute order by ? year, month, day,hour,
> minute[line 1, column 176]|
>
> at org.apache.cayenne.access.jdbc.SQLTemplateProcessor.buildStatement(
> SQLTemplateProcessor.java:149)
>
>
> The query is named, FetchBatchCountsForTransaction, and it looks like this:
>
>
> select
> #result('year','String'),
> #result('month','String'),
> #result('day', 'String'),
> #result('hour','String'),
> #result('minute','String'),
> #result('count(*)',int)
> from
> (
> select "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME",
> EXTRACT(year from "LOG_DATE") AS year,
> EXTRACT(month from "LOG_DATE") AS month,
> EXTRACT(day from "LOG_DATE") as day,
> EXTRACT(hour from "LOG_TIME") as hour,
> trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall)
> order by "FUNCTION_CALL",year, month, day,hour, minute
> ) foo
> group by
> year, month, day, hour, minute
> order by
> year, month, day,hour, minute
>
>
> Here's the java code I use to call it:
>
> Map<String, Object> params = new HashMap<String, Object>();
>
> params.put("divisor", ""+(60 / divisor));
>
> params.put("functionCall","getTransactionHistory");
>
> NamedQuery fetchGraphData = new NamedQuery("FetchBatchCountsForTransaction",
> params);
>
> ObjectContext ctx = BaseContext.getThreadObjectContext();
>
> List dbData = ctx.performQuery(fetchGraphData);