Session.createCriteria(table class namw) criteria method retrieving duplicate rows

Question

We are using session.createCriteria(HLink.class) method to retrieving records from table but it is pulling duplicate records in list.

When I see in the list objects it showing duplicate records of first row from table.

Below is the hibernate mapping file

<hibernate-mapping package="com.stockmann.framework.service.hierarchy">
    <class name="HLink" table="STOCKMANN.HLINK" lazy="true">

        <composite-id>
            <key-property name="linkId" column="LINKID" type="integer"/>
            <key-property name="fromHierarchy" column="FROMHIERARCHY" type="integer"/>
            <key-property name="fromNode" column="FROMNODE" type="integer"/>
        </composite-id>

        <property name="toHierarchy" column="TOHIERARCHY" type="integer"/>
        <property name="toNode" column="TONODE" type="integer"/>

    </class>
</hibernate-mapping> 

When I tried with query in database it is returning correct records.

select * 
from stockmann.hlink 
where LINKID = 130 
  and FROMHIERARCHY = 1 
  and FROMNODE = 3743 
  and TOHIERARCHY = 30;

Records are :

130 1   3743    30  8
130 1   3743    30  186
130 1   3743    30  190

But when i see in List object in java it is showing as below:

List object :[
com.stockmann.framework.service.hierarchy.HLink@695fc665[linkId=130,fromHierarchy=1,fromNode=3743,toHierarchy=30,toNode=8],
com.stockmann.framework.service.hierarchy.HLink@695fc665[linkId=130,fromHierarchy=1,fromNode=3743,toHierarchy=30,toNode=8],
com.stockmann.framework.service.hierarchy.HLink@695fc665[linkId=130,fromHierarchy=1,fromNode=3743,toHierarchy=30,toNode=8]
]

Please help me in this to get actual records from table instead one record duplication.

Answer 1

You have a design flaw in your table or mapping.

Your mapping says, {LINKID, FROMHIERARCHY, FROMNODE} is the primary key (PK) of HLink entity. But in contrast, your schema allows insertion of multiple records having the same combination of {LINKID, FROMHIERARCHY, FROMNODE} in common as proven by your raw SQL query response. You should define a unique index or primary key for {LINKID, FROMHIERARCHY, FROMNODE}.

Alternatively you shlouldn't lie to Hibernate, telling her that this was a primary key - it simply isn't.

Why do entities get duplicated?

When Hibernte reads the SQL query response, for each row with different PK values it would instantiate a new HLink entity object. Hibernate reads 130 1 3743 30 8 and creates an instance for the newly discovered PK touple {130, 1, 3743}, putting that entity instance to session l1 cache. For both additinal records Hibernate recognizes "Oh, I already have that entity with PK {130, 1, 3743} in cache, so I don't need to inspect the full result record but simply add the cached instance again to the query result List".

Edit:

To solve this issue, you can either add a PK to your table structure and map it as @Id, or find a combination of columns that is unique and map this as composite id as you already did with the non unique column combination. Hibernate does not seem to work without an Id as the answer to this question states.