How to define a raw sql in the datamap.xml file and use it through SQLTemplate

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
Hey,

I have defined few queries in the datamap.xml file and and accessing it
using MappedSelect.query("queryName")

My quick question is, I want to define a native sql query in the
datamap.xml file and to access in the code. Currently MappedSelect.query()
is returning a MappedSelect which i cannot use with SQLTemplate.

Thanks in Advance
Saravana Kumar.M
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Dzmitry Kazimirchyk
Hello Kumar,

Currently it is not possible to extract a query defined in the datamap
as an SQLTemplate query instance.

MappedSelect is a separate type of query which represents all the query
types that can be defined in the datamap and the idea behind it is that
after defining your query in the Modeler, you then don't have to worry
about which type it is when calling it and just specify the necessary
parameters for every select you make. E.g.:

List<Artist> artists = MappedSelect.query("SelectArtists", Artist.class)
   .param("name", "artist1")
   .select(context);

So in essence you can use MappedSelect instance you get the way you
would use any other Cayenne query instance.

Hope this helps.


Dima


On 12/21/16 1:09 PM, Kumar wrote:

> Hey,
>
> I have defined few queries in the datamap.xml file and and accessing it
> using MappedSelect.query("queryName")
>
> My quick question is, I want to define a native sql query in the
> datamap.xml file and to access in the code. Currently MappedSelect.query()
> is returning a MappedSelect which i cannot use with SQLTemplate.
>
> Thanks in Advance
> Saravana Kumar.M
>

Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
Hey Dima,

Thanks for replying.
But i don't think i understood your answer totally. As i said, it is a
native SQL with multiple joins and "if exists" clauses.


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


So to fire that i think i need SQLTemplate. If i try what you suggessted
i'm getting the below problem

*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*

Thanks,
Saravana Kumar.M

On Wed, Dec 21, 2016 at 7:06 PM, Dzmitry Kazimirchyk <[hidden email]
> wrote:

> Hello Kumar,
>
> Currently it is not possible to extract a query defined in the datamap as
> an SQLTemplate query instance.
>
> MappedSelect is a separate type of query which represents all the query
> types that can be defined in the datamap and the idea behind it is that
> after defining your query in the Modeler, you then don't have to worry
> about which type it is when calling it and just specify the necessary
> parameters for every select you make. E.g.:
>
> List<Artist> artists = MappedSelect.query("SelectArtists", Artist.class)
>   .param("name", "artist1")
>   .select(context);
>
> So in essence you can use MappedSelect instance you get the way you would
> use any other Cayenne query instance.
>
> Hope this helps.
>
>
> Dima
>
>
>
> On 12/21/16 1:09 PM, Kumar wrote:
>
>> Hey,
>>
>> I have defined few queries in the datamap.xml file and and accessing it
>> using MappedSelect.query("queryName")
>>
>> My quick question is, I want to define a native sql query in the
>> datamap.xml file and to access in the code. Currently MappedSelect.query()
>> is returning a MappedSelect which i cannot use with SQLTemplate.
>>
>> Thanks in Advance
>> Saravana Kumar.M
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
In reply to this post by Dzmitry Kazimirchyk
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

Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

John Huss
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 <[hidden email]> 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
> ​
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Andrus Adamchik
To be clear MappedSelect is a 4.0 analog of the older NamedQuery, only with better API. So John's advice stands.

Andrus

> On Dec 21, 2016, at 6:13 PM, John Huss <[hidden email]> 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 <[hidden email]> 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
>> ​
>>

Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
In reply to this post by John Huss
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 <[hidden email]> 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 <[hidden email]> 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
> > ​
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

John Huss
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
> > > ​
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
> 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
> > > > ​
> > > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

John Huss
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
> 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 <[hidden email]>
> > 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
> > > > > ​
> > > > >
> > > >
> > >
> >
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

dollj
In reply to this post by Kumar
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: [hidden email]
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
> 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
> > > >
> > > >
> > >
> >
>

Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
Hey,
Thanks for the solution. Seems interesting and i'm expecting something
similar like this.
Currently I implemented the same but not creating the dummy db entity and
obj entity in Modelor instead i just have a normal java pojo class
DummyPositionRecord.java and i'm fetching the my sql query using
MappedSelect. MappedSelect is returning DataRows. Once i receive the
DataRows, for each DataRow, i'll create a DummyPositionRecord through
constructor by passing all the values of the DataRow.

This is not a elegant solution than urs. But thanks a lot. I'll try your
solution too.


Thanks,
Kumar.

On Thu, Dec 22, 2016 at 1:23 PM, <[hidden email]> 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: [hidden email]
> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
>> 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
>> > > >
>> > > >
>> > >
>> >
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
In reply to this post by dollj
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, <[hidden email]> 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: [hidden email]
> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
>> 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
>> > > >
>> > > >
>> > >
>> >
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

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

Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

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






_FakeTableDummyPosition.java (1K) Download Attachment
FakeTableDummyPosition.java (372 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

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







Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
Awesome! Working! Thanks a lot.

On Fri, Dec 23, 2016 at 5:38 PM, <[hidden email]> 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: [hidden email]
> 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, <[hidden email]> 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: [hidden email]
>   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, <[hidden email]> 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: [hidden email]
>     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 <[hidden email]>
> 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 <[hidden email]>
> 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 <[hidden email]>
> 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 <[hidden email]
> >
>       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
>       > > >
>       > > >
>       > >
>       >
>
>
>
>
>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
In reply to this post by dollj
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, <[hidden email]> 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: [hidden email]
> 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, <[hidden email]> 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: [hidden email]
>   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, <[hidden email]> 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: [hidden email]
>     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 <[hidden email]>
> 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 <[hidden email]>
> 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 <[hidden email]>
> 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 <[hidden email]
> >
>       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
>       > > >
>       > > >
>       > >
>       >
>
>
>
>
>
>
>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Kumar
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 <[hidden email]> 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, <[hidden email]> 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: [hidden email]
>> 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, <[hidden email]> 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: [hidden email]
>>   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, <[hidden email]> 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: [hidden email]
>>     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 <[hidden email]>
>> 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 <[hidden email]>
>> 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 <[hidden email]>
>> 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 <
>> [hidden email]>
>>       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
>>       > > >
>>       > > >
>>       > >
>>       >
>>
>>
>>
>>
>>
>>
>>
>>
>
Reply | Threaded
Open this post in threaded view
|

Re: How to define a raw sql in the datamap.xml file and use it through SQLTemplate

Andrus Adamchik
In reply to this post by dollj
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, [hidden email] 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: [hidden email]
> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]>
>> 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
>> > > >
>> > > >
>> > >
>> >
>

12