Problem with collection fetch join and transient field

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

Imagine I have an Account entity which has a collection of Payment entities and a transient field sumPaid:

Code:

 /** */
@Entity
@Table(name = "account")
public class Account {
...
    @Column(name = "summ", nullable = false)
    private BigDecimal summ;
...
    @OneToMany(mappedBy = "account")
    private Set<Payment> payments = new HashSet<Payment>();

    @Transient
    private BigDecimal sumPaid;
...
}

sumPaid is a transient calculated field it's being calculated in the following way:

Code:

..
List<Object[]> tuple = new ArrayList<Object[]>();

tuple = session.createQuery("select distinct acc, (select sum(pays.income) from acc.payments pays) from Account as acc left join fetch acc.payments ").list();

for (Object[] obj : tuple) {
                Account acc = (Account)obj[0];

                BigDecimal sumIncome = (BigDecimal)obj[1];

               acc.setSumPaid(sumIncome);

            }
...

The problem is: the resulting list contains duplicated Account objects, all rows, fetched from db seem not to be grouped by Account. What I mean:

Imagine I have 2 Account objects and 4 Payment objects (each Account is referenced by two Payments):
Account1 <- Payment1
Account1 <- Payment2
Account2 <- Payment3
Account2 <- Payment4

so, if I use a select of this type:

Code:

session.createQuery("select distinct acc from Account as acc left join fetch acc.payments ").list();

I recieve two Account objects with each with its own collection of Payment objects:

Account1: payments = (Payment1, Payment2), sumPaid=null
Account2: payments = (Payment3, Payment4), sumPaid=null

But when I use the mentioned query with additional parameter in select

Code:

session.createQuery("select distinct acc, (select sum(pays.income) from acc.payments pays) from Account as acc left join fetch acc.payments ").list();

I recieve 4 Account objects, two of which are totally equall:

Account1: payments = (Payment1, Payment2), sumPaid=100
Account2: payments = (Payment1, Payment2), sumPaid=100
Account3: payments = (Payment3, Payment4), sumPaid=200
Account4: payments = (Payment3, Payment4), sumPaid=200

It seems that distinct word works in Hibernate only when select has the object itself, but when smth else is selected, all rows are fetched directly as from db select.

Thanks to everyone for the help! It seem's that the answer is quite simple, but so far I will have to remove duplicated objects from the resulting list manually

croudet · **Joined:** Mon Apr 19, 2004 6:54 pm **Posts:** 79

Why don't you use a formula on your sumPaid property?

Christophe

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

Could you please remind what is this? Will it be in the same query or it will result in lots of new selects for each Account entity?

Thanks.

croudet · **Joined:** Mon Apr 19, 2004 6:54 pm **Posts:** 79

Check
http://www.hibernate.org/hib_docs/v3/reference/en/html/mapping.html#mapping-declaration-property

Quote:

An especially powerful feature are derived properties. These properties are by definition read-only, the property value is computed at load time. You declare the computation as a SQL expression, this translates to a SELECT clause subquery in the SQL query that loads an instance:

Code:

<property name="totalPrice"
    formula="( SELECT SUM (li.quantity*p.price) FROM LineItem li, Product p
                WHERE li.productId = p.productId
                AND li.customerId = customerId
                AND li.orderNumber = orderNumber )"/>

Christophe

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

Thanx croudet a lot! This thing helped a lot!:

Code:

@Formula(value="(select coalesce(sum(p.income), 0) from payment p where p.account_id = id)")
    private BigDecimal sumPaid;

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

ghm.... Usage of formula helped, but has one very sensitive shortcoming.. Now, all requests to the Account will result in fetch of sums of payments automatically by formula, allthough this is not needed in many situations (in filters for example, or in fetching other objects, which reference Account and do not need this extra information at all).

The extra hit to database seems very bad thing... May be there is the way to avoid fetching all attributes of Entity bean, but only those, which are needed?

croudet · **Joined:** Mon Apr 19, 2004 6:54 pm **Posts:** 79

You can try to set 'lazy' to true. It needs bytecode instrumentation.

