HSQL query using limit in join part

Question

I'm new in Spring Boot. I tried to find an answer for my question on SO and google, but I can't find an exact answer for it.

I'm trying to create a function in my Spring Boot JpaRepository class which returns a Customer by id with a limited number of ascending ordered AccountingLogs related to the Customer.

My line of code in JpaRepository:

@Query("select c from Customer left outer join AccountingLog a on a.customer.id = c.id where c.id= :id")
Customer getWithLimitedNumberOfLastTransactions(@Param("id") Long id, @Param("limit") int limit);

The Customer class only with the relevant code:

@Entity
public class Customer {        
    //...
    @OneToMany(mappedBy = "customer", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    @Column(nullable = true)
    @JsonManagedReference
    private Set<AccountingLog> accountingLogs;
    //...
}

The customer class only with the relevant code:

@Entity
public class AccountingLog {
    //...
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "customer_id")
    @JsonBackReference
    private Customer customer;
    //...
}

So I'm looking for an HSQL query which selects one customer by id with a specified number (variable named limit) of accounting logs in ascending order (last in first) related to the customer.

Thank you for your help!

score 0 · Answer 1 · answered Jul 05 '17 at 10:10

You can write a native query in @Query if you are using MySql:

@Query(value="select c from Customer left outer join AccountingLog a on a.customer.id = c.id where c.id= :id limit :limit", nativeQuery = true)

or Oracle:

@Query(value="select c from Customer left outer join AccountingLog a on a.customer.id = c.id where c.id= :id and ROWNUM < :limit", nativeQuery = true)

HSQL query using limit in join part

1 Answers1