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
– Eduard, Commented Aug 22, 2012 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
– Ondrej Skalicka, Commented Aug 23, 2012 at 5:59

Manu Navarro · Accepted Answer · 2012-08-22 14:28:08Z

3

Use:

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

Regards

answered Aug 22, 2012 at 14:28

Manu Navarro

7401 gold badge6 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ondrej Skalicka Over a year ago

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.

hariprasad Over a year ago

Nice solution, but actually method setResultTransformer is deprecated. But till Hibernate 6 will be work as it is. Look on: vladmihalcea.com/2017/04/03/…

Ondrej Skalicka · Accepted Answer · 2012-08-23 08:24:06Z

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.

Collectives™ on Stack Overflow

Duplicate rows using Hibernate native query with return-join

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related