distinct clause not working properly in hibernate
I am trying to get Distinct states of US from my SQL , but its distinct functionality not working properly in Hibernate, like Alaska is coming 6 times (its present in SQL 6 times but I want distinct only)
StatesProvinces statesProvinces = new StatesProvinces();
ArrayList<StatesProvinces> allStates = new ArrayList<StatesProvinces>();
ArrayList<String>开发者_C百科 states = new ArrayList<String>();
Session session = sessionFactory.openSession();
Criteria crit = session.createCriteria(StatesProvinces.class);
crit.add(Restrictions.eq("country", country));
crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
List rsList = crit.list();
If you have 6 different Alaska provinces in your database, each with a different ID, and you ask for distinct ones, of course you'll get the 6 ones. They are distinct, since they have different IDs.
You could load distinct province names, but you would get String instance, not StatesProvices instances.
The fix is probably to cleanup your data, and make sure with a unique constraint that the table doesn't ever contain more than one province with a given name for a given country.
精彩评论