ConcatenatorProjection concatenates columns using Criteria

denis.etienne · **Posted:** Thu Sep 07, 2006 2:47 pm

Hibernate version:3.1.2

Hello,

We have a table PEOPLE with 2 COLUMNS: XNAME, YNAME

XNAME is SET and YNAME is EMPTY

OR

YNAME is SET and XNAME is EMPTY

Forces:
We have to order them by NAME ASC, my idea is to create a specific Projection to do the concatenation, i.e. ORDER BY CONCAT(XNAME,YNAME)
We have to use Criteria API.

So I wrote a specific Projection for this purpose (concatenation).
The question is was there a simpler solution (except creating a view) ???

Code follows:

Code:

public class MyConcatenatorProjection extends SimpleProjection {

   private String propertyName;
   private String property1;
   private String property2;
   private boolean grouped;

   public MyConcatenatorProjection(String prop) {
      this(prop, false);
   }

   protected MyConcatenatorProjection(String prop, boolean grouped) {

      if (prop.split(" ").length != 2)
         throw new RuntimeException("Invalid property name: two space-separated properties");
      this.property1 = prop.split(" ")[0];
      this.property2 = prop.split(" ")[1];
      this.grouped = grouped;
   }

   public String toString() {
      return "concat( "+property1+","+property2+")";
   }

   public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery)
         throws HibernateException {
      return new Type[] { Hibernate.STRING };
   }

   public String toSqlString(Criteria criteria, int position,
         CriteriaQuery criteriaQuery) throws HibernateException {
      return new StringBuffer().append(
            "concat(" + criteriaQuery.getColumn(criteria, property1)+
            ","+criteriaQuery.getColumn(criteria, property2)+")" )
            .append(" as y").append(position).append('_').toString();
   }
}

deepaksurti · **Joined:** Thu May 12, 2005 5:54 pm **Posts:** 16

Added a MultipleCountProjection.java and ExtraProjections.java.

Quote:

Source code below:

MultipleCountProjection.java:

Code:

package org.hibernate.criterion;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.type.Type;

/**
 * A count for style :  count (distinct (a || b || c))
 * @author Deepak Surti
 */
public class MultipleCountProjection extends AggregateProjection {

   private boolean distinct;

   protected MultipleCountProjection(String prop) {
      super("count", prop);
   }

   public String toString() {
      if(distinct) {
         return "distinct " + super.toString();
      } else {
         return super.toString();
      }
   }

   public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      return new Type[] { Hibernate.INTEGER };
   }

   public String toSqlString(Criteria criteria, int position, CriteriaQuery criteriaQuery) 
   throws HibernateException {
      StringBuffer buf = new StringBuffer();
      buf.append("count(");
      if (distinct) buf.append("distinct ");
        String[] properties = propertyName.split(";");
        for (int i = 0; i < properties.length; i++) {
           buf.append( criteriaQuery.getColumn(criteria, properties[i]) );
             if(i != properties.length - 1) 
                buf.append(" || ");
        }
        buf.append(") as y");
        buf.append(position);
        buf.append('_');
        return buf.toString();
   }
   
   public MultipleCountProjection setDistinct() {
      distinct = true;
      return this;
   }
   
}

ExtraProjections.java

Code:

package org.hibernate.criterion; 

public final class ExtraProjections
{ 
    public static MultipleCountProjection countMultipleDistinct(String propertyNames) {
        return new MultipleCountProjection(propertyNames).setDistinct();
    }
} 

Sample Usage:

Code:

String propertyNames = "titleName;titleDescr;titleVersion"

criteria countCriteria = ....

countCriteria.setProjection(ExtraProjections.countMultipleDistinct(propertyNames);

denis.etienne · **Posted:** Fri Sep 08, 2006 2:59 pm

Cleaner design !!

Did you find any documentation on how to build new Projection classes ?
Thanks.

deepaksurti · **Joined:** Thu May 12, 2005 5:54 pm **Posts:** 16

Hi,

Nope. I did not find any documentation on projections.

I looked up the source code in org.hibernate.criterion package.

Since Projections Factory is final, wrote up the ExtraProjections factory
and mimiced CountProjection as MultipleCountProjection.

I think CountProjection and MultipleCountProjection can be refactored even futher since only toSqlString method differs in this part:

Code:

   String[] properties = propertyName.split(";"); 
   for (int i = 0; i < properties.length; i++) { 
           buf.append( criteriaQuery.getColumn(criteria, properties[i]) ); 
             if(i != properties.length - 1) 
                buf.append(" || "); 
   }

which we can extract into a method called getColumnsFromCriteria.

Let me know what you think.

Cheers.