cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kumar <kumar9885035...@gmail.com>
Subject Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate
Date Fri, 23 Dec 2016 15:03:05 GMT
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 <kumar9885035454@gmail.com> 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, <dollj@xsinet.co.za> 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: 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, <dollj@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, <dollj@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.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 <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.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 <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