Found in one of hibernate tests:

Code:

<!-- 

  This mapping demonstrates 

     (1) use of lazy properties - this feature requires buildtime 
         bytecode instrumentation; we don't think this is a very
         necessary feature, but provide it for completeleness; if
         Hibernate encounters uninstrumented classes, lazy property
         fetching will be silently disabled, to enable testing
     
     (2) use of a formula to define a "derived property"
     
-->

<hibernate-mapping 
   package="org.hibernate.test.lazycache"
   default-access="field">
    
   <class name="Document" table="documents">
       <cache usage="nonstrict-read-write" include="non-lazy" region="foo"/>
         <id name="id">
          <generator class="native"/>
       </id>
       <property name="name" not-null="true" length="50"/>
       <property name="upperCaseName" formula="upper(name)" lazy="true"/>
       <property name="summary" not-null="true" length="200" lazy="true"/>
       <property name="text" not-null="true" length="2000" lazy="true"/>
       <property name="lastTextModification" not-null="true" lazy="true" access="field"/>
    </class>
    

</hibernate-mapping>

Christophe

croudet · **Joined:** Mon Apr 19, 2004 6:54 pm **Posts:** 79

Or you can rewrite your first query and add a group by clause.

Code:

select acc, sum(pays.income) from Account as acc
left join acc.payments pays
group by acc.id, acc.sum, ...

Christophe

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

croudet wrote:

Code:

select acc, sum(pays.income) from Account as acc
left join acc.payments pays
group by acc.id, acc.sum, ...

Christophe

makes the same as

Code:

select distinct acc, (select sum(pays.income) from acc.payments pays) from Account as acc left join fetch acc.payments

but does not solve the main problem: it does not fetch distinct Account objects, but as many, as payments number (because the second parameter is appointed in select - you can use "select acc, null.." - and Hibernate will return duplicated rows!). The way with lazy parameters looks not well either, I will have to initialize all lazy parameters while the session is open and it will result in additional selects (as many, as Account objects exist).

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

http://www.hibernate.org/117.html#A12

Thanks Christophe for the help, this above is the answer, why distinct doesn't work, if any other parameter, except the object itself, is used in select clause when fetching children. Formula properties seem very dangerous thing as they are used automatically in ANY selects. Thus I will have to remove duplicated object by hands... It's the pay for the automatization, given by Hibernate.

Cameron McKenzie · **Posted:** Thu Jul 31, 2008 4:34 pm

If you grab the collection as a Set, rather than a List, would that not elminate the duplicates, as a Set cannot contain duplicate objects?

fitech · **Joined:** Wed Jul 30, 2008 8:43 am **Posts:** 32

Cameron McKenzie wrote:

If you grab the collection as a Set, rather than a List, would that not elminate the duplicates, as a Set cannot contain duplicate objects?

I tried this, but result objects in Set are duplicated, as arrays do not override 'equals' method and do not compare with other array by object. In this case we should use Arrays.equals(Object[], Object[]), and I need to create my own set to use this functionality. What I mean:

Code:

Set<Object[]> unique = new LinkedHashSet<Object[]>();

unique = new LinkedHashSet(session.createQuery(
                "select ord, (select sum(pays.payedExpenses) from ord.payments pays) from Order as ord left join fetch ord.accounts").list());

            } 
...

We get a set of two Object arrays:
objArray1 ({Order1, 1000});
objArray2 ({Order1, 1000}),

because objArray.equals(objArray2) returns false.

So far I solved the problem this way:

Code:

List<Object[]> temp;

Set<Order> unique = new LinkedHashSet<Order>();

temp = session.createQuery(
                "select ord, (select sum(pays.payedExpenses) from ord.payments pays) from Order as ord left join fetch ord.coWorkers ").list();

            // Removing duplicates
            for (Object[] obj : temp) {
                Order order = (Order)obj[0];

                BigDecimal sumPayedExpenses = (BigDecimal)obj[1];

                order.setSumPayedExpenses(sumPayedExpenses);

                unique.add(order);
            }