Recently I've run into a problem with Hibernate running additional queries, even though I specify a query for it in the CrudRepository interface.
For this example I'm using Java 23, Spring Boot 3.4.4, Hibernate 6.6.11.Final and an H2 database.
I have these 2 entities:
@Entity
@Table(name = "box")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Box {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// fetch type is changed to EAGER because I run a simple integration test
@OneToMany(mappedBy = "box", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Item> items;
}
@Entity
@Table(name = "item")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@ManyToOne
@JoinColumn(name = "box_id")
private Box box;
// override toString() to avoid the toString methods from calling each other endlessly.
}
I also have this repository with a method using @NativeQuery annotation:
public interface BoxRepository extends CrudRepository<Box, Long> {
@NativeQuery("""
SELECT
b.id as box_id,
i.id as item_id,
i.name as item_name
FROM box b
JOIN item i ON i.box_id = b.id
WHERE i.name = :name
""")
List<Box> getBoxesContainingItemWithName(@Param("name") String name);
}
The goal is to fetch boxes that contain at least one item with a specific name. However, when I run this query, Hibernate fetches all items in a box that contains an item matching the WHERE clause (i.e., any box that has any item with the specified name will have all its items loaded, not just the matching ones).
Problem: Even though the SQL query filters for items based on their name, Hibernate still loads all items for each box in the result, which isn't efficient.
Question: How can I modify this behavior so that only the items that match the WHERE clause are fetched, without Hibernate performing additional queries to load all items for each box?
Recently I've run into a problem with Hibernate running additional queries, even though I specify a query for it in the CrudRepository interface.
For this example I'm using Java 23, Spring Boot 3.4.4, Hibernate 6.6.11.Final and an H2 database.
I have these 2 entities:
@Entity
@Table(name = "box")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Box {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
// fetch type is changed to EAGER because I run a simple integration test
@OneToMany(mappedBy = "box", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Item> items;
}
@Entity
@Table(name = "item")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Item {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
@ManyToOne
@JoinColumn(name = "box_id")
private Box box;
// override toString() to avoid the toString methods from calling each other endlessly.
}
I also have this repository with a method using @NativeQuery annotation:
public interface BoxRepository extends CrudRepository<Box, Long> {
@NativeQuery("""
SELECT
b.id as box_id,
i.id as item_id,
i.name as item_name
FROM box b
JOIN item i ON i.box_id = b.id
WHERE i.name = :name
""")
List<Box> getBoxesContainingItemWithName(@Param("name") String name);
}
The goal is to fetch boxes that contain at least one item with a specific name. However, when I run this query, Hibernate fetches all items in a box that contains an item matching the WHERE clause (i.e., any box that has any item with the specified name will have all its items loaded, not just the matching ones).
Problem: Even though the SQL query filters for items based on their name, Hibernate still loads all items for each box in the result, which isn't efficient.
Question: How can I modify this behavior so that only the items that match the WHERE clause are fetched, without Hibernate performing additional queries to load all items for each box?
Share Improve this question edited Mar 22 at 16:02 Reluctant asked Mar 22 at 14:25 ReluctantReluctant 11 bronze badge 7- 1 This is covered all over - You are running a native query to fetch Items and Boxes, but only retrieving partial data. JPA is required to give you a managed Box instance, and you have the box->Items relationship marked as eagerly fetched, so it has to go and fetch them. Make it lazy and your problem should likely go away. – Chris Commented Mar 22 at 15:25
- Thanks for your answer! I tried with the fetch value set to FetchType.LAZY, but it didn't change the behaviour – Reluctant Commented Mar 22 at 16:59
- Is there any reason you aren't using a JPQL query? Try that, and you'll see that your Native query is incomplete for what it needs to load a Box instance - i'm surprised you aren't getting errors now that I notice you are fetch 'box_id' which isn't mapped in the Box entity at all, as if you are building an Item not box instance. – Chris Commented Mar 23 at 14:52
- I'm not too fond of JPQL/HQL because it is mapped to SQL behind the scenes by Hibernate. I dislike losing control over what queries are being executed (especially in a more complex system, this is just a simple example). The information I fetch is all that's needed to create the entities (as shown by my code down below). I changed the query to use JPQL but the result is still the same (and in this case, I'm not even surprised that it pulls Items that are associated with the Box that matches the WHERE clause). – Reluctant Commented Mar 23 at 19:23
- You were not returning everything needed for an 'entity', and still unfortunately aren't - a ConstructorResult is not a managed entity, so doesn't have any change tracking, lazy loading etc. That could be good, or bad depending on your usecases, but JPQL/entities are another great tool when the situation calls for them, and are easier to maintain. If you have trouble with entities and native SQL, write JPQL and see what is generated, and then tweak that SQL to your purposes. – Chris Commented Mar 24 at 12:40
2 Answers
Reset to default 0I managed to come up with a workaround that resembles the way we approach the problem when using JDBC. Instead of returning List<Box>, I'm returning List<QueryResult>, I then map each row.
@Data
@SqlResultSetMapping(
name = "BoxItemDtoMapping",
classes = @ConstructorResult(
targetClass = QueryResult.class,
columns = {
@ColumnResult(name = "box_id", type = Long.class),
@ColumnResult(name = "item_id", type = Long.class),
@ColumnResult(name = "item_name", type = String.class)
}
)
)
@NoArgsConstructor
@AllArgsConstructor
public class QueryResult {
private Long boxId;
private Long itemId;
private String itemName;
}
@NativeQuery("""
SELECT
b.id as box_id,
i.id as item_id,
i.name as item_name
FROM box b
JOIN item i ON i.box_id = b.id
WHERE i.name = :name
""")
List<QueryResult> getBoxesContainingItemWithName(@Param("name") String name);
public List<Box> getBoxesContainingItemWithName(String name) {
List<QueryResult> queryResults = boxRepository.getBoxesContainingItemWithName(name);
Map<Long, Box> boxMap = new HashMap<>();
for (QueryResult queryResult: queryResults) {
Long boxId = queryResult.getBoxId();
Long itemId = queryResult.getItemId();
String itemName = queryResult.getItemName();
Box box = boxMapputeIfAbsent(boxId, id -> new Box(id, new ArrayList<>()));
box.getItems().add(new Item(itemId, itemName, box));
}
return new ArrayList<>(boxMap.values());
}
I wonder if there is a better solution, this feels a bit clunky.
You can write a JPQL like that
from Box b join fetch items where :name = some(select name from b.items)
The generated query is
select b1_0.id, i1_0.box_id,
i1_0.id, i1_0.name
from box b1_0
join item i1_0 b1_0.id=i1_0.box_id
where ? = any(select
i2_0.name from
item i2_0 where
b1_0.id=i2_0.box_id)
as you can see box and items are all fetches with a single query.
This HQL from Box b where :name = some(select name from b.items) generate
select b1_0.id
from box b1_0
where ? = any(select
i1_0.name from
item i1_0 where
b1_0.id=i1_0.box_id)
this query doesn't load all items, just the plain box