-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: query based on dates with Criteria
PostPosted: Wed Feb 23, 2005 11:09 am 
Beginner
Beginner

Joined: Thu Oct 23, 2003 1:17 pm
Posts: 44
Read the rules before posting!
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.1.8

I'm sure this is an issue that's been dealt with already, but I don't see it in the docs or the forum.

I have a class called CalendarEvent and a table to match. I love using Criteria to do my queries, but I'm not sure how to do it in this case.

CalendarEvent has a java.util.Date field on it named "start". I'd like to select all the CalendarEvents that have a start date with a given month. Is there a way for me to get at the month field of the Date?

problem #1 - Date doesn't have bean getters and setters.
problem #2 - Date is an object, but isn't an association where I can do a createCriteria("start") and then get at more fields.

The best solution I could come up with is to use something like this:

Code:
Criteria crit = session.createCriteria(CalendarEventTO.class);
crit.add(Expression.between("start", firstOfMonth, lastOfMonth));
crit.list();


where firstOfMonth and lastOfMonth are Dates set up for the first and last of the month.

Even if I was going to try to do my query with HQL, I would still run into the same problems, no?

Any ideas? Suggestions?


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 23, 2005 12:44 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX
Well, in HQL I'd use the sql escape functions. That'd look something like:

... WHERE {fn MONTH(startDate)} BETWEEN :startMonth AND :endMonth

Of course assuming your driver supports sql escape functions ;)


Top
 Profile  
 
 Post subject:
PostPosted: Wed Feb 23, 2005 2:35 pm 
Beginner
Beginner

Joined: Thu Oct 23, 2003 1:17 pm
Posts: 44
Thanks, I'll take a look at that.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 28, 2005 5:08 pm 
Beginner
Beginner

Joined: Thu Oct 23, 2003 1:17 pm
Posts: 44
I was able to get it working with the HQL, and I was also able to get it working with Criterion by making my own Criterion / Expression classes like this:

Code:
import net.sf.hibernate.expression.AbstractCriterion;
import net.sf.hibernate.engine.SessionFactoryImplementor;
import net.sf.hibernate.engine.TypedValue;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.type.IntegerType;
import net.sf.hibernate.util.StringHelper;

import java.util.Map;

public class DateMonthEqExpression extends AbstractCriterion {

   private final String  propertyName;
    private final Integer month;

   DateMonthEqExpression(String propertyName, Integer month) {
      this.propertyName = propertyName;
        this.month = month;
   }

   public String toSqlString(SessionFactoryImplementor sessionFactory, Class persistentClass, String alias, Map aliasClasses) throws HibernateException {
        String sql = StringHelper.join(
         " and ",
         StringHelper.prefix(
                StringHelper.suffix( getColumns(sessionFactory, persistentClass, propertyName, alias, aliasClasses), ")} = ?" ),
                "{fn MONTH(")
      );

        return sql;
   }

   public TypedValue[] getTypedValues(SessionFactoryImplementor sessionFactory, Class persistentClass, Map aliasClasses) throws HibernateException {
        TypedValue tv = new TypedValue(new IntegerType(), month);
        return new TypedValue[] {tv};
    }

   public String toString() {
      return propertyName + " equals " + month;
   }

}


I also made a DateExpression class similar to the Expression class to easily get me my Criterion

and then I can call it like this:

Code:
crit.add(DateExpression.dateMonthEq("start", 3));


That matches any objcets with a "start" date that has a month of March.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.