Duplicate rows using Hibernate native query with return-join

Question

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?

Using select distict wouldnt help? why dont you modify your query to do exactly what you need? — Eduard, Aug 22 '12 at 14:42
Hi @Eduard, database distinct won't help as the DATABASE rows are different. But as hibernate transforms the DB rows into a set of java objects, there are duplicities. — Ondrej Skalicka, Aug 23 '12 at 05:59

score 3 · Answer 1 · answered Aug 22 '12 at 14:28

3

Use:

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

Regards

answered Aug 22 '12 at 14:28

Manu Navarro

700
6
9

1

Hi, seems I've simplified my problem a bit too much. I have several more return aliases in the query and since DISTINCT_ROOT_ENTITY returns just the last column, this wont help me. – Ondrej Skalicka Aug 23 '12 at 05:57
Nice solution, but actually method *setResultTransformer* is deprecated. But till Hibernate 6 will be work as it is. Look on: https://vladmihalcea.com/2017/04/03/why-you-should-use-the-hibernate-resulttransformer-to-customize-result-set-mappings/ – hariprasad Jul 20 '17 at 11:17

score 2 · Accepted Answer · answered Aug 23 '12 at 08:24

Inspired by @manu-navarro I came 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 registed it using

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

and this works quite well.

Duplicate rows using Hibernate native query with return-join

2 Answers2

Linked