openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ram <ram_ku...@yahoo.co.in>
Subject Re: SysDate-5 days implmentation
Date Fri, 06 May 2011 02:50:44 GMT
Here is the timestamp, that i was sending for fiveDays to the query.

2011-04-30 17:30:12.323

   I am using Oracle 11g and the openJPA, which is by dafualt in RAD 7.5

        SysDate i can't really use that since i am using Named Query.

Here is my named Query

@NamedQuery(name = "getXXXXByUser", query = "select cust from ECustomer cust inner join cust.eCustXXXXXCollection
emailAddr inner join cust.myXXXXXXXCollection mb inner join mb.myXXXXXXCollection tod  where
LOWER(emailAddr.addres) =LOWER(:email) and tod.todDprtDttm > :fivedays"),


ECustomer is parent table and its having relationships with other tables as one to many 
          I want to use customer object, so that i can populate, whatever i want from the
db.


       
        


--- On Fri, 6/5/11, Michael Dick <michael.d.dick@gmail.com> wrote:

> From: Michael Dick <michael.d.dick@gmail.com>
> Subject: Re: SysDate-5 days implmentation
> To: users@openjpa.apache.org
> Date: Friday, 6 May, 2011, 6:33 AM
> Your query should work, assuming the
> value for the fivedays is of the
> appropriate type (java.sql.Timestamp from what you've
> said).
> 
> If you could post your entities, and table definition to
> pastebin or
> something similar that might help us. Also, which version
> of OpenJPA and
> which database are you using?
> 
> Failing that you could use a native query and rely on the
> database
> (presumably Oracle) to handle the sysdate keyword.
> 
> -mike
> 
> On Thu, May 5, 2011 at 5:47 PM, <ram_kurra@yahoo.co.in>
> wrote:
> 
> > Yes i tries ld that one. But its still diplays older
> than 5 days.My context
> > is something different. I  don't want to use the
> range b/w two dates
> >        Here is my scenario, I want
> to retrieve all the the ticket bookings
> > which are  starts from currentdate-5  and
> Bookings can be any date in
> > future.
> >     As I said previously, in db
> schema the type is date but in jpa entities
> > it's timestamp. Since date in SQL can store the time
> format like 2011-04-12
> > 0:12:23 so we have kept in as date in db schema 
> and to store in this format
> > we are using @temporal.type timestamp we r using
> >
> >
> > > Query q = em.createQuery("Select m from Message m
> "
> > >    + "where m.targetTime >
> :fivedays
> >
> >
> > Sent from my iPhone
> >
> > On May 5, 2011, at 6:19 PM, Rick Curtis <curtisr7@gmail.com>
> wrote:
> >
> > > Did you try Mike's answer?
> > >
> > > Date now = new Date();
> > > Date thirtyDaysAgo = new Date(now.getTime() - (30
> * MS_IN_DAY));
> > >
> > > Query q = em.createQuery("Select m from Message m
> "
> > >    + "where m.targetTime < :now and
> m.targetTime > :thirtyDays");
> > > q.setParameter("now", now);
> > > q.setParameter("thirtyDays", thirtyDaysAgo);
> > >
> > > List<Message> results =
> (List<Message>) q.getResultList();
> > >
> > >
> > >
> > > On Thu, May 5, 2011 at 4:35 PM, ram <ram_kurra@yahoo.co.in>
> wrote:
> > >
> > >> Hi,
> > >>  I am trying to implement sysdate-5 in
> jpa.
> > >>
> > >> select *From emp where joinDate >
> sysdate-5
> > >>
> > >>       The problem is
> in db schema, join date is defined as date, but in
> > >> jpa entites, we have made it as TimeStamp by
> using @temporal so the it
> > can
> > >> store exact timestamp.
> > >>
> > >>     when i tried to query
> by using @namedQuery, i am getting older than
> > 5
> > >> days. But i need data which is just 5 days
> old.
> > >>   I followed the following
> link
> > >>
> > >>
> > http://stackoverflow.com/questions/2539035/how-to-do-a-timestamp-comparison-with-jpa-query
> > >>
> > >>    but still i am getting same
> problem. Any ideas on this.
> > >>
> > >>
> > >> Thanks
> > >>
> > >>
> > >>
> >
> 

Mime
View raw message