Limit the number of record from a join

Question

I have this entity

@Entity
public class RentAmount {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long rentAmountId;

    private BigDecimal unpaidBalance;

    private BigDecimal cashAdvance;

    private BigDecimal totalRentAmount;

    private LocalDate paymentRentDueDate;

    private LocalDate paymentDueDate;

    @OneToMany(cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.LAZY)
    @OrderBy
    private Set<RoomPayment> roomPaymentList;

    @OneToOne
    private Lodger lodger;
}

My query

select r from RentAmount r Join fetch r.lodger l join fetch l.bailList b join fetch r.roomPaymentList p where r.paymentRentDueDate <= :date and b.paymentPeriod=:paymentPeriod order by r.rentAmountId

RoomPaymentList can be very huge, i search a way to limit the number of record returned.

Is there a way to limit the number of value from roomPaymentList?

score 4 · Answer 1 · edited May 23 '17 at 12:26

4

If I understand you right - you want to limit number of RoomPaymentList rows for each RentAmount, but not limit rows number in result set. If so, ther is no such way in JPA. But you can write some native query or stored procedure and limit it in subquery etc (Example of such query for MS SQL).

edited May 23 '17 at 12:26

Community

1
1

answered Mar 16 '16 at 06:52

Javasick

2,753
1
23
35

ya exactly, jpa seem very limited. – robert trudel Mar 23 '16 at 18:47

score 0 · Answer 2 · answered Mar 15 '16 at 19:01

0

If you have a plain MySql Query you can do that by appending

Limit 5;

to your query. Oracle offers the ROWNUM-alternative:

ROWNUM <= 5;

(as described here http://www.w3schools.com/sql/sql_top.asp).

answered Mar 15 '16 at 19:01

Christian

303
2
15

score 0 · Answer 3 · answered Mar 16 '16 at 11:48

This might be help for you to get the 10 rows using the jpa query.

String hql = "select r from RentAmount r Join fetch r.lodger l join fetch l.bailList b join fetch r.roomPaymentList p where r.paymentRentDueDate <= :date and b.paymentPeriod=:paymentPeriod order by r.rentAmountId";
Query query = session.createQuery(hql);
query.setParameter("date", "date");
query.setParameter("paymentPeriod", "paymentPeriod");
List results = query.list();

// If you want to fetch only 10 rows then
query.setMaxResults(10); //it takes only an integer parameter

Not really what he was asking, he was curious how to limit inside the join (and not the whole query) — Alkanshel, Feb 20 '17 at 17:51

Limit the number of record from a join

3 Answers3