Hibernate Community • View topic - Retrieving too many objects in a many-to-many mapping

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Retrieving too many objects in a many-to-many mapping

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

md_luce

Post subject: Retrieving too many objects in a many-to-many mapping

Posted: Thu Mar 22, 2007 4:35 pm

Newbie

Joined: Tue Feb 28, 2006 5:03 pm
Posts: 15
Location: North Carolina

I am using Hibernate v 3.2.2 and jdk 1.5. I am trying to create a one-to-many mapping that uses a join table. I have definied a uni-directional mapping where you can traverse from a Batch to it's Loans. When I create a query using "SELECT COUNT(b.loans.id) FROM Batch AS b WHERE b.id = :id" I get the correct number back (3). But, if I create a query using "SELECT b.loans FROM Batch AS b WHERE b.id = :id ORDER BY b.loans.loanNumber DESC" it will retrieve 3x the correct number in the list. I am sure that I am just mapping this incorrectly, but I cannot see what I am doing wrong. And it makes no sense to me why the Select Count is working. Any help will greatly appreciated.

Here are my mapping files

Code:

    <class name="testHibernate.Batch" 
           table="TestBatch" 
           schema="dbo" 
           dynamic-insert="true"
           dynamic-update="true">
           
        <id name="id" type="java.lang.Integer" column="id" >
            <generator class="increment" />
        </id>
        <property name="name" type="java.lang.String" column="name" length="80" not-null="true" />

        
        <set name="loans" table="TestMapping">
            <key column="batchId"/>
            <many-to-many column="loanId" unique="true" class="testHibernate.Loan" lazy="proxy"/>
        </set>
    </class>

Code:

    <class name="testHibernate.Loan" 
           table="TestLoan" 
           schema="dbo" 
           dynamic-insert="true"
           dynamic-update="true">
           
        <id name="id" type="java.lang.Integer" column="id" >
            <generator class="increment" />
        </id>
        <property name="loanNumber" type="java.lang.String" column="name" length="80" not-null="true" />
    </class>

Here is my test case:

Code:

        String queryString = "SELECT b.loans FROM Batch AS b WHERE b.id = :id ORDER BY b.loans.loanNumber DESC";
        String countString = "SELECT count(b.loans.id) FROM Batch AS b WHERE b.id = :id";
        Integer id = new Integer(1);
        Query listQ = HibernateSessionFactory.getSession().createQuery(queryString);
        listQ.setParameter("id", id);
        List loans = listQ.list();
        Query countQ = HibernateSessionFactory.getSession().createQuery(countString);
        countQ.setParameter("id", id);
        Long count = (Long) countQ.uniqueResult();
        logger.debug("  Count -> " + count);
        logger.debug("  # Retrieved -> " + loans.size());

The output is:

Code:

2007-03-22 16:17:19:953|DEBUG|testHibernate.TestMapping   |  Count -> 3
2007-03-22 16:17:19:953|DEBUG|testHibernate.TestMapping   |  # Retrieved -> 9

The SQL it generates is:

Code:

    select
        loan2_.id as id2_,
        loan2_.name as name2_ 
    from
        dbo.TestBatch batch0_ 
    inner join
        TestMapping loans1_ 
            on batch0_.id=loans1_.batchId 
    inner join
        dbo.TestLoan loan2_ 
            on loans1_.loanId=loan2_.id,
        TestMapping loans3_,
        dbo.TestLoan loan4_ 
    where
        batch0_.id=loans3_.batchId 
        and loans3_.loanId=loan4_.id 
        and batch0_.id=? 
    order by
        loan4_.name DESC

and

Code:

    select
        count(loan2_.id) as col_0_0_ 
    from
        dbo.TestBatch batch0_,
        TestMapping loans1_,
        dbo.TestLoan loan2_ 
    where
        batch0_.id=loans1_.batchId 
        and loans1_.loanId=loan2_.id 
        and batch0_.id=?

Top

md_luce

Post subject: Additional information

Posted: Fri Mar 23, 2007 8:45 am

Newbie

Joined: Tue Feb 28, 2006 5:03 pm
Posts: 15
Location: North Carolina

In the previous post notice that the problem with the main SELECT is that hibernate is joining to 2 superfluous tables, TestMapping loans3_ and TestLoan loan4_. I have discovered that that is generated as a result of the ORDER BY clause. I have included the HQL I am using and the resulting SQL generated by Hibernate. Am I naming the order by property incorrectly or is this a bug in Hibernate?

The original select statement was:

Code:

SELECT b.loans FROM Batch AS b WHERE b.id = :id ORDER BY b.loans.loanNumber DESC

and results in the following SQL and 3x the results in the database:

Code:

    select
        loan2_.id as id2_,
        loan2_.name as name2_ 
    from
        dbo.TestBatch batch0_ 
    inner join
        TestMapping loans1_ 
            on batch0_.id=loans1_.batchId 
    inner join
        dbo.TestLoan loan2_ 
            on loans1_.loanId=loan2_.id,
        TestMapping loans3_,
        dbo.TestLoan loan4_ 
    where
        batch0_.id=loans3_.batchId 
        and loans3_.loanId=loan4_.id 
        and batch0_.id=? 
    order by
        loan4_.name DESC

if I change the select to:

Code:

SELECT b.loans FROM Batch AS b WHERE b.id = :id

The correct SQL is generated as follows and the correct number of object returned:

Code:

    select
        loan2_.id as id2_,
        loan2_.name as name2_ 
    from
        dbo.TestBatch batch0_ 
    inner join
        TestMapping loans1_ 
            on batch0_.id=loans1_.batchId 
    inner join
        dbo.TestLoan loan2_ 
            on loans1_.loanId=loan2_.id 
    where
        batch0_.id=?

[/code]

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum