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