cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate
Date Fri, 23 Dec 2016 19:35:55 GMT
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, 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.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
View raw message