Hey Got it. Setting the root="obj-entity" root-name="FakeTableDummyPosition" is solving the problem.
Correct me if i'm wrong. Thanks, Kumar On Fri, Dec 23, 2016 at 7:35 PM, Kumar <[email protected]> wrote: > 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, <[email protected]> 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: [email protected] >> 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, <[email protected]> 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: [email protected] >> 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, <[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.DummyPosit >> ion.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.CayenneRunt >> imeException: >> > [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 >> > > > >> > > > >> > > >> > >> >> >> >> >> >> >> >> >
