cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kumar <kumar9885035...@gmail.com>
Subject Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate
Date Wed, 21 Dec 2016 15:21:55 GMT
Hey
Even with NamedQuery i face the same issue.
*Exception in thread "main" org.apache.cayenne.CayenneRuntimeException:
[v.4.0.M4.ce40fc0 Oct 26 2016 22:22:58] Set up Object entity or use
rowFetchingQuery*

final NamedQuery namedQuery = new NamedQuery("PositionWithoutBuyerAccount");

final List<ExternalTrade> x =
CayenneHelper.getCayenneServerRuntime().newContext().performQuery(namedQuery);


<query name="PositionWithoutBuyerAccount" type="SQLTemplate">
<sql>

<![CDATA[
SELECT et.* FROM external_trade et, exch_tools_trade
ett,external_trade_state ets WHERE (et.external_trade_system_oid IN (1))
AND (et.external_trade_source_oid in (1)) AND (et.external_trade_status_oid
IN (1, 2, 3, 4)) AND (et.external_trade_state_oid IN (1, 2, 3, 4)) AND
(ett.buyer_account NOT IN ('TC123')) AND (ett.creation_date >=
('2016-01-01')) AND (ett.creation_date <= ('2016-12-21')) AND NOT EXISTS
(SELECT 1 FROM exch_tools_trade ett1 JOIN external_trade et1 ON et1.oid =
ett1.external_trade_oid  JOIN external_trade_state ets1 ON
et1.external_trade_state_oid = ets1.oid WHERE ett.commodity =
ett1.commodity AND ett.exch_tools_trade_num  = ett1.exch_tools_trade_num
AND ett.trading_period = ett1.trading_period AND ett.buyer_account =
ett1.buyer_account AND
convert(datetime,convert(varchar,ett.creation_date,109)) =
convert(datetime,convert(varchar,ett1.creation_date,109)) AND
ISNULL(ett.call_put,'NULL') = ISNULL(ett1.call_put,'NULL') AND
ISNULL(ett.strike_price,0) = ISNULL(ett1.strike_price,0) AND
(((ets1.external_trade_state_name = 'Update' or
ets1.external_trade_state_name = 'Delete') AND
(ets.external_trade_state_name = 'Add')) OR (ets1.external_trade_state_name
= 'Delete' AND ets.external_trade_state_name = 'Update'))) AND
ets.external_trade_state_name != 'Delete'  AND et.oid =
ett.external_trade_oid AND et.external_trade_state_oid = ets.oid
]]>
</sql>
</query>

Thanks,
Kumar

On Wed, Dec 21, 2016 at 8:43 PM, John Huss <johnthuss@gmail.com> wrote:

> I haven't used MappedQuery, which may be a better solution.  But here is
> the old-school way to do it.
> http://cayenne.apache.org/docs/3.0/namedquery.html
>
> You don't have to fetch DataRows, but be aware that you can only
> materialize one type of Entity at a time.  And for performance you should
> be sure to fetch ALL the columns of that entity - both of which it looks
> like you are already doing.
>
> John
>
>
> On Wed, Dec 21, 2016 at 8:47 AM Kumar <kumar9885035454@gmail.com> wrote:
>
> > I know setting the below property will solve the problem but i don't want
> > DataRows to be return instead i want my persistent entity type.
> >
> > <property name="cayenne.GenericSelectQuery.fetchingDataRows"
> value="true"/>
> >
> > Thanks!
> > Kumar
> > ‚Äč
> >
>

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