Hey
One more question. Can i use the same above mentioned query with
MappedSelect? returning DummyPositionRecord instead of DataRows?
Thanks!
Kumar.
On Fri, Dec 23, 2016 at 5:38 PM, <do...@xsinet.co.za> wrote:
> Hi Kumar
>
> You have to include all the fields required by
> FakeTableDummyPosition.class, so your select field clause has to specify
> all these fields:
>
> buyer_account
> exch_tools_trade_num
> external_trade_oid
> port_num
>
> Regards
> Jurgen
>
>
> From: Kumar
> Sent: Friday, December 23, 2016 1:12 PM
> To: user@cayenne.apache.org
> Subject: Re: How to define a raw sql in the datamap.xml file and use it
> through SQLTemplate
>
> Hey
>
>
> One problem what i'm facing is if i say * in my select query it is working
> but not when i specify the column name. May be i'm wrong in specifying the
> col name?
> This is what i have done.
>
>
> <db-entity name="FAKE_TABLE_DUMMY_POSITION">
> <db-attribute name="buyer_account" type="VARCHAR" length="20"/>
> <db-attribute name="exch_tools_trade_num" type="VARCHAR" length="50"/>
> <db-attribute name="external_trade_oid" type="INTEGER" length="20"
> isPrimaryKey="true" isMandatory="true"/>
> <db-attribute name="port_num" type="INTEGER" length="20"/>
> </db-entity>
> <obj-entity name="FakeTableDummyPosition" className="FakeTableDummyPosition"
> readOnly="true" dbEntityName="FAKE_TABLE_DUMMY_POSITION">
> <obj-attribute name="buyerAccount" type="java.lang.String"
> db-attribute-path="buyer_account"/>
> <obj-attribute name="exchToolsTradeNum" type="java.lang.String"
> db-attribute-path="exch_tools_trade_num"/>
> <obj-attribute name="portNum" type="java.lang.Integer"
> db-attribute-path="port_num"/>
> </obj-entity>
>
>
> This is working:
> SQLTemplate temp = new SQLTemplate(FakeTableDummyPosition.class, "select
> * from external_trade et join exch_tools_trade ett on et.oid =
> ett.external_trade_oid where ett.external_trade_oid > 18853859");
>
>
>
> This is not WORKING:
> final SQLTemplate temp = new SQLTemplate(FakeTableDummyPosition.class,
> "select ett.buyer_account from external_trade et join exch_tools_trade ett
> on et.oid = ett.external_trade_oid where ett.external_trade_oid >
> 18853859");
>
>
>
> Also attached the Modeler generated files.
>
>
>
>
> Thanks,
> Kumar
>
> On Fri, Dec 23, 2016 at 1:45 PM, <do...@xsinet.co.za> wrote:
>
> Hi Kumar
>
> Glad it worked :-)
> I don't have any knowledge of Hibernate so I cannot say.
> Maybe someone else on the list can comment ?
>
> Regards
> Jurgen
>
> -----Original Message----- From: Kumar
> Sent: Friday, December 23, 2016 8:12 AM
> To: user@cayenne.apache.org
> Subject: Re: How to define a raw sql in the datamap.xml file and use it
> through SQLTemplate
>
> Hey,
>
> I tried your solution and its working. Thanks again. I can save a few
> lines
> of code now.
>
> Can i take this as a equivalent to the below which i was using in
> Hibernate.
>
> *sqlQueryToFetchPositions.setResultTransformer(
> Transformers.aliasToBean(com.tc.app.exchangemonitor.
> controller.DummyPosition.class));*
>
>
> Thanks,
> Kumar.
>
> On Thu, Dec 22, 2016 at 1:23 PM, <do...@xsinet.co.za> wrote:
>
>
> 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 <johnth...@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 <kumar9885035...@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 <johnth...@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 <kumar9885035...@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
> > > >
> > > >
> > >
> >
>
>
>
>
>
>
>
>
