cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Named Queries and SQL that doesn't fetch mapped objects
Date Thu, 15 Jan 2015 06:57:53 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. 

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