inapproriate result using sum method in HQL

Question

Hello i am new to HQL and i stuck with a query in HQL.

Structure of the problem

i have two classes Project and Income in hibernate with one (project) to many(income) relationship.

there are so many incomes of a particular project.

What i want to do?

i want to retrieve all of the project, each with sum of its all income. let suppose there are two projects with 3000(sum of various different incomes for project one) and 4000(sum of various different incomes for project two) total income amount and i want to retrieve them as a list of Objects. this list of the Object should contain project one and two with their individual total amount.

What i am doing

   public java.util.List<Object[]> retrieveFromTo(int firstResult, int maxResult) {
    session = sessionFactory.openSession();

    Query query = session.createQuery(
              "select p.projectId, "
            + "p.projectDateOfCommencement, "
            + "p.projectName, "
            + "pi.investorName, "
            + "sum(bc.incomeAmount), "
            + "sum(ab.expenseAmount), "
            + "sum(bc.incomeAmount)-sum(ab.expenseAmount), "
            + "p.projectStatus, "
            + "p.projectCompletitionDate "
            + "from Project as p, Investor as i "
                      + "left outer join p.projectExpenses as ab "
                      + "left outer join p.projectIncome as bc "
                      + "left outer join p.projectInvestor as pi "
                      + "group by p.projectId, pi.investorId "                          
                      + "order by p.projectId desc ")
            .setFirstResult(firstResult)
            .setMaxResults(maxResult);

    List<Object[]> projects = query.list();

    session.close();

    return projects;        
}

Result of the query is producing inappropriate result.

let suppose there are two projects and if the total incomes of both project is suppose 3000 then this query retrieve list of the project where total income of first is 3000(correct) but total income of the second project is just double of the first one that means 6000 instead it should be 3000 as well.

Please someone let me know that what exactly i want to add or remove from my query to get my desired output. Thanks in advance !

Answer 1

From what I understood from your problem, you seem to have OneToMany relationships in this manner:

public class Project {
    ...

    @OneToMany
    List<Expense> expenses;

    @OneToMany
    List<Income> incomes;

    ...
}

Lets suppose your database structure is like this;

Project

id          name 
1           helloWorld

project_income

proj_id     income_id
1           1

project_expense

proj_id     expense_id
1           1
1           2

income

id          amount
1           200

expense

id          amount
1           500
2           600

Your query produces result in this manner

proj_id     proj_name       income_id       income_amount       expense_id      expense_amount
1           helloWorld      1               200                 1               500
1           helloWorld      1               200                 2               600

and so when you sum incomeAmount and expenseAmount, you get a result like this:

proj_id     proj_name       sum(income_amount)      sum(expense_amount)
1           helloWorld      400                     1100

Because of this, when you sum incomeAmount and expenseAmount, you (sometimes) get double or triple values. Its the nature of join statements. You may wanna take a look as to what is inner and outer joins and how do they produce results.

To get the desired results, one (one of the many available) solution is that you can use multiple select statements as shown below:

Query query = session.createQuery(
          "select p.projectId, "
        + "p.projectName, "
        + "(select sum(i.incomeAmount) from Income i where i.incomeProject = p), "
        + "(select sum(e.expenseAmount) from Expense e where e.expenseProject = p), "
        + "from Project as p"
                  + "group by p.projectId, pi.investorId ")
        .setFirstResult(firstResult)
        .setMaxResults(maxResult);

Output:

proj_id     proj_name       sum(income_amount)      sum(expense_amount)
1           helloWorld      200                     1100

I hope it helps.