using projections on multiple columns - joined tables

Question

Good day.

So i have mapping @Id @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "discount_fares_id_sequence") @SequenceGenerator( name = "discount_fares_id_sequence", sequenceName = "discount_fares_id_seq", allocationSize = 1 ) private Long id;

@Column
private Long discount;

@Column(name = "fare_code", length = 255)
private String fareCode;

@ManyToOne
@JoinColumn(name = "aircompany_id", foreignKey = @ForeignKey(name = "fk_discfares_ref_aircompany_id"))
private AircompanyRB aircompanyId;

how do i use projections to extract only

discountFare id discount fareCode and aircompany_id (the key of AircompanyRB)

So basically all the fields of DiscountFares plus only key of AircompanyRB?

I tried

    DetachedCriteria criteria = DetachedCriteria.forClass(DiscountFares.class)
        .createCriteria("aircompanyId")
        .setProjection(Projections.projectionList()
        .add(Projections.property("id"))
        .add(Projections.property("discount"))
        .add(Projections.property("fareCode")));

    List<DiscountFares> result = criteria.getExecutableCriteria(sessionFactory.getCurrentSession()).list();

but it throws exception saying AircompanyRB doesnt have a field "discount" (yes, it does not- DiscountFares have)

thank you very much! any help is highly appreciated

JB Nizet · Accepted Answer · 2014-08-11 06:36:56Z

1

First of all, you're making your life difficult by using a Criteria query instead of a dead simple HQL query:

select df.id, df.discount, df.fareCode, ac.id 
from DiscountFares df
left join df.aircompanyId ac

Now, if you really want to use a Criteria, just translate the above query:

Criteria c = session.createCriteria(DiscountFares.class, "df");
c.createAlias("df.aircompanyId", "ac", JoinType.LEFT_OUTER_JOIN);
c.setProjection(Projections.projectionList()
    .add(Projections.property("df.id"))
    .add(Projections.property("df.discount"))
    .add(Projections.property("df.fareCode"))
    .add(Projections.property("ac.id")));

answered Aug 11, 2014 at 6:36

JB Nizet

694k94 gold badges1.3k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tania Over a year ago

thank you that's awesome, but i need to get an object DiscountFare,

JB Nizet Over a year ago

Then use select df, ad.id from from DiscountFares df ...

Collectives™ on Stack Overflow

using projections on multiple columns - joined tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related