I have two entities defined: School
and District
. A district can have many schools and a school can belong to one district.
When executing a GET
request against this endpoint http://localhost:8080/districts
I would like to get a list of all the districts WITHOUT fetching each district's set of associated schools. But it seems like no matter what I do, hibernate is making DB calls to fetch data for each school individually.
@Getter
@Setter
@NoArgsConstructor
@Entity
public class School {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@NotNull
@Column(unique=true)
private Long number;
@NotNull
@Column
private String name;
@NotNull
private boolean closed;
@Embedded
private ContactInfo contactInfo;
private String gradeLow;
private String gradeHigh;
private int enrollment;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "district_id")
private District district;
}
@Getter
@Setter
@NoArgsConstructor
@Entity
public class District {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(unique = true)
private Integer number;
private String name;
private String type;
private int enrollment;
private Date updated;
@Embedded
private ContactInfo contactInfo;
@Getter(AccessLevel.NONE)
@JsonIgnore
@OneToMany(fetch = FetchType.LAZY, mappedBy = "district")
private Set<School> schoolList;
}
SELECT district0_.id AS id1_5_,
district0_.city AS city2_5_,
district0_.email AS email3_5_,
district0_.fax AS fax4_5_,
district0_.first_name AS first_na5_5_,
district0_.last_name AS last_nam6_5_,
district0_.name_prefix AS name_pre7_5_,
district0_.phone AS phone8_5_,
district0_.state AS state9_5_,
district0_.street AS street10_5_,
district0_.title AS title11_5_,
district0_.website AS website12_5_,
district0_.zip AS zip13_5_,
district0_.enrollment AS enrollm14_5_,
district0_.NAME AS name15_5_,
district0_.number AS number16_5_,
district0_.type AS type17_5_,
district0_.updated AS updated18_5_
FROM district district0_
SELECT schoollist0_.district_id AS distric20_7_0_,
schoollist0_.id AS id1_7_0_,
schoollist0_.id AS id1_7_1_,
schoollist0_.closed AS closed2_7_1_,
schoollist0_.city AS city3_7_1_,
schoollist0_.email AS email4_7_1_,
schoollist0_.fax AS fax5_7_1_,
schoollist0_.first_name AS first_na6_7_1_,
schoollist0_.last_name AS last_nam7_7_1_,
schoollist0_.name_prefix AS name_pre8_7_1_,
schoollist0_.phone AS phone9_7_1_,
schoollist0_.state AS state10_7_1_,
schoollist0_.street AS street11_7_1_,
schoollist0_.title AS title12_7_1_,
schoollist0_.website AS website13_7_1_,
schoollist0_.zip AS zip14_7_1_,
schoollist0_.district_id AS distric20_7_1_,
schoollist0_.enrollment AS enrollm15_7_1_,
schoollist0_.grade_high AS grade_h16_7_1_,
schoollist0_.grade_low AS grade_l17_7_1_,
schoollist0_.NAME AS name18_7_1_,
schoollist0_.number AS number19_7_1_
FROM school schoollist0_
WHERE schoollist0_.district_id = ?
SELECT schoollist0_.district_id AS distric20_7_0_,
schoollist0_.id AS id1_7_0_,
schoollist0_.id AS id1_7_1_,
schoollist0_.closed AS closed2_7_1_,
schoollist0_.city AS city3_7_1_,
schoollist0_.email AS email4_7_1_,
schoollist0_.fax AS fax5_7_1_,
schoollist0_.first_name AS first_na6_7_1_,
schoollist0_.last_name AS last_nam7_7_1_,
schoollist0_.name_prefix AS name_pre8_7_1_,
schoollist0_.phone AS phone9_7_1_,
schoollist0_.state AS state10_7_1_,
schoollist0_.street AS street11_7_1_,
schoollist0_.title AS title12_7_1_,
schoollist0_.website AS website13_7_1_,
schoollist0_.zip AS zip14_7_1_,
schoollist0_.district_id AS distric20_7_1_,
schoollist0_.enrollment AS enrollm15_7_1_,
schoollist0_.grade_high AS grade_h16_7_1_,
schoollist0_.grade_low AS grade_l17_7_1_,
schoollist0_.NAME AS name18_7_1_,
schoollist0_.number AS number19_7_1_
FROM school schoollist0_
WHERE schoollist0_.district_id = ?
SELECT schoollist0_.district_id AS distric20_7_0_,
schoollist0_.id AS id1_7_0_,
schoollist0_.id AS id1_7_1_,
schoollist0_.closed AS closed2_7_1_,
schoollist0_.city AS city3_7_1_,
schoollist0_.email AS email4_7_1_,
schoollist0_.fax AS fax5_7_1_,
schoollist0_.first_name AS first_na6_7_1_,
schoollist0_.last_name AS last_nam7_7_1_,
schoollist0_.name_prefix AS name_pre8_7_1_,
schoollist0_.phone AS phone9_7_1_,
schoollist0_.state AS state10_7_1_,
schoollist0_.street AS street11_7_1_,
schoollist0_.title AS title12_7_1_,
schoollist0_.website AS website13_7_1_,
schoollist0_.zip AS zip14_7_1_,
schoollist0_.district_id AS distric20_7_1_,
schoollist0_.enrollment AS enrollm15_7_1_,
schoollist0_.grade_high AS grade_h16_7_1_,
schoollist0_.grade_low AS grade_l17_7_1_,
schoollist0_.NAME AS name18_7_1_,
schoollist0_.number AS number19_7_1_
FROM school schoollist0_
WHERE schoollist0_.district_id = ?
SELECT schoollist0_.district_id AS distric20_7_0_,
schoollist0_.id AS id1_7_0_,
schoollist0_.id AS id1_7_1_,
schoollist0_.closed AS closed2_7_1_,
schoollist0_.city AS city3_7_1_,
schoollist0_.email AS email4_7_1_,
schoollist0_.fax AS fax5_7_1_,
schoollist0_.first_name AS first_na6_7_1_,
schoollist0_.last_name AS last_nam7_7_1_,
schoollist0_.name_prefix AS name_pre8_7_1_,
schoollist0_.phone AS phone9_7_1_,
schoollist0_.state AS state10_7_1_,
schoollist0_.street AS street11_7_1_,
schoollist0_.title AS title12_7_1_,
schoollist0_.website AS website13_7_1_,
schoollist0_.zip AS zip14_7_1_,
schoollist0_.district_id AS distric20_7_1_,
schoollist0_.enrollment AS enrollm15_7_1_,
schoollist0_.grade_high AS grade_h16_7_1_,
schoollist0_.grade_low AS grade_l17_7_1_,
schoollist0_.NAME AS name18_7_1_,
schoollist0_.number AS number19_7_1_
FROM school schoollist0_
WHERE schoollist0_.district_id = ?
....
The SELECT FROM school
as seen above, repeats hundreds of times despite having the schoolList configured as a lazy load.
Versions:
springBootVersion = '1.4.2.RELEASE'
hibernate-core:5.0.11
'org.springframework.boot:spring-boot-starter-data-jpa'
'org.springframework.boot:spring-boot-starter-data-rest'
'org.springframework.boot:spring-boot-starter-web'
'org.springframework.boot:spring-boot-starter-actuator'
'org.springframework.boot:spring-boot-starter-hateoas'
'org.springframework.boot:spring-boot-starter-security'
Finally figured this out ... I didn't include all of the code when I originally posted this question for simplicity sake. Unfortunately, the erroneous code was not something I originally posted.
I had created a Projection for my School
object and mapped the projection to the SchoolRepository
as shown in the below code. I thought the projection was only applied when explicitly specified in a REST request (ie : /schools?projection=schoolExcerpt
) But apparently, the projection was getting applied all the time. And for some reason, the District
object was applying the SchoolProjection
to each associated school -> resulting in SQL queries to fetch data form each school individually even with the @JsonIgore
annotation present.
By simply removing the projection I was able to retrieve a list of all Districts
without making thousands of calls to resolve each associated school object.
@RepositoryRestResource(excerptProjection = SchoolProjection.class) //removing this line solves my problems
public interface SchoolRepository extends CrudRepository<School, Long>{
}
I don't actually even need the @JsonIgnore
annotation... HATEOAS is smart enough to not include related objects - instead it includes links to related objects.
Looks like your code even not workable, without @JsonIgnore
for the field district
during attempt to get all available schools you should recieve something like that:
com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.javassist.JavassistLazyInitializer and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: java.util.ArrayList[0]->com.mberazouski.stackoverflow.springboothibernate.model.School["district"]->com.mberazouski.stackoverflow.springboothibernate.model.District_$$_jvstad5_0["handler"]) at com.fasterxml.jackson.databind.exc.InvalidDefinitionException.from(InvalidDefinitionException.java:77) ~[jackson-databind-2.9.6.jar:2.9.6]
But if you will add this annotation - everything should start working as expected. So the only change which should be added should be:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "district_id")
@JsonIgnore
private District district;
The result of trace will be:
2018-06-17 17:27:56.431 DEBUG 25024 --- [nio-8080-exec-1] org.hibernate.SQL : select school0_.id as id1_3_, school0_.closed as closed2_3_, school0_.district_id as district8_3_, school0_.enrollment as enrollme3_3_, school0_.grade_high as grade_hi4_3_, school0_.grade_low as grade_lo5_3_, school0_.name as name6_3_, school0_.number as number7_3_ from school school0_
In opposite to that if you will remove lazy:
@ManyToOne
@JoinColumn(name = "district_id")
private District district;
We will get full result:
Controller which was used for the test:
import com.mberazouski.stackoverflow.springboothibernate.model.School;
import com.mberazouski.stackoverflow.springboothibernate.repository.SchoolRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class SchoolController {
@Autowired
SchoolRepository schoolRepository;
@GetMapping("/schools")
public List<School> getAllSchools() {
return schoolRepository.findAll();
}
}
Hope that suggested change will solve your issue.
Good luck.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With