Using Projecions to fetch a particular column from child table

Question

I have two tables

Loan (id, amount, duration)

LoanStatus(id, status, loan_id)   // just an example, but it has lot more fields in this table

Loan.java

public class Loan{

    private int id;
    private int amount;
    private int duration;
    private LoanStatus loanStatus;

    // getter and setters

}

LoanStatus.java

public class LoanStatus{   // just an example, but it has many fields than what actually given

    private int id;
    private String status;
    private Loan loan;

    //getter and setters

}

Now I would like to get only amount , duration , loanStatus.status using Projections. I've used createAlias() to successfully fetch that particular column, but the problem occurs when setting it to a setter.

Criteria criteria = getSession().createCriteria(Loan.class,"loan");
    criteria.createAlias("loan.loanStatus", "loanStatus")
            .setProjection(Projections.projectionList().add(Projections.property("id"),"id")
            .add(Projections.property("amount"),"amount")
            .add(Projections.property("duration"),"duration")
            .add(Projections.property("loanStatus.status"), "loanStatus"))
            .add(Restrictions.eq("id", id))
            .setResultTransformer(Transformers.aliasToBean(Loan.class));
    return criteria.list();

I've an error which as follows.

IllegalArgumentException occurred while calling setter for property [com.site.dto.Loan.loanStatus (expected type = com.site.dto.LoanStatus)]; target = [com.site.dto.Loan@4083974a], property value = [Pending]

So I'm getting my expected column value "Pending", but the problem is when setting it to a setter. I've seen many question for projections, but most of them was based on Restrictions using Projections but not fetching a child's particular column using projections.

Master Slave · Accepted Answer · 2015-03-01T20:47:23.650

2

Write your own custom transformer. The following implementation might be exactly what you need

https://github.com/samiandoni/AliasToBeanNestedResultTransformer . The usage example as written in their docs

class Person {
  private Long id;
  private String name;
  private Car car;
  // getters and setters
}

class Car {
  private Long id;
  private String color;
  // getters and setters
}

List<Person> getPeople() {
  ProjectionList projections = Projections.projectionList()
    .add(Projections.id().as("id"))
    .add(Projections.property("name").as("name"))
    .add(Projections.property("c.id").as("car.id"))
    .add(Projections.property("c.color").as("car.color"));

  Criteria criteria = getCurrentSession().createCriteria(Person.class)
    .createAlias("car", "c")
    .setProjection(projections)
    .setResultTransformer(new AliasToBeanNestedResultTransformer(Person.class));

  return (List<Person>) criteria.list();
}

// each car of Person will be populated

edited Mar 01 '15 at 20:47

answered Mar 01 '15 at 17:16

Master Slave

27,771
4
57
55

But exactly in reverse, I've loanStatus as an object in loan class for one to one mapping and also for easy restrictions based on status. To be straight, I need only status field from loanStatus object, but it should be available as loanStatus object instead of just a string coz I've some further jaxb XML conversions – The Coder Mar 01 '15 at 17:33
I've edited an answer, didn't notice that the Loan class was an Entity as well, I tought it was simple a data transfer object – Master Slave Mar 01 '15 at 17:45
Thanks.. I too thought about that. But I've many statuses present in loan status, so I don't think its a good practise to create transient field for each status. I'm just wondering is it possible to get a child's particular fields as child object instead of making a transient field in parent object. – The Coder Mar 01 '15 at 17:53
For eg, if I call loan.getLoanStatus().getStatus(), it should return status. But if I call loan.getLoanStatus().getSomeOtherFields(), it should return null. This is my expected output. Like fetching only few columns in parent table using projections, I should also able to fetch only needed columns in child AS child object but not as a field in parent object – The Coder Mar 01 '15 at 17:58
was about to suggest writting a custom transformer, and found an implementation online that you could most likely use as is – Master Slave Mar 01 '15 at 18:14
Just had a look at it and I think, that's the kind of exact solution I've been looking. Will implement that now. Thanks. btw, you could edit your answer to make that link as an exact answer for helping future visitors.. – The Coder Mar 01 '15 at 19:28
Just to inform you, I do get nested child object as child object by using AliasToBeanNestedResultTransformer, but it's fetching all the fields of parent and child no matter what the only fields I've defined in Projections. – The Coder Mar 02 '15 at 06:04
@Master Slave if i have list of car objects in peron class can still be used? – Hadi J Aug 25 '15 at 05:12

Using Projecions to fetch a particular column from child table

1 Answers1

Linked