java 如何使用休眠条件查询将两个属性连接成一个属性

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

提问by Amit Anand

for example there are 2 properties house number and pincode and i want a single property as address like house number is 10 and pincode is 110064 and combine address property is 10,110064 this is my code

例如，有 2 个属性的门牌号和密码，我想要一个属性作为地址，如门牌号为 10，密码为 110064，合并地址属性为 10,110064，这是我的代码

  final Criteria criteria= getDatabaseSession().createCriteria(Application.class, "application");
 final ProjectionList projectionList=Projections.projectionList();
 criteria.setProjection(projectionList);

projectionList.add(Projections.property("address.street"), "street");
 projectionList.add(Projections.property("address.postcode"), "postcode");
 projectionList.add(Projections.property("address.houseNumber"), "houseNumber");

 criteria.createAlias("application.applicationCase", "applicationCase", JoinType.INNER_JOIN);
 criteria.createAlias("applicationCase.property", "property");
 criteria.createAlias("property.address", "address");
 criteria.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
 return (Map<String, Object>) criteria.uniqueResult(); 

and i want to do something like this

我想做这样的事情

   projectionList.add(Projections.property("address.street"+"address.houseNumber"+"address.postcode"),"address");

can somebody help.

有人可以帮忙吗。

回答by v.ladynev

Using HQL

使用 HQL

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

您可以使用concat表达式，但它只能与 HQL 一起使用

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

Using @Formula

使用@Formula

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

如果你想使用，Criteria,你可以使用@Formula. 需要为持久化添加额外的属性

@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.

您需要指定列名（而不是属性名），因为 Hibernate 将它们按原样添加到 SQL 中。使用连接时不是很方便——您需要在公式中指定由 Hibernate 生成的别名。

You can refer to the fullAddressin the Projection

您可以参考fullAddress在Projection

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

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

我已经用 MySQl 对其进行了测试。对于 Oracle，您可以尝试使用

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

Extending Hibernate

扩展休眠

I have tried to extend the Projectionto add concatfunction to the Criteria. I test it for the simplest case.

我试图将Projection添加concat功能扩展到Criteria. 我在最简单的情况下测试它。

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");