Hibernate Date Criteria fails but HQL works

Question

Hibernate seems to incorrectly handle date ranges using the Criterion API in Oracle. The SQL Query itself seems correct (copied it from Hibernate and executed it manually). So,

given

Clazz<Bar> clazz;
Date start, end;

this fails

List<Bar> bars = sessionFactory.getCurrentSession()
    .createCriteria(clazz)
    .add(Restrictions.between("timestamp", start, end))
    .list();

and this

 List<Bar> bars = sessionFactory.getCurrentSession()
     .createCriteria(clazz)
     .add(Restrictions.ge("timestamp", start))
     .add(Restrictions.le("timestamp", end))
     .list();

but this works

List<Bar> bars = sessionFactory.getCurrentSession()
    .createQuery("from Bar b where b.timestamp > ? and b.timestamp < ?")
    .setDate(0, start)
    .setDate(1, end)
    .list();

The fail observation is:

The number of Bar results returned are the same (and correct)
but in the criterion cases a Bar with a List<Foo> returns roughly 10x more Foo objects than the corresponding SQL query does. All the extra Foo objects are identical copies.

EDIT

@Entity
public class Bar {

    @Id 
    private String id;

    @Temporal(TemporalType.TIMESTAMP)
    private Date timestamp;

    @ManyToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)
    @JoinTable(
        name = "bar_foo",
        joinColumns = { @JoinColumn(name = "barid") },
        inverseJoinColumns = { @JoinColumn(name = "fooid") }
    )
    private List<Foo> params;
}

@Entity
public class Foo {
    @Id private String id;
}

@JohanSjöberg Does [this question and the first answer to it](http://stackoverflow.com/questions/6122808/restrictions-between-for-date-in-hibernate-criteria) helps? — Kohányi Róbert, Dec 12 '11 at 18:39
@Kohányi, ty but the problem isn't about inclusive/exclusive search, it's about hibernate returning more `Foo`s than there even exist in the DB. — Johan Sjöberg, Dec 12 '11 at 18:47
Please post all the objects foo that are brought by the working query — radu florescu, Dec 12 '11 at 19:19

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

Try to rewrite query

List<Bar> bars = sessionFactory.getCurrentSession()
 .createCriteria(Bar.class)
 .add(Restrictions.ge("timestamp", start))
 .add(Restrictions.le("timestamp", end))
 .list();

It's the same like you did, but I've never seen examples which variable of class type like you use them.

You should check this link also HQL(hibernate) timestamp range match also this Restrictions Between for Date in Hibernate Criteria

Edit 2: Check the examples in here : http://www.javalobby.org/articles/hibernatequery102/

edited May 23 '17 at 12:27

Community

1
1

answered Dec 12 '11 at 17:56

radu florescu

4,315
10
60
92

Thanks for answering. It doesn't make a difference. The range query seems to be working, but hibernate assembling the result does not. – Johan Sjöberg Dec 12 '11 at 18:08
ty, although I get the correct *number* of result back already – Johan Sjöberg Dec 12 '11 at 18:11
post the solution then:p please – radu florescu Dec 12 '11 at 18:12
perhaps my error description is insufficient. But the problem is that I get to many `Foo` objects back. If I should get 1 `Foo` back, I end up with ~10 `Foo` copies instead (but only using Criteria API). – Johan Sjöberg Dec 12 '11 at 18:16

score 0 · Accepted Answer · answered Dec 12 '11 at 21:23

0

I can't remember or look up the rationale behind this right now but what you are seeing might be expected behavior. Try this:

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

answered Dec 12 '11 at 21:23

tscho

2,024
15
15

Hibernate Date Criteria fails but HQL works

2 Answers2