Duplicate rows using Hibernate native query with return-join

hibernate, nativequery

Solution

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

/**
 * Transformer, that returns only distinct rows from the set.
 *
 * Distinction is based on all <return alias/> 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 registed it using

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

and this works quite well.

Problem

I'm using Hibernate (3.3.x) and I have two entities: ``` public class FtChargeAcctPkgDtl { private FtChargeAcctPkgDtlId id; private Set<FtChargeAcctPkgRate> ftChargeAcctPkgRates; } ``` and ``` public class FtChargeAcctPkgRate { private FtChargeAcctPkgRateId id; } ``` (left other attributes and setters out for simplicity). I have a native query: ``` <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 query is supposed (I want it to) return one row for every item in pkgDtl, with FtChargeAcctPkgDtl#ftChargeAcctPkgRates filled in. But in fact it returns one row for every item in ft_charge_acct_pkg_rate. Lets say there are 5 rows in the main (pkgDtl) table and 50 in the joined one (average 10 pkgRates for a single pkgDtl). When I invoke the query using ``` Query query = session.getNamedQuery("readSymbolsFtPackages"); query.list(); ``` I get 50 rows. 45 of those are duplicates however. I want to get those 5 pkgDtls and every one with filled in pkdRates. Is there a way to do this in hibernate?

Original source