java - Hibernate criteria, distinct association property -

say have @ least these 2 entities:

class person {    string firstname, lastname;     address address;     manyotherpropertiesandentities ...; }  class address {       string street;       country country;    } 

now, query person table , only persons live on different streets.

that is, ignore persons live on same street, , return 1 of these person, one.

how can perform such query?

is possibly using criteria?

 criteria criteria = session.createcriteria(person.class, "person");   criteria addresscriteria = criteria.createcriteria("address")   criteria.setprojection(                     projections.distinct(                         projections.projectionlist().add(projections.property("address.street"))                     )             ); 

this doesnt work.

i've tried do:

projectionlist.add( projections.sqlprojection("distinct on ( address.street ), ... ", columns.toarray(new string[columns.size()]), types.toarray(new type[types.size()]))); 

but fruitless.

>>>>>>>>>>>>>>>>>>>>>>>edit<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

i able run, , generate sql query returns results in pure sql mode, seems return 0 in hibernate:

list<string> columns = lists.lst();                                         list<type> types     = lists.lst();                                          bondcriteria.setprojection(                                                 projections.sqlprojection ("distinct on ( tvmux2_.polarization ) * "                                                         , columns.toarray (new string[columns.size()])                                                         , types.toarray   (new type[types.size()])                                                 )                                                  // projections.projectionlist().add(projections.distinct(projections.property("polarization")))                                         );                                          resulttransformer resulttransformer = new resulttransformer() {                                                 @override                                                 public list transformlist( list collection ) {                                                         return null;                                                 }                                                  @override                                                 public object transformtuple( object[] tuple, string[] aliases ) {                                                         return null;                                                 }                                         };                                          bondcriteria.setresulttransformer(resulttransformer); 

* 2017 , still hasn't included proper editor able format code indentation , copy , paste not complete hell. feel free scroll horizontally. *

this generates teh following query

select distinct on ( tvmux2_.polarization ) *  tvchannelbond this_ inner join tvchannel tvchannel1_ on this_.channel=tvchannel1_.id inner join tvmux tvmux2_ on this_.mux=tvmux2_.id this_.enabled=true order tvmux2_.polarization asc limit 100 

which return results in non hibernate mode.

however, since sqlprojection method requires supplementation of 3 params, not sure add second , third params. types can not other predefined hibernate types double, string , on.

when debugging resulttransformer, gets transformtuple 0 length tuple[] , aliases[].

might have sqlprojection 0 length types , columns lists.

in sql, this:

select p.* address inner join person p on ... group a.street having p.id = min(p.id) 

this statement selects every distinct street address person minimum id value. instead of min(p.id) can of course use other field , aggregate function match 1 person per street; max(p.id) work, min(p.lastname) won't if there can more 1 "smith" in street.

can transform above sql criteria query?