CODEWITHSUNDEEP
javahibernateannotationsprojectionhibernate-criteria
Aravinthan K
Aravinthan K

Reputation: 1861

how to return criteria Distinct Result

I have Bill and Bill_Details with onetomany and manytoone Relationship. I need a help in getting Bill List.

Pojo

Bill

@OneToMany(cascade={CascadeType.ALL},fetch = FetchType.EAGER)
@JoinColumn(name = "bill_id")
private Set<BillDetails> billDetails = new HashSet<BillDetails>();

BillDetails

@ManyToOne(cascade = { CascadeType.ALL }, fetch = FetchType.EAGER)
@JoinColumn(name = "bill_id")
private Bill billId;

I am using Projections to take Bill value from List.

DaoHibernate

@Transactional
public List<Bill> getbillDetailsByBillId(String billId) {
    Criteria cr = null;
    try {
        cr = getSession().createCriteria(Bill.class,"bill")
                .createAlias("bill.billDetails","billDetails")
                .setProjection(Projections.projectionList()
  // I tried    .setProjectionProjections.distinct(Projections.projectionList()
                        .add(Projections.property("billNo"),"billNo")
                        .add(Projections.property("billDetails.amount"),"billDetails.amount")
                        .add(Projections.property("billDetails.rate"),"billDetails.rate"))                          
                        .add(Restrictions.eq("id", billId))
                        .setResultTransformer(new AliasToBeanNestedResultTransformer(Bill.class));

    } catch (Exception e) {
        System.out.println("Get bill DetailsByBillId Error----------"+e);
        e.printStackTrace();
    }
    System.out.println(cr.list().size());
    return cr.list();
}

Note:

-- > Bill table contains single row

-- > BillDetails table contains four row for this BillId

My criteria Query Returns four Objects instead of single Object. I also tried with distinct feature.

Expected Output :

I need single object that contains BillDetails Objects(4 Values). ie.I explained with sample Json format below

{billNo:231,
    billDetails[{amount:100,rate:1}{amount:200,rate:2}
     {amount:300,rate:30}{amount:400,rate:4}] }

How to get this by Hibernate criteria Query ? Please Help

Upvotes: 2

Views: 642

Answers (1)

JB Nizet
JB Nizet

Reputation: 691735

First of all, your mapping isn't correct. You have a bidirectional association, and one of the side (the one side) must be the inverse of the one side:

@OneToMany(mappedBy = "billId", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private Set<BillDetails> billDetails = new HashSet<BillDetails>();

You should also rename the field billId to bill, given that what it contains is a bill, and not a bill ID.

Now, the problem with your query is that you're using projections for no reason. When using projections, you deliberately choose to return rows containing individual columns. And since the SQL query returns 4 rows you get back 4 bills: one for each row.

You're also making your life unnecessarily complicated by using a Criteria query instead of HQL, which is much more suited for such simple static queries.

But even a HQL query is useless here, since you simply want to get a bill from its ID. All you need is

Bill bill = (Bill) session.get(Bill.class, billId);

This will get the bill, and since you chose to make the OneToMany association as EAGER, it will also immediately load its bill details.

If you hadn't made the association EAGER (and you should really leave it as LAZY), you could use this simple HQL query to load a bill with its details:

select distinct b from Bill bill 
left join fetch bill.billDetails 
where bill.id = :billId

Upvotes: 1

Related Questions