cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <do...@xsinet.co.za>
Subject Re: Named Queries and SQL that doesn't fetch mapped objects
Date Thu, 15 Jan 2015 08:40:02 GMT
>> 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 <tony@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);


Mime
View raw message