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, <[email protected]> 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: [email protected] > 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> >> 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 >> > > > >> > > > >> > > >> > >> >> >
