JPA supports eager and lazy fetch of child entities. If you’re not careful with the lazy fetch strategy it can result in excessive queries as it needs to execute a query for the parent entity and then an additional one for each child. This is the so-called n+1 problem. You’ll often want to use eager fetching so that you can pull the parent and all children with a single query.
If you use HQL/JPQL, the JPA Criteria API or queries derived from Spring Data Repository method names, JPA will convert your SQL query result set to entity objects. That’s what an Object Relational Mapping (ORM) system is for. However if you use JPA native queries (SQL), you’ll need to map the results yourself.
In this post, I’ll look at how to run eager fetches for JPQL and native queries and how to manage the results.
Define the object mapping
In this example, I have a simple data set of movies and stars with a @ManyToMany relationship. A Movie may have many Stars and a Star may feature in many Movies.
@Entity
public class Movie {
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
private Long id;
private String title;
@ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE})
@JoinTable(name = "movie_star", joinColumns = @JoinColumn(name = "movie_id"), inverseJoinColumns = @JoinColumn(name = "star_id"))
private Set<Star> stars = new HashSet<>();
}
@Entity
public class Star {
@Id
@GeneratedValue(strategy= GenerationType.AUTO)
private Long id;
private String firstName;
private String lastName;
@ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.PERSIST, CascadeType.MERGE})
private Set<Movie> movies = new HashSet<>();
}
The FetchType is EAGER in both directions.
FetchType.EAGER queries
I can query Movies by Spring Data Repository query methods, JPQL and native queries in the usual way:
public interface MovieRepository extends CrudRepository<Movie, Long> {
List<Movie> findByTitle(String title);
@Query("SELECT m FROM Movie m JOIN m.stars s WHERE m.title = :title")
List<Movie> findByTitleEagerFetchStars(String title);
@Query(value = """
SELECT m.*
FROM Movie m
LEFT JOIN movie_star ms ON m.id = ms.movie_id
LEFT JOIN Star s ON ms.star_id = s.id
WHERE m.title = :title
""", nativeQuery = true)
List<Movie> findByTitleNative(String title);
}
In all cases, the child entities (Stars) are loaded eagerly and correctly mapped to the Movie and Star entities.
@Test
void testFindByTitle() {
List<Movie> results = repository.findByTitle("Ghostbusters");
entityManager.clear(); // Clear EM to prevent lazy loading. We want to prove that stars are lazy loaded
assertThat(results, hasSize(1));
Movie result = results.get(0);
assertThat(result.getTitle(), equalTo("Ghostbusters"));
assertThat(result.getStars(), containsInAnyOrder(
hasProperty("lastName", equalTo("Aykroyd")),
hasProperty("lastName", equalTo("Hudson")),
hasProperty("lastName", equalTo("Murray")),
hasProperty("lastName", equalTo("Ramis"))
));
// Movie and Star entities were loaded with a single query
assertEquals(1, statistics.getQueryExecutionCount());
}
FetchType.LAZY queries
Now what happens if I set FetchType.LAZY but still want to load child entities eagerly? It’s not as daft an idea as it might appear. I often define entities with FetchType.LAZY strategy but then load child entities eagerly by crafting queries with eager fetching. My reason is that if you define FetchType.EAGER on a child entity, it will always be loaded eagerly. If I define FetchType.LAZY it will be lazy loaded by default but I can override in my query. So if I have a relationship that I sometimes want loaded eagerly and sometimes lazy, I have to use FetchType.LAZY.
Spring Data query methods
First, this technique does not work with queries derived from Spring Data Repository method names. Spring Data’s query methods will build your query based on method names of your Repository subinterface and will make sensible guesses about the behaviours you want. We’ve declared FetchType.LAZY on the entity so that’s what you’ll get.
Spring Data allows you to override its behaviours by implementing your Repository subinterface or by using the @Query annotation.
JPQL queries
You can annotate your query method with @Query and define your query using JPQL. If you want to explicitly fetch child entities, use the FETCH keyword:
@Query("SELECT m FROM Movie m JOIN FETCH m.stars s WHERE m.title = :title")
List<Movie> findByTitleEagerFetchStars(String title);
This tells JPA to JOIN Movie to Stars and to eagerly FETCH the Star entities.
JPA native queries
Native SQL queries are usually best avoided in JPA but are sometimes necessary. I recently wanted to query using Oracle’s REGEXP_LIKE condition. This is not standard SQL and does not have an equivalent in JPQL. So I was forced to use a JPA native query.
@Query(value = """
SELECT m.*
FROM Movie m
LEFT JOIN movie_star ms ON m.id = ms.movie_id
LEFT JOIN Star s ON ms.star_id = s.id
WHERE m.title = :title
""", nativeQuery = true)
List<Movie> findByTitleNative(String title);
If you’ve defined the relationship with FetchType.EAGER, this works surprisingly well. If you’ve defined FetchType.LAZY, you’ll need to do some extra work to help JPA map your query results to Movie and Star entities.
@SqlResultSetMapping
The @SqlResultSetMapping JPA annotation can map a result set to entities or POJOs. Mapping to entities with @EntityResult can result in awkward syntax as the repository method return type has to return a List of entity tuples, like this:
List<Object[]> results = repository.findByTitleNamedNativeQueryWithResultSetMapping("Ghostbusters");
You’ll have to cast the Objects to the appropriate entity classes. Baeldung has an example of this – it’s just horrible to work with.
I prefer to map the results to POJOs using @ConstructorResult and define a POJO that takes the entire result.
@NamedNativeQuery(name = "Movie.findByTitleNamedNativeQueryWithResultSetMapping", query = """
SELECT m.id m_id, m.title m_title, s.id s_id, s.first_name s_firstName, s.last_name s_lastName
FROM Movie m
LEFT JOIN movie_star ms ON m.id = ms.movie_id
LEFT JOIN Star s ON ms.star_id = s.id
WHERE m.title = :title
""", resultSetMapping = "movieStarResult", resultClass = MovieStarResult.class)
@SqlResultSetMapping(name="movieStarResult", classes = @ConstructorResult(
targetClass = MovieStarResult.class,
columns = {
@ColumnResult(name="m_id"),
@ColumnResult(name="m_title"),
@ColumnResult(name="s_id"),
@ColumnResult(name="s_firstName"),
@ColumnResult(name="s_lastName")
}
))
In my example the MovieStarResult class handles all Movie and Star entity fields. My repository method can then return a List of MovieStarResult objects:
@Query(nativeQuery = true)
List<MovieStarResult> findByTitleNamedNativeQueryWithResultSetMapping(String title);
You’ll still need to do some processing on your results to build properly formed entity objects. In particular you’ll need to manage the entity relationships yourself. This is fairly easy to do with the MovieStarResult POJO:
public static Collection<Movie> getMovies(List<MovieStarResult> results) {
Map<Long, Movie> movies = new HashMap<>();
Map<Long, Star> stars = new HashMap<>();
for (MovieStarResult result : results) {
Movie movie = movies.computeIfAbsent(result.movieId, id -> result.getMovie());
Star star = stars.computeIfAbsent(result.starId, id -> result.getStar());
movie.addStars(star);
star.addMovie(movie);
}
return movies.values();
}
private Movie getMovie() {
return new Movie(movieId, movieTitle);
}
private Star getStar() {
return new Star(starId, starFirstName, starLastName);
}
Example code
There’s a lot going on here and you need to get the entities, repositories, queries and mappings defined correctly against each other. Take a look at the example in my GitHub. The main version shows how to set up mappings with FetchType.LAZY . An alternative FetchType.EAGER version shows the differences when you define the relationship with EAGER fetch.
Be First to Comment