CODEWITHSUNDEEP
javaspringhibernatespring-data-jpa
user3603632
user3603632

Reputation: 447

Load all lazy collection at once with hibernate/spring

I have a problem that loading my lazy collections produces a lot of SQL-Statements and I wonder if there is no more efficient way of loading the data.

Situation:

Parent has a lazy collection of Child called children. It is actually a Many-To-Many relation.

I load a list of Parents with a CrudRepository and I need to get all child_ids for each Parent. So every time I access the children collection i executes a new SQL-Statement.

If i load 200 Parents there are 201 queries executes (1 for the list of Parents and 1 for each Parent's children).

Any idea how i can load the data with just one query?

EDIT 1

Parent/Child is probably a bad naming here. In fact i have a Many-To-Many relation.

Here is some code:

@Entity
public class Tour {
    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name="system-uuid",
        strategy = "uuid2")
    @Column(length = 60)
    private String id;

    @ManyToMany
    @JoinTable(
        name="parent_images",
        joinColumns = @JoinColumn(name="tour_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name="image_id", referencedColumnName = "id"),
        foreignKey = @ForeignKey(name = "FK_TOUR_IMAGE_TOUR"),
        inverseForeignKey = @ForeignKey(name = "FK_TOUR_IMAGE_IMAGE")
    )
    private List<Image> images = new ArrayList<>();
}

@Entity
public class Image {
    @Id
    @GeneratedValue(generator = "system-uuid")
    @GenericGenerator(name="system-uuid",
        strategy = "uuid2")
    @Column(length = 40)
    private String id;

    //....
}

// Code to fetch:
@Autowired
TourRepository repo;
List<Tour> tours = repo.findBy(....);


List<String> imageIds = new ArrayList<>();

for(Tour tour : tours){
    imageIds.addAll(tour.getImages().stream().map(b -> b.getId()).collect(Collectors.toList()));
}

Upvotes: 2

Views: 2754

Answers (2)

Adrian Shum
Adrian Shum

Reputation: 40066

As another answer suggested, JOIN FETCH is usually the way to solve similar problem. What happens internally for join-fetch is that the generated SQL will contains columns of the join-fetched entities.

However, you shouldn't blindly treat join-fetch being the panacea.

One common case is you want to retrieve entities with 2 One-To-Many relationships. For example, you have User, and each User may have multiple Address and Phone

If you naively do a from User user join fetch user.phones join fetch users.addresses, Hibernate will either report problem in your query, or generate a inefficient query which contains Cartesian product of addresses and phones.

In the above case, one solution is to break it into multiple queries: from User user join fetch user.phones where .... followed by from User user join fetch user.addresses where .....

Keep in mind: less number of SQL does not always means better performance. In some situation, breaking up queries may improve performance.

Upvotes: 3

M&#225;rio Fernandes
M&#225;rio Fernandes

Reputation: 1862

That's the whole idea behind lazy collections :)

Meaning, a lazy collection will only be queried if the getter for that collection is called, what you're saying is that you load all entities and something (code, framework, whatever) calls the getChildren (assumption) for that entity; This will produce those queries.

Now, if this is always happening, then first of all, there's no point in having a lazy collection, set them as EAGER. - EDIT: as said in the comments, EAGER is rarely the solution, in this case in particular it definitely does not seem like it, the join is though :)

Either way, for your case that won't help, what you want is to load all data at once I assume, for that, when you do the query you have to make the join explicit, example with JPQL:

SELECT p FROM Parent p LEFT JOIN FETCH p.children

Upvotes: 2

Related Questions