OK
Thanks Andrus
let's try...
This is the sample quiery of SQLTemplate and Prefetching
SQLTemplate q = new SQLTemplate(
Artist.class,
"SELECT distinct "
+ "#result('ESTIMATED_PRICE' 'BigDecimal' ''
'paintingArray.ESTIMATED_PRICE'), "
+ "#result('PAINTING_TITLE' 'String' ''
'paintingArray.PAINTING_TITLE'), "
+ "#result('GALLERY_ID' 'int' ''
'paintingArray.GALLERY_ID'), "
+ "#result('PAINTING_ID' 'int' ''
'paintingArray.PAINTING_ID'), "
+ "#result('ARTIST_NAME' 'String'), "
+ "#result('DATE_OF_BIRTH' 'java.util.Date'), "
+ "#result('t0.ARTIST_ID' 'int' '' 'ARTIST_ID') "
+ "FROM ARTIST t0, PAINTING t1 "
+ "WHERE t0.ARTIST_ID = t1.ARTIST_ID");
q.addPrefetch("paintingArray");
List objects = context.performQuery(query);
So, this will be an approximation to my final query, that looks some thing like:
SQLTemplate q = new SQLTemplate(
TABLE.class,
"SELECT distinct "
+ "#result('XMIN' 'BigDecimal'
SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 1)), " //?
+ "#result('XMAX' 'BigDecimal'
SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 1)), " //?
+ "#result('YMIN' 'BigDecimal'
SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 2)), "//?
+ "#result('YMAX' 'BigDecimal'
SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 2)) "//?
+ "FROM TABLE c, user_sdo_geom_metadata m "
+ "WHERE m.table_name = 'TABLE' AND m.column_name = 'GEOMETRYCOLUMN' "
+ "AND c.KEY= 'XXXX' ");
q.addPrefetch("user_sdo_geom_metadataArray"); //?
But i dont know how to use/combine prefeching here in order to include
the calls to SDO functions in the fields after SELECT distinct
some more idea?
EmeCas
On Tue, Sep 28, 2010 at 5:41 PM, Andrus Adamchik <andru..bjectstyle.org> wrote:
>
> Not an expert on Oracle Spatial, but I think you should be able to run this via SQLTemplate query:
>
> http://cayenne.apache.org/doc30/sqltemplate-query.html
>
> Andrus
>
> On Sep 28, 2010, at 2:11 PM, ЄЖЄRSoN CმStმNЄDმ S wrote:
>
> > Hello everyone,
> >
> > I am newby in Cayenne 3.0, I would like to know if it is possible to
> > structure a query as follow using cayenne (these are SDO fuction of ORACLE).
> >
> > SELECT SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 1) as xmin,
> > SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 1) as xmax,
> > SDO_GEOM.SDO_MIN_MBR_ORDINATE(c.geometry, m.diminfo, 2) as ymin,
> > SDO_GEOM.SDO_MAX_MBR_ORDINATE(c.geometry, m.diminfo, 2) as ymax
> > FROM TABLE c, user_sdo_geom_metadata m
> > WHERE m.table_name = 'TABLE' AND m.column_name = 'GEOMETRYCOLUMN';
> > AND c.KEY= 'XXXX';
> >
> > In case that it would not be viable directly from a query, which is the best
> > way implementing a query like that?, maybe calling a procedure in PL/sql, i
> > guess.
> >
> > is it there some documentation source about SDO Oracle & Cayenne?
> >
> > Thanks for your help
> >
> >
> > EmeCas
>
This archive was generated by hypermail 2.0.0 : Thu Sep 30 2010 - 16:06:16 UTC