how can i concatenate two properties into one property using hibernate criteria query

Using HQL

You can use the concat expression, but it can be used only with HQL

select concat(address.street, address.houseNumber, address.postcode) as fullAddress from ...

Using @Formula

If you want to use Criteria, you can use @Formula. It is need to add additional property to the persistent

@Formula(value = "concat(f_street, f_houseNumber, f_postcode)")
private String fullAddress;

You need to specify column names (not property names), because of Hibernate adds them to the SQL as is. It is not very convenient when you use joins — you need to specify aliases, generated by Hibernate, in the formula.

You can refer to the fullAddress in the Projection

projectionList.add(Projections.property("fullAddress"), "fullAddress");

I have tested it with MySQl. For Oracle you can try to use

@Formula(value = "f_street || f_houseNumber || f_postcode")
private String fullAddress;

Extending Hibernate

I have tried to extend the Projection to add concat function to the Criteria. I test it for the simplest case.

public class ConcatProjection extends SimpleProjection {

    private static final String CONCAT_FUNCTION_NAME = "concat";

    private final String[] properties;

    public ConcatProjection(String... properties) {
        this.properties = properties;
    }

    @Override
    public String toSqlString(Criteria criteria, int loc, CriteriaQuery criteriaQuery)
            throws HibernateException {
        String result = getFunction(criteriaQuery).render(StringType.INSTANCE,
                propertiesToColumns(criteria, criteriaQuery), criteriaQuery.getFactory());
        return result + " as y" + loc + '_';
    }

    private List<String> propertiesToColumns(Criteria criteria, CriteriaQuery criteriaQuery) {
        List<String> result = new ArrayList<String>(properties.length);

        for (String property : properties) {
            result.add(criteriaQuery.getColumn(criteria, property));
        }

        return result;
    }

    private SQLFunction getFunction(CriteriaQuery criteriaQuery) {
        return criteriaQuery.getFactory().getSqlFunctionRegistry()
                .findSQLFunction(CONCAT_FUNCTION_NAME);
    }

    @Override
    public Type[] getTypes(Criteria criteria, CriteriaQuery criteriaQuery)
            throws HibernateException {
        return new Type[] { StringType.INSTANCE };
    }

}

Using

projectionList.add(
    new ConcatProjection("address.street", 
        "address.houseNumber",  "address.postcode"), "fullAddress");