Andrus,
Thanks for taking time to answer the question. I got your point.
On Sat, Dec 24, 2016 at 1:05 AM, Andrus Adamchik <and...@objectstyle.org>
wrote:
> This works of course, but is a matter of taste. I'd personally prefer a
> manual copy function to a redundant mapped entity. The code itself is
> straightforward, and makes your intent crystal clear:
>
> List<DummyPositionRecord> result = new ArrayList<>();
> SQLSelect.dataRowQuery(sql)
> .select(context)
> .stream()
> .map(dr -> {
> DummyPositionRecord record = new DummyPositionRecord();
> record.setX(dr.get("X"));
> ..
> return record;
> })
> .forEach(result::add);
>
> Andrus
>
>
> > On Dec 22, 2016, at 10:53 AM, 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
> >> > > >
> >> > > >
> >> > >
> >> >
> >
>
>
