CriteriaQuery month and year filter

Question

I'm trying to make a criteria query (JPA/Hibernate)in a model which contain one column with date (Oracle 11G) . For example, i have

1. 13-JAN-09
2. 15-JAN-09
3. 16-MAR-09

And my function is like:

public MyEntity getEntitiesFromCertainFilters(int a, int b, java.util.date c)
{
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery< MyEntity > query = builder.createQuery(MyEntity.class);
    Root< MyEntity > root = query.from(MyEntity.class);

    query.select(root).where (
        builder.equal ( root.get ( "id" ).get ( "codEstablec" ) , establecimiento),
        builder.equal ( root.get ( "id" ).get ( "correlGrupo" ) , correlGrupo),
        //HERE I NEED TO ADD FILTER BY C.MONTH AND C.YEAR FROM THE DATE ATTRIBUTE
        );

    List < MyEntity > resultList = entityManager.createQuery(query).getResultList();  
    return resultList.get(0);
}

If i want to filter by c = "01-JAN-09", it should return:

1. 13-JAN-09
2. 15-JAN-09

Any help would be really appreciated, thanks in advance.

Answer 1

You can use the builder.function method. E.g.

query.select(root).where (
    builder.equal ( root.get ( "id" ).get ( "codEstablec" ) , establecimiento),
    builder.equal ( root.get ( "id" ).get ( "correlGrupo" ) , correlGrupo),
    builder.equal(builder.function("YEAR", Integer.class, root.get("DATE_FIELD") ), C.YEAR),
    builder.equal(builder.function("MONTH", Integer.class, root.get("DATE_FIELD")), C.MONTH) 
    );

Answer 2

I assume you're getting your date values like this question.

Rather than making a messy query comparing individual parts of dates, just create two encompassing queries.

Date c; //this is your provided date.
Date d; //this is your endpoint.

d = new Date(c.getTime());
d.setMonth(d.getMonth()+1); // creates 01FEB09

From then, all you need to do is find dates GREATERTHANOREQUALTO c and LESSTHAN d.