Load all lazy collection at once with hibernate/spring

Question

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()));
}

If you only need parents with all children IDs for each parent, why not just use JDBC-based DAO (using JdbcTemplate, for example) for this method and write a simple sql like select parent_id, child_id from parents, childs join ...? — Roman Puchkovskiy
– Roman Puchkovskiy, Commented Aug 8, 2017 at 19:17
Show your code pls.. entities, repos, usage example with the bad result... — Cepr0
– Cepr0, Commented Aug 8, 2017 at 19:18
When we talk about Parent-Child, it is supposed to be One-To-Many.. — Adrian Shum
– Adrian Shum, Commented Aug 9, 2017 at 6:42
Actually i want to use the convenient method of spring-data/jpa/hibernate to handle all the SQL-stuff. — user3603632
– user3603632, Commented Aug 10, 2017 at 19:39

Adrian Shum · Accepted Answer · 2017-08-09 07:24:25Z

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.

Mário Fernandes · Accepted Answer · 2017-08-09 06:45:41Z

2

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

edited Aug 9, 2017 at 6:45

answered Aug 9, 2017 at 6:35

Mário Fernandes

1,8621 gold badge23 silver badges22 bronze badges

3 Comments

Adrian Shum Over a year ago

Using EAGER is rarely a correct solution. There are too many situation that it will still generate n+1 queries. I would rather suggest doing join fetch etc

Mário Fernandes Over a year ago

NEVER is a strong word :) But yes, in this case it most likely is not. Will edit the answer

Mário Fernandes Over a year ago

eheheheh :) fair enough :)

Collectives™ on Stack Overflow

Load all lazy collection at once with hibernate/spring

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related