queries with hibernate

Question

I have a database include eventType and event Tables

eventType 

  - id
  - name

event

  - id 
  - name
  - location
  - eventType_id
  - eventSubType_id

where eventType_id and eventTypeSubtype_id reference to eventType Table.

What i want to do with hibernate is select all events that have :

• eventType in (2,6)
• and eventSubType in (2,null)

I did :

EventCriteria.createCritria("eventType").add(Expression.in("id"),new Long [] {2L,6L});
EventCriteria.createCriteria("eventSubType").add(Expression.in("id", new Long [] {2L,null}));

The output not consider the events that have eventype with id (2 or 6) and eventSubType is null, it consider only the events that have eventype with id (2 or 6) and eventSubType is (2). The output must Be both

Thanks

Answer 1

Assuming that your eventType numbers are ids...

select event 
from Event event
where event.eventType_id in (2,6)
and (eventSubType is null
     or eventSubType = 2)

Answer 2

You used eq instead of in...