CODEWITHSUNDEEP
javamysqlspringhibernatejpa
cgval
cgval

Reputation: 1096

Spring JPA Join Efficiency - Create a query for each iteration

I have a simple 2 JPA entities which I have a Join Between them:

Primary entity Country:

public class Country implements Serializable {

  @Id
  @Column(name = "MCC")
  private String mcc;

  ......

  @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER)
  @JoinColumn(name = "mcc", referencedColumnName = "mcc")
  private List<CountryInfo> countryInfo;

Joint entity CountryInfo:

public class CountryInfo implements Serializable {

  @Id
  @Column(name = "id")
  private Long id;

  @Column(name = "mcc")
  private String mcc;

  @Column(name = "continent")
  private String continent;

When I've turned on my configuration to dump the queries being executed, I've noticed that for each Country found, another call is done on the CountryInfo entity with the mcc specified..

This is obviously slow since rather than creating 1 call with a JOIN, it is executing N + 1 queries (where N = count of Country).

I've already seen this tutorial https://zeroturnaround.com/rebellabs/how-to-use-jpa-correctly-to-avoid-complaints-of-a-slow-application/ and changed accordingly but it is still calling N + 1 queries..

Is there a way to overcome this?

EDIT

In order to get the data I have a Repository:

@RepositoryRestResource(exported = false)
public interface CountryRepository extends JpaRepository<E212MCC, Long>, 
        JpaSpecificationExecutor<E212MCC> {
}

And then call with some specifications:

List<E212MCC> countries = this.countryRepository.findAll(specifications);

Upvotes: 1

Views: 1098

Answers (1)

Maciej Marczuk
Maciej Marczuk

Reputation: 3733

Since you are using Specifications you could try with specification that performs fetch join operation (I am assuming that you are using JPA meta model):

private Specification<Country> joinContryInfo() {
    return (root, query, cb) -> {
        root.fetch(Country_.countryInfo);
        // here you can fetch more entities if you need...
        return null;
    };
}

And then, just add it to your specification object:

Specifications.where(joinCountryInfo())

If you are not using meta model then just replace Country_.countryInfo with "countryInfo" string.

If you are using CountryInfo fields for searching, you can omit joinContryInfo() specification and prepare join and search query in one specification:

private Specification<Country> continentEqual(String param) {
    return (root, query, cb) -> {
        Join<Country,CountryInfo> join = (Join) root.fetch(Country_.countryInfo);
        return cb.equal(join.get(CountryInfo_.continent), addWildCards(param));;
    };
}

Upvotes: 1

Related Questions