Duplicate rows using native Hibernate return-join query

Question

I am using Hibernate (3.3.x) and I have two objects:

public class FtChargeAcctPkgDtl { private FtChargeAcctPkgDtlId id; private Set<FtChargeAcctPkgRate> ftChargeAcctPkgRates; } 

and

 public class FtChargeAcctPkgRate { private FtChargeAcctPkgRateId id; } 

(other attributes and settings for simplicity).

I have my own request:

 <sql-query name="readSymbolsFtPackages"> <return alias="pkgDtl" class="somepackage.FtChargeAcctPkgDtl"/> <return-join alias="pkgRate" property="pkgDtl.ftChargeAcctPkgRates"/> <![CDATA[ SELECT {pkgDtl.*}, {pkgRate.*} FROM ft_charge_acct_pkg_dtl pkgDtl JOIN ft_charge_acct_pkg_rate pkgRate ON pkgRate.master_seq_no = pkgDtl.master_seq_no -- just primary key AND pkgRate.pkg_id = pkgDtl.pkg_id ]]> </sql-query> 

The assumption is that the query (I want it) returns one row for each item in pkgDtl, populating FtChargeAcctPkgDtl # ftChargeAcctPkgRates. But actually it returns one line for each element in ft_charge_acct_pkg_rate.

Suppose there are 5 rows in the main (pkgDtl) table and 50 in the combined table (average 10 pkgRates for one pkgDtl). When I call a request with

 Query query = session.getNamedQuery("readSymbolsFtPackages"); query.list(); 

I get 50 lines. However, 45 of them are duplicates. I want to get these 5 pkgDtls and each with filled pkdRates. Is there any way to do this in sleep mode?

Answer 1

Inspired by @ manu-navarro, I came up with this transformer:

 /** * Transformer, that returns only distinct rows from the set. * * Distinction is based on all &lt;return alias/&gt; items. */ public class DistinctResultTransformer extends BasicTransformerAdapter { @Override public List transformList(List collection) { // set of objects already in the result Set<List<Object>> existingRows = new HashSet<List<Object>>(); List result = new ArrayList(); for (Object row : collection) { // array must be converted to list as array has equals() implemented using == List<Object> rowAsList = Arrays.asList((Object[]) row); if (!existingRows.contains(rowAsList)) { existingRows.add(rowAsList); result.add(row); } } return result; } } 

and then registered with

 Query query = session.getNamedQuery("readSymbolsFtPackages"); query.setResultTransformer(new DistinctResultTransformer()); query.list(); 

and it works pretty well.

Answer 2

Using:

 Query query = session.getNamedQuery("readSymbolsFtPackages"); query.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); query.list(); 

Hello