Hi, Thank you so much Andrus
I have implemented a solution using SQLTemplate, I think as you said, It's
the best bet.
A great solution, even better once I have been discovering stuff as: the
#bind and #result directives, and the possibility of DataRow treatment in
order to build structure flexible queries
I'm including a single example about the solution in case it results
usefull for someone or for comments
public List getRowsByDateAndID(int d, int m, int y, int id){
//String pgTemplate = "SELECT *"
String pgTemplate = "SELECT #result('TIME' 'java.util.Date')"
+ " FROM Table1"
+ " WHERE id = #bind($id) and"
+ " extract(day from timestampfield) = #bind($d) and"
+ " extract(month from timestampfield) = #bind($m) and"
+ " extract(year from timestampfield)= #bind($y)";
SQLTemplate query = new SQLTemplate(Table1.class, pgTemplate);
query.setFetchingDataRows(true);
//query.setTemplate(PostgresAdapter.class.getName(), pgTemplate);
Map parameters = new HashMap();
parameters.put("d", d);
parameters.put("m", m);
parameters.put("a", y);
parameters.put("id", new Integer(id));
query.setParameters(parameters);
System.out.println(query.toString());
System.out.println(query.getDefaultTemplate());
List list = performQuery(query);
return list;
}
Using and Handling results:
List list = getRowsByDateAndID(4,4,2009,4551);
Iterator itr = list.iterator();
while(itr.hasNext()) {
DataRow d = (DataRow)itr.next();
System.out.println("A-"+ d);
Map row = (Map)d;
System.out.println("field:"+row.get("TIME"));
//System.out.println("field:"+row.get("time"));//for select * case
}
Cayenne Rocks
EMERSON
On Thu, Mar 15, 2012 at 1:44 AM, Andrus Adamchik <[email protected]>wrote:
> Your best bet is using SQLTemplate in this case. Not as OO as SelectQuery,
> but certainly not a stored procedure either :)
>
> http://cayenne.apache.org/doc30/sqltemplate-query.html
>
> Cheers,
> Andrus
>
> On Mar 14, 2012, at 4:22 PM, Emerson Castañeda wrote:
>
> > HI everyone
> >
> > I have a table with a timestamp field, so I'm thinking about how to
> write
> > the next query without define a store procedure, but directly from my
> java
> > code:
> >
> > select *
> > from table1
> > where
> > id = 1 and
> > extract(day from timestampField)='04' and
> > extract(month from timestampField)='04' and
> > extract(year from timestampField)='2009'
> >
> > I'd like to know if that is possible using cayenne, maybe some thing like
> > this?
> >
> >
> > SelectQuery query = new SelectQuery(Table1.class);
> > query.andQualifier(<??EXPRESSION???>)
> >
> > OR
> >
> > SelectQuery query = new SelectQuery(Table1.class);
> > query.andQualifier( ExpressionFactory.likeIgnoreCaseExp(
> > Table1.timestampproperty, "%"));
> >
> >
> > Thank you for your time
> >
> > EMERSON
>
>
