Hi,

How to select the distinct rows with the particular field like there is a particular field say "type" with that i have "TT" field value repeated twice where it should get only the distinct value with the row instead of fetching all the rows.

How can this be acheived using the criteria queries or HQL.

Pls Anyone help me in this.

Thanks,
Ambika.

Normally, hibernate fetches information from database distinctly only on the mapped <id> column. If you wish to get distinct based on another column, use projections.

Project all columns you want to build your data class and add a distinct on your column of interest

e.g.

Projection projection = Projections.projectionList();
pojection.add(Projections.property("P1");
pojection.add(Projections.property("P2");
pojection.add(Projections.property("P3");
pojection.add(Projections.property("P4");
.
.
.
pojection.add(Projections.distinct(Projections.property("TT"));

Then set this projection to the criteria.

This would return a List<Object[]>. Iterate this list and build your class. This is however a costly operation and must be avoided.

Alternativley, you may map another class with <id> mapped to the column you want. Make sure you map that with mutable="false" lest you mess around with your data.

Do not forget to post a reply if this answers your question.

==============================

Shreenath Sreenivas
Senior Software Engineer (TL)
Ocimum Biosolutions