Hibernate HQL nested query

Question

I've three classes that have nested relationships:

public class Route {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToMany(cascade = {CascadeType.DETACH, CascadeType.REFRESH}, fetch = FetchType.EAGER)
    private List<Place> places;
}

public class Place {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @OneToMany(cascade = {CascadeType.DETACH, CascadeType.REFRESH, CascadeType.REMOVE}, mappedBy = "place")
    private List<Image> images;
}

public class Image {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    @ManyToOne(cascade = {CascadeType.REFRESH, CascadeType.DETACH})
    private Place place;

}

I need to fetch a pageable list of images by a route id.

Thats my query:

@Repository
public interface ImageRepository extends CrudRepository<Image, Long> {
    @Query("select place.images from Place place where place.id in (select placeD.id from Place placeD where placeD in (select route.places from Route route where route.id = :routeId)) ")
    Page<List> findAllByRouteId(@Param("routeId") Long routeId, Pageable Page);
}

I've tried different combinations but still no result. I'm new to HQL and read a lot of tutorials and as I understood it requires me to write a connected query as it is now.

But it doesn't work. An error it throws:

InvalidDataAccessResourceUsageException: could not prepare statement

How can I fix it? Or at least where should I dig to?

Ralph · Accepted Answer · 2016-02-23 19:49:25Z

1

Try:

@Query("SELECT DISTINCT i 
        FROM Route AS r
           LEFT JOIN r.places AS p
           LEFT JOIN p.images AS i
        WHERE r.id = :routeId)) ")

answered Feb 23, 2016 at 19:49

Ralph

121k57 gold badges300 silver badges391 bronze badges

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

1 Comment

Mehdzor Over a year ago

It works, thanks! Can you explain it a little? As for dummies) I mean shouldn't it select routes instead of images?

Keshi · Accepted Answer · 2016-02-23 20:06:49Z

1

You can find more information here: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-subqueries

There are some things that we need to take care of when we try to apply raw sql to hibernate.

Other alternative is to simplify the query using JOINs as mentioned.

answered Feb 23, 2016 at 20:06

Keshi

1,07812 silver badges25 bronze badges

Collectives™ on Stack Overflow

Hibernate HQL nested query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related