cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Emerson CastaƱeda <eme...@gmail.com>
Subject Re: Howto Include Time/Date Functions in Query Expressions
Date Thu, 15 Mar 2012 15:18:51 GMT
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 <andrus@objectstyle.org>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
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message