Hibernate issue: @OneToMany annotation returns duplicates

Question

I am facing a problem with Hibernate (4.3.0) where as unidirectional @OneToMany returns duplicates.

My database structure (MySQL with InnoDB) where as 'entry' table has a 1:N relationship with 'entry_address' table. The 'entry' table is the main table and 'entry_address' is a sub-table of 'entry' table.

CREATE TABLE IF NOT EXISTS `entry` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(500) NOT NULL,
  `active` int(1) NOT NULL DEFAULT '0',
  `modifiedTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `createdTS` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `entry` (`id`, `name`, `active`, `modifiedTS`, `createdTS`) VALUES
(1, 'Test1', 0, '2012-11-05 13:41:03', '2012-11-01 10:11:22'),
(2, 'Test2', 1, '2012-11-05 11:19:37', '2012-11-01 10:11:33'),
(3, 'Test3', 1, '2012-11-05 11:19:37', '2012-11-01 10:11:44');

CREATE TABLE IF NOT EXISTS `entry_address` (
  `id` int(10) unsigned NOT NULL,
  `precedence` int(1) NOT NULL DEFAULT '0',
  `line` varchar(255) DEFAULT NULL,
  `line2` varchar(255) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `street2` varchar(255) DEFAULT NULL,
  `zip` int(5) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  UNIQUE KEY `entry_address_uq` (`id`,`precedence`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `entry_address` (`id`, `precedence`, `line`, `line2`, `street`, `street2`, `zip`, `city`, `country`) VALUES
(1, 0, 'Line4.1', 'Line4.2', 'Street4.1', 'Street4.2', 9488, 'Schellenberg', 'Liechtenstein'),
(2, 10, 'Line1.1', 'Line1.2', 'Street1.1', 'Street1.2', 9492, 'Eschen', 'Liechtenstein'),
(2, 20, 'Line2.1', 'Line2.2', 'Street2.1', 'Street2.2', 9490, 'Vaduz', 'Liechtenstein'),
(2, 30, 'Line3.1', 'Line3.2', 'Street3.1', 'Street3.2', 9494, 'Schaan', 'Liechtenstein'),
(3, 10, 'Line5.1', 'Line5.2', 'Street5.1', 'Street5.2', 9492, 'Eschen', 'Liechtenstein'),
(3, 20, 'Line6.1', 'Line6.2', 'Street6.1', 'Street6.2', 9492, 'Eschen', 'Liechtenstein');

ALTER TABLE `entry_address`
  ADD CONSTRAINT `entry_address_fk` FOREIGN KEY (`id`) REFERENCES `entry` (`id`);

Here's the minimal code of "entry" entity.

import java.util.Collection;
import javax.persistence.*;

@Entity
@Table(name = "entry")
public class Entry {
    @Id
    @GeneratedValue
    @Column(name = "id")
    private Integer id;

    @Column(name = "name")
    private String name;

    @OneToMany(fetch=FetchType.EAGER)
    @JoinColumn(name = "id")
    private Collection<EntryAddress> addresses;

    @Override
    public String toString() {
        return String.format("Entry [id=%s, name=%s, addresses=%s]", id, name, addresses);
    }
}

Here's the minimal code of "entry_address" entity:

import javax.persistence.*;

@Entity
@Table(name = "entry_address")
public class EntryAddress {
    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "line")
    private String line;

    @Override
    public String toString() {
        return String.format("EntryAddress [line=%s]", line);
    }
}

This is the query done by Hibernate (looks good!): Hibernate: select this_.id as id0_1_, this_.name as name0_1_, addresses2_.id as id0_3_, addresses2_.id as id1_3_, addresses2_.id as id1_0_, addresses2_.line as line1_0_ from entry this_ left outer join entry_address addresses2_ on this_.id=addresses2_.id

But if I run JUnit using:

import java.util.Collection;

import junit.framework.Assert;
import li.pitschmann.transaction.dao.EntryDao;
import li.pitschmann.transaction.entity.Entry;

import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.AbstractTransactionalJUnit4SpringContextTests;

@ContextConfiguration(locations={"file:**/web-spring.xml"})
public class EntryDaoTest extends AbstractTransactionalJUnit4SpringContextTests {
    @Autowired
    private EntryDao entryDao;

    @Test
    public void findAllEntries() {
        Collection<Entry> entries = entryDao.findEntries();

        Assert.assertNotNull(entries);

        for (Entry e : entries) {
            System.out.println("++: " + e);
        }
        // Assert.assertEquals(3, entries.size());

    }
}

import java.util.Collection;

import org.hibernate.SessionFactory;
import org.springframework.transaction.annotation.Transactional;

import li.pitschmann.transaction.dao.EntryDao;
import li.pitschmann.transaction.entity.Entry;

public class EntryDaoImpl implements EntryDao {
    private SessionFactory sessionFactory;

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    @Transactional
    public Collection<Entry> findEntries() {
        return sessionFactory.getCurrentSession().createCriteria(Entry.class).list();
    }

    public void setSessionFactory(SessionFactory sessionFactory) {
        this.sessionFactory = sessionFactory;
    }
}

Spring XML (most important part, Spring 3.1.2.RELEASE):

<tx:annotation-driven transaction-manager="transactionManager" />
<context:annotation-config />
<!-- MySQL DataSource -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.jdbc.Driver" />
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/entry_db" />
    <property name="user" value="root" />
    <property name="password" value="" />
</bean>
<!-- Session Factory -->
<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan">
        <list>
            <value>li.pitschmann.transaction.entity</value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</prop>
            <prop key="hibernate.show_sql">true</prop>
        </props>
    </property>
</bean>
<!-- Transaction Manager -->
<bean id="transactionManager" class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

The console log is:

++: Entry [id=1, name=Test1, addresses=[EntryAddress [line=Line4.1]]]
++: Entry [id=2, name=Test2, addresses=[EntryAddress [line=Line1.1], EntryAddress [line=Line1.1], EntryAddress [line=Line1.1]]]
++: Entry [id=2, name=Test2, addresses=[EntryAddress [line=Line1.1], EntryAddress [line=Line1.1], EntryAddress [line=Line1.1]]]
++: Entry [id=2, name=Test2, addresses=[EntryAddress [line=Line1.1], EntryAddress [line=Line1.1], EntryAddress [line=Line1.1]]]
++: Entry [id=3, name=Test3, addresses=[EntryAddress [line=Line5.1], EntryAddress [line=Line5.1]]]
++: Entry [id=3, name=Test3, addresses=[EntryAddress [line=Line5.1], EntryAddress [line=Line5.1]]]

I also tried to use @OneToMany(fetch=FetchType.LAZY) instead of FetchType.EAGER - same issue with duplicate addresses.

++: Entry [id=1, name=Test1, addresses=[EntryAddress [line=Line4.1]]]
++: Entry [id=2, name=Test2, addresses=[EntryAddress [line=Line1.1], EntryAddress [line=Line1.1], EntryAddress [line=Line1.1]]]
++: Entry [id=3, name=Test3, addresses=[EntryAddress [line=Line5.1], EntryAddress [line=Line5.1]]]

Expectation

This is my expecation (3 entry objects with different addresses):

++: Entry [id=1, name=Test1, addresses=[EntryAddress [line=Line4.1]]]
++: Entry [id=2, name=Test2, addresses=[EntryAddress [line=Line1.1], EntryAddress [line=Line2.1], EntryAddress [line=Line3.1]]]
++: Entry [id=3, name=Test3, addresses=[EntryAddress [line=Line5.1], EntryAddress [line=Line6.1]]]

Is there a bug in Hibernate or am I doing something wrong? Hope someone can help me to find the root cause?! Thank you :-)

Answer 1

You might need to modify your method like so:

@SuppressWarnings("unchecked")
@Transactional
public Collection<Entry> findEntries() {
    return sessionFactory.getCurrentSession()
      .createCriteria(Entry.class)
      .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
      .list();
}

Also, change addresses to a Set:

@OneToMany(fetch=FetchType.EAGER)
@JoinColumn(name = "id")
private Set<EntryAddress> addresses;

Edit:

Oh...In EntryAddress you have id defined as the @Id but it is not unique. You should make id the primary key and have it auto increment like you do in Entry. Then create another field in EntryAddress that is the foreign key to Entry called something like entry_id.