我怎么能连接两个属性到一个属性使用休眠条件查询 [英] how can i concatenate two properties into one property using hibernate criteria query

问题描述

例如，有2个房屋号码和PIN码，我想单个房产作为地址
，房子号码是10，pincode是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（）; 
  

我想做这样的事情

  projectionList.add（Projections.property（address.street+address.houseNumber+address.postcode），address）; 
  

有人可以帮忙。

解决使用HQL

可以使用 concat 表达式，但它只能用于HQL

  select concat（address.street，address.houseNumber，address.postcode）as fullAddress from ... 
  

使用@Formula

如果您想使用 Criteria，，您可以使用 @Formula 。需要为持久

  @Formula（value =concat（f_street，f_houseNumber，f_postcode））添加额外的属性。 
 private String fullAddress; 
  

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

您可以参考 Projection 中的 fullAddress

  projectionList.add（Projections.property（fullAddress），fullAddress）; 
  

我用MySQl测试了它。对于Oracle，您可以尝试使用

  @Formula（value =f_street || f_houseNumber || f_postcode）
私人字符串fullAddress; 
  

扩展Hibernate

我试图扩展 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; 
 
 $ b @Override 
 public String toSqlString（Criteria criteria，int loc，CriteriaQuery criteriaQuery）
抛出HibernateException {
 String result = getFunction（criteriaQuery） .render（StringType.INSTANCE，
 propertiesToColumns（criteria，criteriaQuery），criteriaQuery.getFactory（））; 
返回结果+as y+ loc +'_'; 
} 
 
私人列表< String> propertiesToColumns（Criteria criteria，CriteriaQuery criteriaQuery）{
 List< String> result = new ArrayList< String>（properties.length）; 
 
 for（String property：properties）{
 result.add（criteriaQuery.getColumn（criteria，property））; 
} 
 
返回结果; 
} 
 
 private SQLFunction getFunction（CriteriaQuery criteriaQuery）{
 return criteriaQuery.getFactory（）。getSqlFunctionRegistry（）
 .findSQLFunction（CONCAT_FUNCTION_NAME）; 
 
 $ b @Override 
 public Type [] getTypes（Criteria criteria CriteriaQuery criteriaQuery）
抛出HibernateException {
 return new Type [] {StringType。 INSTANCE}; 
} 
 
} 
  

使用

  projectionList.add（
 new ConcatProjection（address.street，
address.houseNumber，address.postcode ）， 完整地址）; 
  

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

  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.

解决方案

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

这篇关于我怎么能连接两个属性到一个属性使用休眠条件查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！