How to handle if query parameter is null or empty in hibernate?

Question

I'm using JPA, hibernate 3.

String sqlQuery = " FROM TraceEntityVO  where lotNumber =:lotNumber and mfrLocId=:mfrLocId and mfrDate=:mfrDate and qtyInitial=:qtyInitial and expDate=:expDate";
Query query = entityManager.createQuery(sqlQuery)
                .setParameter("lotNumber", traceEntityVO.getLotNumber())
                .setParameter("mfrLocId", traceEntityVO.getMfrLocId())
                .setParameter("mfrDate", traceEntityVO.getMfrDate())
                .setParameter("qtyInitial", traceEntityVO.getQtyInitial())
                .setParameter("expDate", traceEntityVO.getExpDate());

This query works like a charm when the there were no empty or null values. But there could be possible of null or empty value for traceEntityVO.getLotNumber(),traceEntityVO.getMfrLocId(),traceEntityVO.getExpDate().

In this case the value 'null' or '' is checked against the variable instead of is null condition. How do I handle when I'm not sure about the parameter value, either null or empty?

I don't want to construct the query dynamically based on the values if empty or null.

Is this possible?

Thanks in advance..

Flavio · Accepted Answer · 2012-07-10 12:19:27Z

3

I think you really can't do that without a dynamic query.

Building such a query however is easy with the criteria API (hibernate) (JPA), did you consider it?

answered Jul 10, 2012 at 12:19

Flavio

12k3 gold badges37 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prathap Over a year ago

I fixed with dynamic query. I constructed query based on the conditions and passed the constructed query to entity manager. It works.. Thanks.

Java SE · Accepted Answer · 2012-07-10 22:02:35Z

2

I hope following code will sort your problem. Assuming getMfrDate and getExpDate will return Date Object and others either Number or String objects. But you can modify IsEmpty according to return types.

String sqlQuery = " FROM TraceEntityVO  where lotNumber :lotNumber 
and mfrLocId :mfrLocId and mfrDate :mfrDate and qtyInitial :qtyInitial and 
expDate :expDate";

Query query = entityManager.createQuery(sqlQuery)
            .setParameter("lotNumber", isEmpty(traceEntityVO.getLotNumber()))
            .setParameter("mfrLocId", isEmpty(traceEntityVO.getMfrLocId()))
            .setParameter("mfrDate", isEmpty(traceEntityVO.getMfrDate()))
            .setParameter("qtyInitial", isEmpty(traceEntityVO.getQtyInitial()))
            .setParameter("expDate", isEmpty(traceEntityVO.getExpDate()));


private String isEmpty(Object obj) {

    if(obj!=null) {

        if (obj instanceof java.util.Date) {
            return " = to_date('"+obj.toString()+"') ";
        } else if(obj instanceof String) { 
            return " = '"+obj.toString()+"' ";
        } else if (obj instanceof Integer) {
            return " = "+obj.toString()+" ";
          }
    } 

    return new String(" is null ");
 }

answered Jul 10, 2012 at 22:02

Java SE

2,0734 gold badges19 silver badges26 bronze badges

1 Comment

Dean Hiller Over a year ago

do you risk sql injection attack here as it's sort of like adding a string in if object is a String?

Collectives™ on Stack Overflow

How to handle if query parameter is null or empty in hibernate?

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related