Geek Answers Handbook

How to handle if the request parameter is empty or empty in sleep mode?

I am 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 there were no empty or zero values. But it may be possible to zero or empty for traceEntityVO.getLotNumber (), traceEntityVO.getMfrLocId (), traceEntityVO.getExpDate ().

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

I do not want to build the query dynamically based on the values, if empty or null.

Is it possible?

Thanks in advance.

+8
source share
3 answers

I think you really cannot do this without a dynamic request.

Building such a request, however, is easy with API ( hibernate ) ( JPA ) criteria, did you consider this?

+2
source

Hope the following code sorts your problem. Assuming getMfrDate and getExpDate will return a Date object, while others will return Number or String objects. But you can change 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 "); }
+1
source

Build the query according to your parameters (null or not) using the conditional if () operator, as in the following example for the date:

 String jpqlQuery = "select f from Formation f where f.libelleFormation Like :libelleFormation and f.statutFormation Like :statutFormation and f.codeFormation Like :codeFormation"; if (critereRecherche.getDateDebFormation() != null) { jpqlQuery = jpqlQuery.concat(" and f.dateDebFormation > :dateDebFormation"); } if (critereRecherche.getDateFinFormation() != null) { jpqlQuery = jpqlQuery.concat(" and f.dateFinFormation < :dateFinFormation"); } Query query= em.createQuery(jpqlQuery); query.setParameter("libelleFormation","%"+critereRecherche.getLibelleFormation()+"%"); query.setParameter("statutFormation","%"+critereRecherche.getStatutFormation()+"%"); query.setParameter("codeFormation","%"+critereRecherche.getCodeFormation()+"%"); if (critereRecherche.getDateDebFormation() != null) { query.setParameter("dateDebFormation",critereRecherche.getDateDebFormation(),TemporalTyp.DATE); } if (critereRecherche.getDateFinFormation() != null) { query.setParameter("dateFinFormation",critereRecherche.getDateFinFormation(),TemporalType.DATE); } List<Formation> formations = query.getResultList(); return formations;
0
source

More articles:


All Articles