Hibernate Criteria: How to create a query for many search fields?

Question

I need to fetch details from DB if any of the fields are entered, the fields are as below

1. Date

2. Code

3. Action

4. Status

5. UserName

6. Application.

Kindly help me as any of the fields can be entered and not necessarily all values need to entered.

Thanks & Regards, Jafer

Answer 1

You can simply test if following fields are not empty or null, if not null or empty add a restrictions. For example.

    //assuming you have Person.class
    Criteria cr = session.createCriteria(Person.class);

   //prevent to add restrictions if date is null
    if(date !=null)
    cr.add(Restrictions.eq("date", date));


    if(!username.equals(""))
    cr.add(Restrictions.eq("userName"),username);
    //test other fields

    List results = cr.list();

    //return results

For more basic info. You can check this.

Answer 2

public List searchAccommodation(Date startDate, Date endDate, Country country, AccommodationType type, Integer capacity)
    Criteria criteria = session.createCriteria(Accommodation.class);

    if (startDate != null) {
        criteria.add(Expression.ge("availabilityDate",startDate);
    }                
    if (endDate != null) {
        criteria.add(Expression.le("availabilityDate",endDate);
    }                
    if (country != null) {
          criteria.add(Expression.eq("country",country);
    }                
    if (capacity != null) {
          criteria.add(Expression.ge("capacity",capacity);
    }                
    if (type != null) {
          criteria.add(Expression.eq("type",type);
    }                
    List results = criteria.list();
    //
    // Execute the query
    //
    return query.list();
}

Answer 3

Criteria doesn't fit for this scenario. I suggest you build HQL dynamically!

Answer 4

HQL VS Criteria API - Performance

There is a difference in terms of performance between HQL and CriteriaQuery, everytime you fire a query using criteriaQuery, it creates a new alias for the table name which does not reflect in the last queried cache for any DB. This leads to an overhead of compiling the generated SQL, taking more time to execute.

Criteria respects the laziness settings in your mappings and guarantees that what you want loaded is loaded. This means one Criteria query might result in several SQL immediate SELECT statements to fetch the subgraph with all non-lazy mapped associations and collections. If you want to change the "how" and even the "what", use setFetchMode() to enable or disable outer join fetching for a particular collection or association. Criteria queries also completely respect the fetching strategy (join vs select vs subselect).

HQL respects the laziness settings in your mappings and guarantees that what you want loaded is loaded. This means one HQL query might result in several SQL immediate SELECT statements to fetch the subgraph with all non-lazy mapped associations and collections. If you want to change the "how" and even the "what", use LEFT JOIN FETCH to enable outer-join fetching for a particular collection or nullable many-to-one or one-to-one association, or JOIN FETCH to enable inner join fetching for a non-nullable many-to-one or one-to-one association. HQL queries do not respect any fetch="join" defined in the mapping document.