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);
