cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John Huss <johnth...@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 16:49:37 GMT
You can't do it directly (unless some very recent addition to Cayenne makes
this possible).  You can return DataRows and then use reflection to copy
the data to the object using PropertyUtils.setProperty (assuming the names
are the same).


On Wed, Dec 21, 2016 at 10:43 AM Kumar <kumar9885035454@gmail.com> wrote:

> 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
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message