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();
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.
5 | klm