cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tony Giaccone <t...@giaccone.org>
Subject Named Queries and SQL that doesn't fetch mapped objects
Date Wed, 14 Jan 2015 17:41:36 GMT
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);

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message