按特定列选择不同的实体 [英] Select distinct entities by specific column

问题描述

我有一个包含4列的表：id，column_a和column_b

我的初始查询基本上是

 会话会话=初始化我的休眠会话; 
 Criteria criteria = session.createCriteria（MytableBean.class）; 
 //添加一些限制
返回criteria.list（）
  

返回如下结果：

  id | column_a | column_b | 
 1 | A | 1 | 
 2 | A | 1 | 
 3 | A | 2 | 
 4 | B | 3 | 
 5 | B | 3 | 
  

但是我不想多次获得同样的情侣（column_a，column_b），即使我需要在我的数据库中多次使用它。

所以，我正在寻找一个返回类似于以下结果的查询：

  id | column_a | column_b | 
 | A | 1 | 
 | A | 2 | 
 | B | 3 | 
  

在sql中，我会使用 SELECT DISTINCT column_a，column_b FROM ....

但是我不能使用sql（并且尽可能不要使用hql）。

解决方案

以下将做到这一点。返回Object []的列表。

  Criteria criteria = session.createCriteria（MytableBean.class）; 
 ProjectionList projList = Projections.projectionList（）; 
 projList.add（Projections.property（column_a））; 
 projList.add（Projections.property（column_b））; 
 projList.add（Projections.groupProperty（column_a））; 
 projList.add（Projections.groupProperty（column_b））; 
 
 criteria.setProjection（projList）; 
 return criteria.list（）; 
  

I have a table with 4 columns : id, column_a and column_b

My initial query is basicaly

Session session = initialize my hibernate session;
Criteria criteria = session.createCriteria(MytableBean.class);
// add some constraints
return criteria.list()

And it returns some results like :

id  |  column_a  | column_b | 
1   |  A         | 1        | 
2   |  A         | 1        | 
3   |  A         | 2        | 
4   |  B         | 3        | 
5   |  B         | 3        | 

But I don't want to get several times the same couple (column_a, column_b), even if I need to have it many times in my database.

So, I'm looking for a query which returns a result similar to:

id  |  column_a  | column_b | 
    |  A         | 1        | 
    |  A         | 2        | 
    |  B         | 3        | 

In sql I would use SELECT DISTINCT column_a, column_b FROM ....

But I can't use sql (and either don't want to use hql, as far as possible).

解决方案

The following will accomplish this. A list of Object[] will be returned.

    Criteria criteria = session.createCriteria(MytableBean.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.property("column_a"));
    projList.add(Projections.property("column_b"));
    projList.add(Projections.groupProperty("column_a"));
    projList.add(Projections.groupProperty("column_b"));

    criteria.setProjection(projList);
    return criteria.list();

这篇关于按特定列选择不同的实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！