CODEWITHSUNDEEP
javahibernatepostgresqlhql
Geo Thomas
Geo Thomas

Reputation: 1149

Hibernate query to get all distinct rows based on a where condition

I have a POJO named SiteProperty as follows:

public class SiteProperty
{
    private int sitePropertyId;
    private int siteId;
    private String  key;
    private String  value;
    private String  type;
}

Is there any way to get all the distinct values of value for the given siteId? Also the same value might be there with siteId 0. So basically the query should get all the rows for siteId 0 and then if any of the value is present for the specified siteId then that row has to be returned insted of the one for siteId 0.

I use PostgreSQL database for my application.

I am very new to this. Any suggestions would be appreciated. Thanks.

Basicaly I have many value and key pairs in the table for siteId 0, and this are the default values. There can be a different value for the same key for some values of siteId. If for a siteId there is an value corresponding to a key it has to be returned, else the value corresponding to the same key for siteId 0 should be returned. And the return data should be a list of type SiteProperty

I have tried a sample SQL query in the pgAdmin tool to test the result I get:

select * from site_property where site_key IN (SELECT DISTINCT site_key FROM site_property where site_id = 0 or site_id = 3) and site_id = 0 or site_id = 3;

This query is just a trial, the result gives me a combination of data with both the siteId.

Upvotes: 0

Views: 893

Answers (1)

Konstantin Yovkov
Konstantin Yovkov

Reputation: 62854

You need to add a Projection to a Criteria (which is a builder-class for a query):

Criteria criteria = getCurrentSession().createCritiera(SiteProperty.class);
criteria.add(Projections.distinct(Projections.property("value")));
criteria.add(Restrictions.eq("siteId", 0));
List<String> resultList = critiera.list();

Upvotes: 1

Related Questions