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, <do...@xsinet.co.za> wrote:
> Hi Kumar
>
> Glad it worked :-)
> I don't have any knowledge of Hibernate so I cannot say.
> Maybe someone else on the list can comment ?
>
> Regards
> Jurgen
>
> -----Original Message----- From: Kumar
> Sent: Friday, December 23, 2016 8:12 AM
> To: user@cayenne.apache.org
> Subject: Re: How to define a raw sql in the datamap.xml file and use it
> through SQLTemplate
>
> Hey,
>
> I tried your solution and its working. Thanks again. I can save a few lines
> of code now.
>
> Can i take this as a equivalent to the below which i was using in
> Hibernate.
>
> *sqlQueryToFetchPositions.setResultTransformer(Transformers.
> aliasToBean(com.tc.app.exchangemonitor.controller.DummyPosition.class));*
>
>
> Thanks,
> Kumar.
>
> On Thu, Dec 22, 2016 at 1:23 PM, <do...@xsinet.co.za> wrote:
>
> Hi Kumar
>>
>> You could do the following:
>>
>> 1. Create a DBEntity in the Modeler say FAKE_TABLE_DUMMY_POSITION with all
>> the fields that appear in your SQLTemplate's select field clause.
>>
>> 2. Then create the ObjEntity in the Modeler for DummyPositionRecord based
>> on the FAKE_TABLE_DUMMY_POSITION.
>> Mark DummyPositionRecord as Read-Only !
>>
>> Then just specify DummyPositionRecord in your SQLTemplate as usual:
>> SQLTemplate qry = new SQLTemplate( DummyPositionRecord.class, sql );
>>
>> Note, you can't use DummyPositionRecord.class in a normal SelectQuery as
>> the DBEntity doesn't exist.
>>
>> Remember to document your code ;-)
>>
>> Regards
>> Jurgen
>>
>>
>>
>> -----Original Message----- From: Kumar
>> Sent: Wednesday, December 21, 2016 6:42 PM
>> To: user@cayenne.apache.org
>> Subject: Re: How to define a raw sql in the datamap.xml file and use it
>> through SQLTemplate
>>
>>
>> Guys,
>>
>> Let me refine my question and tell you the exact requirement.
>>
>> I have an UI control (TableView) where i need to display records which are
>> fetched from DB. For the TableView UI control i need to tell the java
>> class
>> from which it should pick the values for each column. So each properties
>> getter() will be bound to the TableColumn.
>>
>> But now in my case I want values from different db tables, so i'm planning
>> to write a native sql with required db columns in the select class.
>>
>> But to bind to the TableView UI control, i need a java class, so i created
>> a dummy java pojo class. Eg:DummyPositionRecord.java and i will define
>> getters and setters for which i' have written the native SQL.
>>
>> Note:DummyPositionRecord.java is not a cayenne supported persistent class.
>> I mean to say it is not auto generated by Modeler.
>>
>> So my question is,
>> Is there a way where i can tell the native sql select query to return
>> DummyPositionRecord type, so each record will be an instance of
>> DummyPositionRecord.
>>
>> Previously, I could achieve this in Hibernate by
>> sqlQueryToFetchPositions.setResultTransformer(Transformers.
>> aliasToBean(com.tc.app.exchangemonitor.controller.DummyPosition.class));
>>
>> Is there anything similar to this in Cayenne?
>>
>> If not i think i should go with DataRow. If so is there any easy way to
>> convert a DataRow into DummyPositionRecord instead of picking each
>> property
>> from DataRow and create a DummyPositionRecord.
>>
>> Thanks and Sorry for confusing you Guys.
>>
>>
>>
>>
>> On Wed, Dec 21, 2016 at 8:54 PM, John Huss <johnth...@gmail.com> wrote:
>>
>> You need to specify which ObjEntity this is supposed to create in the
>>
>>> DataMap query.
>>>
>>>
>>> On Wed, Dec 21, 2016 at 9:22 AM Kumar <kumar9885035...@gmail.com> wrote:
>>>
>>> > Hey
>>> > Even with NamedQuery i face the same issue.
>>> > *Exception in thread "main" org.apache.cayenne.CayenneRunt
>>> imeException:
>>> > [v.4.0.M4.ce40fc0 Oct 26 2016 22:22:58] Set up Object entity or use
>>> > rowFetchingQuery*
>>> >
>>> > final NamedQuery namedQuery = new
>>> > NamedQuery("PositionWithoutBuyerAccount");
>>> >
>>> > final List<ExternalTrade> x =
>>> >
>>> > CayenneHelper.getCayenneServerRuntime().newContext().performQuery(
>>> namedQuery);
>>> >
>>> >
>>> > <query name="PositionWithoutBuyerAccount" type="SQLTemplate">
>>> > <sql>
>>> >
>>> > <![CDATA[
>>> > SELECT et.* FROM external_trade et, exch_tools_trade
>>> > ett,external_trade_state ets WHERE (et.external_trade_system_oid IN >
>>> (1))
>>> > AND (et.external_trade_source_oid in (1)) AND
>>> (et.external_trade_status_oid
>>> > IN (1, 2, 3, 4)) AND (et.external_trade_state_oid IN (1, 2, 3, 4)) AND
>>> > (ett.buyer_account NOT IN ('TC123')) AND (ett.creation_date >=
>>> > ('2016-01-01')) AND (ett.creation_date <= ('2016-12-21')) AND NOT >
>>> EXISTS
>>> > (SELECT 1 FROM exch_tools_trade ett1 JOIN external_trade et1 ON et1.oid
>>> > =
>>> > ett1.external_trade_oid JOIN external_trade_state ets1 ON
>>> > et1.external_trade_state_oid = ets1.oid WHERE ett.commodity =
>>> > ett1.commodity AND ett.exch_tools_trade_num = >
>>> ett1.exch_tools_trade_num
>>> > AND ett.trading_period = ett1.trading_period AND ett.buyer_account =
>>> > ett1.buyer_account AND
>>> > convert(datetime,convert(varchar,ett.creation_date,109)) =
>>> > convert(datetime,convert(varchar,ett1.creation_date,109)) AND
>>> > ISNULL(ett.call_put,'NULL') = ISNULL(ett1.call_put,'NULL') AND
>>> > ISNULL(ett.strike_price,0) = ISNULL(ett1.strike_price,0) AND
>>> > (((ets1.external_trade_state_name = 'Update' or
>>> > ets1.external_trade_state_name = 'Delete') AND
>>> > (ets.external_trade_state_name = 'Add')) OR (ets1.external_trade_state_
>>> name
>>> > = 'Delete' AND ets.external_trade_state_name = 'Update'))) AND
>>> > ets.external_trade_state_name != 'Delete' AND et.oid =
>>> > ett.external_trade_oid AND et.external_trade_state_oid = ets.oid
>>> > ]]>
>>> > </sql>
>>> > </query>
>>> >
>>> > Thanks,
>>> > Kumar
>>> >
>>> > On Wed, Dec 21, 2016 at 8:43 PM, John Huss <johnth...@gmail.com>
>>> wrote:
>>> >
>>> > > I haven't used MappedQuery, which may be a better solution. But here
>>> is
>>> > > the old-school way to do it.
>>> > > http://cayenne.apache.org/docs/3.0/namedquery.html
>>> > >
>>> > > You don't have to fetch DataRows, but be aware that you can only
>>> > > materialize one type of Entity at a time. And for performance you
>>> should
>>> > > be sure to fetch ALL the columns of that entity - both of which it
>>> looks
>>> > > like you are already doing.
>>> > >
>>> > > John
>>> > >
>>> > >
>>> > > On Wed, Dec 21, 2016 at 8:47 AM Kumar <kumar9885035...@gmail.com>
>>> wrote:
>>> > >
>>> > > > I know setting the below property will solve the problem but i > >
>>> > don't
>>> > want
>>> > > > DataRows to be return instead i want my persistent entity type.
>>> > > >
>>> > > > <property name="cayenne.GenericSelectQuery.fetchingDataRows"
>>> > > value="true"/>
>>> > > >
>>> > > > Thanks!
>>> > > > Kumar
>>> > > >
>>> > > >
>>> > >
>>> >
>>>
>>>
>>>
>>
>
package com.tc.app.exchangemonitor.model.cayenne.persistent.auto;
import org.apache.cayenne.CayenneDataObject;
import org.apache.cayenne.exp.Property;
/**
* Class _FakeTableDummyPosition was generated by Cayenne.
* It is probably a good idea to avoid changing this class manually,
* since it may be overwritten next time code is regenerated.
* If you need to make any customizations, please use subclass.
*/
public abstract class _FakeTableDummyPosition extends CayenneDataObject {
private static final long serialVersionUID = 1L;
public static final String EXTERNAL_TRADE_OID_PK_COLUMN = "external_trade_oid";
public static final Property<String> BUYER_ACCOUNT = new Property<>("buyerAccount");
public static final Property<String> EXCH_TOOLS_TRADE_NUM = new Property<>("exchToolsTradeNum");
public static final Property<Integer> PORT_NUM = new Property<>("portNum");
public String getBuyerAccount()
{
return (String)this.readProperty("buyerAccount");
}
public String getExchToolsTradeNum()
{
return (String)this.readProperty("exchToolsTradeNum");
}
public Integer getPortNum()
{
return (Integer) this.readProperty("portNum");
}
}package com.tc.app.exchangemonitor.model.cayenne.persistent;
import com.tc.app.exchangemonitor.model.cayenne.persistent.auto._FakeTableDummyPosition;
public class FakeTableDummyPosition extends _FakeTableDummyPosition
{
private static final long serialVersionUID = 1L;
}
