Re: Named Queries and SQL that doesn't fetch mapped objects

[dollj]

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 <t...@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);