hibernate, check if object exists and null values

Question

I am using hibernate to save records (i.e. objects) to a database. Before saving my objects, I want to verify if the database already contains this object. (The primary key is just an incremental key and cannot be used for this.)

I am creating a HQL statement at runtime to check the existance of a record with these attributes (i.e. column1-3).

The resulting query should look like:

from myTable where column1 is null and column2 = :column2 and column3 = :column3'

Because sometimes the columns can contain null values, I check the value of the attributes, if it is a NULL value, then I use a is instead of a = in this query (e.g. the column1 is :column1 in the above statement).

Because I start to realize that I am doing a lot of work to achieve something reletively crucial, I am starting to wonder if I'm on the right track. Is there an easier way to check the existance of objects ?

EDIT: I slightly rephrased my question after I realized that also column1 is :column1 does not work when :column1 parameter is set to null. Apparently the only syntax that seems to work as expected is column1 is null. So, it seems like you just cannot use wildcards when searching for null values. But that does not change the main aspect of my question: should I really be checking all this stuff at runtime ?

One simple solution would be to add unique constraint on the combination of columns1-3 in your db. This way automatically you will not be allowed to add duplicates to db. But if you have the scenario where multiple records can contain null values for these columns, this will not work. — Waqar, Jul 20 '15 at 12:05
WHERE ((column1 is null AND :column1Param is null) OR column1=:column1Param ) AND (the same for column2...) ? — StanislavL, Jul 20 '15 at 12:20
maybe you can create a checksum of the columns and save it as separate column - even if the columns will be null, the checksum will have value (for example "null,null,null"), than create index on it and you will have to calculate the checksum for new object and check only this column — Zavael, Jul 20 '15 at 12:21
@Waqar I agree that there should be constraints as a safeguard. There is no denying that. But I'm not convinced that relying on an exception is a good idea. The throwing of exceptions is an expensive operation in java, therefore relying on exceptions is usually bad practice. — bvdb, Jul 20 '15 at 22:28

score 1 · Accepted Answer · answered Jul 26 '15 at 10:21

This is the best way that I found so far.

I prefer to put my filters in a map. The key refers to the property (i.e. map.put("column1", Integer.valueOf(1))).

There is a Restritions.eqOrIsNull method that simplifies the conversion to a Criterion object. The following method converts an entire Map to a List<Criterion>.

public List<Criterion> mapToCriterion(Map<String, Object> params)
{
  if (params == null) return null;

  // convert the parameter map to a list of criterion
  List<Criterion> criterionList = new ArrayList<>(params.size());
  for (Map.Entry<String, Object> entry : params.entrySet())
    criterionList.add(Restrictions.eqOrIsNull(entry.getKey(), entry.getValue()));
  return criterionList;
}

Later on, I use the List<Criterion> to build a Criteria object.

 Criteria criteria = session.createCriteria(clazz);
 if (criterionList != null)
 {
   for(Criterion criterion : criterionList) 
     criteria.add(criterion);
 }

 // get the full list
 @SuppressWarnings("unchecked")
 List<T> objectList = criteria.list();

My general impression is still that there are missing several convenience methods here (e.g. Criteria#addAll(List<Criterion>) would have been nice).

hibernate, check if object exists and null values

1 Answers1