How does Criteria.DISTINCT_ROOT_ENTITY actually work?

Question

I am currently trying to build an SQL (plain SQL, directly executed on the database - I'm not using hibernate for this in particular) to get data from my database. It's an export based on a Java-implemented (hibernate) criteria query to which I want to add additional information from additional tables.

In Java Criteria queries, I have a statement as follows:

Criteria criteria = session.createCriteria(MyEntity.class);
                criteria.createCriteria("fk").add(Restrictions.in("sysid", <<someListofIDsfromPreviousQuery>>))
                criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
                return criteria.list();

Which results in a 1-1 result set from a list of IDs from Table A ("someListofIDsFromPreviousQuery") to which there are multiple results in Table B ("MyEntity.class").

I know what DISTINCT_ROOT_ENTITY does but I'm wondering how it actually works, meaning, what would an equivalent SQL look like?

Example according to the criteria query above:

SELECT COUNT(*) FROM TABLEA a 
WHERE a.property1=someValue AND a.property2=someOtherValue;

Returns 2000 results. (This is my list of IDs)

SELECT COUNT(*) FROM TABLEA a
JOIN TABLEB b ON b.fk = a.sysid -- <-- this is basically equivalent to what the criteria query above does, no?
WHERE a.property1=someValue AND a.property2=someOtherValue;

Returns 2500 results.

How does Critera.DISTINCT_ROOT_ENTITY actually chose which entry of TABLEB to pick as its "distinct entity"? Or am I getting something wrong here?

If it makes a difference: I'm trying to write my query in OracleDB SQL Dialect.

score 0 · Accepted Answer · edited Aug 02 '23 at 05:40

After some searching, experimenting, and generally being confused - it seems as if DISTINCT_ROOT_ENTITY does not make any actual decision making in how it determines the values from Table B. It just takes the first value of the result set from the JOIN "randomly".

So the code I'm trying to base my SQL off of is actually wrong and dangerous for the application it's in. Good to know. Cough

score -1 · Answer 2 · answered Nov 01 '21 at 12:16

-1

I found out that we can get distinct result using following two different ways

Projections.distinct(Projections.property("id"));

Here this is the Projections giving the same results as

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

answered Nov 01 '21 at 12:16

Arun_Raja

1

I'm trying to replicate this functionality with plain SQL, this has nothing to do with java/hiberante besides that this is where I discovered it. Check out the SO Answer I linked in my question, it already explains what you've said here. I'm interested in the mechanism behind these/that hibernate feature. – Eskir Nov 01 '21 at 12:27

How does Criteria.DISTINCT_ROOT_ENTITY actually work?

2 Answers2