cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <do...@xsinet.co.za>
Subject Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate
Date Thu, 22 Dec 2016 07:53:42 GMT
Hi Kumar

You could do the following:

1. Create a DBEntity in the Modeler say FAKE_TABLE_DUMMY_POSITION with all 
the fields that appear in your SQLTemplate's select field clause.

2. Then create the ObjEntity in the Modeler for DummyPositionRecord based on 
the FAKE_TABLE_DUMMY_POSITION.
Mark DummyPositionRecord as Read-Only !

Then just specify DummyPositionRecord in your SQLTemplate as usual:
SQLTemplate  qry = new SQLTemplate( DummyPositionRecord.class, sql );

Note, you can't use DummyPositionRecord.class in a normal SelectQuery as the 
DBEntity doesn't exist.

Remember to document your code ;-)

Regards
Jurgen



-----Original Message----- 
From: Kumar
Sent: Wednesday, December 21, 2016 6:42 PM
To: user@cayenne.apache.org
Subject: Re: How to define a raw sql in the datamap.xml file and use it 
through SQLTemplate

Guys,

Let me refine my question and tell you the exact requirement.

I have an UI control (TableView) where i need to display records which are
fetched from DB. For the TableView UI control i need to tell the java class
from which it should pick the values for each column. So each properties
getter() will be bound to the TableColumn.

But now in my case I want values from different db tables, so i'm planning
to write a native sql  with required db columns in the select class.

But to bind to the TableView UI control, i need a java class, so i created
a dummy java pojo class. Eg:DummyPositionRecord.java and i will define
getters and setters for which i' have written the native SQL.

Note:DummyPositionRecord.java is not a cayenne supported persistent class.
I mean to say it is not auto generated by Modeler.

So my question is,
Is there a way where i can tell the native sql select query to return
DummyPositionRecord type, so each record will be an instance of
DummyPositionRecord.

Previously, I could achieve this in Hibernate by
sqlQueryToFetchPositions.setResultTransformer(Transformers.aliasToBean(com.tc.app.exchangemonitor.controller.DummyPosition.class));

Is there anything similar to this in Cayenne?

If not i think i should go with DataRow. If so is there any easy way to
convert a DataRow into DummyPositionRecord instead of picking each property
from DataRow and create a DummyPositionRecord.

Thanks and Sorry for confusing you Guys.




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

> You need to specify which ObjEntity this is supposed to create in the
> DataMap query.
>
>
> On Wed, Dec 21, 2016 at 9:22 AM Kumar <kumar9885035454@gmail.com> wrote:
>
> > 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
View raw message