get id with distinct with hibernate criteria

Question

I want to fetch rows with distinct batch code and id.

The below code is now fetching duplicate batch codes like:

batch1 12,
batch1 45,
batch1 63,
batch2 96,
batch2 96

@Entity
@Table(name = "key")
public class Key implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false, length = 11)
    @Column(name = "batch_code", nullable = false)
    private String batchCode;

    //getter , setter
}



Criteria c = getSession().createCriteria(Key.class);

ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("batchCode"));

c.setProjection(Projections.distinct(projList));
c.setProjection(Projections.property("id"));

if (searchTerm != null && !searchTerm.isEmpty()) {
    c.add(Restrictions.like("keyCode", searchTerm.toUpperCase() + "%"));
}

c.setFirstResult(currPosition);
c.setMaxResults(pageSize);

List<Key> result = c. list();

Ashish Dutt Sharma · Answer 1 · 2016-08-12T09:26:44.640

3

It seems you are setting multiple projections in criteria. The last one overriding the earlier one. To avoid this you can use ProjectionList to add multiple projections like below

    Criteria c = getSession().createCriteria(Key.class);
    ProjectionList projList = Projections.projectionList();
    projList.add(Projections.distinct(Projections.property("batchCode")));
    projList.add(Projections.property("id"));
    c.setProjection(projList);
      if (searchTerm != null && !searchTerm.isEmpty()) {
          c.add(Restrictions.like("keyCode", searchTerm.toUpperCase() + "%"));
      }
    c.setFirstResult(currPosition);
    c.setMaxResults(pageSize);
    List<Key> result = c. list();

edited Aug 12 '16 at 09:26

answered Aug 11 '16 at 12:13

Ashish Dutt Sharma

99
3

thanks for your reply, but is it possible to return all the records with distinct. – boycod3 Aug 11 '16 at 16:34
I did not understand your meaning by "all the records with distinct". There was a slight change in the above snippet I have posted. projection list will not require the distinct clause. – Ashish Dutt Sharma Aug 12 '16 at 09:26
actually your code is not working..showing grammer exception. – boycod3 Aug 12 '16 at 09:27
yes. it requires a slight change. I have updated the above snippet. Try that. – Ashish Dutt Sharma Aug 12 '16 at 09:29
i'm not getting result as expected..batch code is repeating more than 2 times. – boycod3 Aug 12 '16 at 10:11
could you please provide your expected results. – Ashish Dutt Sharma Aug 12 '16 at 19:10
i want distinct batch code with their id like, `csc 1 bsc 2 hhhd 8` – boycod3 Aug 13 '16 at 08:36

score 2 · Answer 2 · edited May 23 '17 at 10:28

I've found this question that is similar to yours but it has not been solved.

In the comments, the OP stated that this little change may be the solution of your problem:

 ProjectionList projList = Projections.projectionList();
 projList.add(Projections.property("batchCode"));
 projList.add(Projections.property("id"));     
 c.setProjection(Projections.distinct(projList));

Notice also that in the above linked question no entity class is set to the Criteria.

 Criteria c = this.createCriteria();

So a List<Object[]> is returned by the execution of the list method. I don´t know if you're gonna get a ClassCastException trying to obtain Key objects

Ashish Dutt Sharma · Accepted Answer · 2016-08-16T13:20:45.463

I guess the scenario you are trying is not valid. Batch code and id field is not a unique combination neither a composite key. Now in below table if you need only unique batch code you can create SQL query like “Select distinct batchcode from ” but what I understand from the conversation is you are expecting a complete record batchcode and id for distinct batch code. In this scenario how system determine which record needs to return for any duplicate batch code i.e abc. Here system don’t have any hint whether to return (1, abc) or (2, abc) or (4, abc). If this is the use case then you need to validate the business scenario what actually you are trying to achieve.

get id with distinct with hibernate criteria

3 Answers3

Id | BatchCode

1 | abc

2 | abc

3 | xyz

4 | abc