Native Queries converted to N+1 queries in Hibernate 5.6.15

Hibernate ORM
1

Guys, when executing a repository with a native query

@Query(value = "select cc.id, cc.name, ccp.itu from common.countries cc 
LEFT JOIN common.countriesphone ccp on cc.id=ccp.id;"
            , nativeQuery = true)
    public List<Countries> findAllCountriesNativeQuery();

the native query is converted to more than 200 queries. One query per country.

As I have two classes Country (Parent Class), and Countryphone (Child Class) I have assured that @ManyToOne in child class is Lazy.

But anyway I am having 20 Seconds of time response when sending the native query, compared to 221 milliseconds when I send the JPQL query.

I really appreciate any suggestion on what I have wrong. Having queries which take 20 seconds when they suppose to take 200 milliseconds has become a major problem for us.

I have recorded the behavior here: https://vimeo.com/996850300/086679f9ba?share=copy

I have these two simple clases. Logs below.

Country

@Entity
@Table(name = "countries")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Countries {

//	private static final long serialVersionUID = -5594930455475309563L;
	
	@Id
    private Integer id;

    @Column(length = 1, name = "name")
    private String name;

    @OneToMany(mappedBy="countries", fetch = FetchType.EAGER)
    @JsonManagedReference
    private List<Countriesphone> countriesphone;
    
}

Countryphone

@Entity
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Table(name = "countriesphone")
@IdClass(CountriesphonePK.class)
public class Countriesphone {

//	private static final long serialVersionUID = -5594930455475309563L;
	@Id
    @JsonIgnore
    private Integer id;

    @Id
	private Integer itu;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "id", referencedColumnName = "id",insertable=false,updatable =false)
    @JsonBackReference
    private Countries countries;

}

and this repositories:

@Repository
public interface CountriesRepository extends JpaRepository<Countries, Long> {


    public Iterable<Countries> findAllByOrderByNameAsc();

    @Query("SELECT c FROM Countries c LEFT JOIN FETCH c.countriesphone")
    List<Countries> findAllCountriesJPQL();

    @Query(value = "select cc.id, cc.name, ccp.itu from common.countries cc LEFT JOIN common.countriesphone ccp on cc.id=ccp.id;"
            , nativeQuery = true)
    public List<Countries> findAllCountriesNativeQuery();

}

Log using Native query:

Hibernate:
    /* dynamic native SQL query */ select
        *
    from
        common.countries cc
    LEFT JOIN
        common.countriesphone ccp
            on cc.id=ccp.id;
Hibernate:
    select
        countriesp0_.id as id2_1_0_,
        countriesp0_.itu as itu1_1_0_,
        countriesp0_.itu as itu1_1_1_,
        countriesp0_.id as id2_1_1_
    from
        countriesphone countriesp0_
    where
        countriesp0_.id=?
Hibernate:
    select
        countriesp0_.id as id2_1_0_,
        countriesp0_.itu as itu1_1_0_,
        countriesp0_.itu as itu1_1_1_,
        countriesp0_.id as id2_1_1_
    from
        countriesphone countriesp0_
    where
        countriesp0_.id=?
Hibernate:
    select
        countriesp0_.id as id2_1_0_,
        countriesp0_.itu as itu1_1_0_,
        countriesp0_.itu as itu1_1_1_,
        countriesp0_.id as id2_1_1_
    from
        countriesphone countriesp0_
    where
        countriesp0_.id=?
200. more identical queries.

Log using JPQL query. (Working as expected). 221 milliseconds. We do not want to switch from sql to JPQL because we have more 500 sql queries tested and ready to use. So we need to use those.

/* SELECT
        c 
    FROM
        Countries c 
    LEFT JOIN
        FETCH c.countriesphone */ select
            countries0_.id as id1_0_0_,
            countriesp1_.itu as itu1_1_1_,
            countriesp1_.id as id2_1_1_,
            countries0_.name as name2_0_0_,
            countriesp1_.id as id2_1_0__,
            countriesp1_.itu as itu1_1_0__ 
        from
            countries countries0_ 
        left outer join
            countriesphone countriesp1_ 
                on countries0_.id=countriesp1_.id
2

Your Country#countriesphone is marked as eager, so Hibernate has to load that collection when loading a Country entity. If you want to implement join fetching like Hibernate ORM does in your HQL query, then you will have to use Hibernate ORM APIs.

3

Many thanks for answering. Where is tagged as EAGER?. I have checked and I see it is tagged as Lazy. Please see above.

4

I don’t know what to tell you. Check the code that you posted.

@OneToMany(mappedBy="countries", fetch = FetchType.EAGER)
@JsonManagedReference
private List<Countriesphone> countriesphone;
5

Many thanks. I had totally misunderstood this.

6

I had changed that one to:

@OneToMany(mappedBy=“countries”, fetch = FetchType.LAZY)
@JsonManagedReference
private List countriesphone;

It produces one query. But it generates an error.

Hibernate:
/* dynamic native SQL query */ select
cc.id,
cc.name,
ccp.itu
from
common.countries cc
LEFT JOIN
common.countriesphone ccp
on cc.id=ccp.id
where
cc.id is not null;
2024-08-13 10:05:38.196 WARN 91150 — [nio-9087-exec-4] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: failed to lazily initialize a collection of role: itk.dbcommon.countries.Countries.countriesphone, could not initialize proxy - no Session; nested exception is com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: itk.dbcommon.countries.Countries.countriesphone, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->itk.dbcommon.countries.Countries[“countriesphone”])]

Is there anyway that Hibernate could understand a repository with a native query like:


@Query(value = "select cc.id, cc.name, ccp.itu from common.countries cc LEFT JOIN common.countriesphone ccp on cc.id=ccp.id;"
            , nativeQuery = true)
    public List<Countries> findAllCountriesNativeQuery();

with out generating this error?

7

Fetching collections with native queries requires you to use Hibernate ORM APIs, like I wrote before.