spring-data-jpa icon indicating copy to clipboard operation
spring-data-jpa copied to clipboard

Error binding partial countQuery parameters on @Query using nativeQuery and pagination

Open bodiam opened this issue 3 years ago • 0 comments

I have a native query with 3 parameters:

    @Query(nativeQuery = true)
    fun findNearbyDiveSpotsBySealifeContaining(@Param("latitude") lat: Double,
                                               @Param("longitude") lon: Double,
                                               @Param("objectId") objectId: String,
                                               pageable: Pageable): Page<SpotList>

In my orm.xml, I've created 2 queries for this. The data retrieval query works fine, but the count query doesn't:

    <named-native-query name="DiveSpot.findNearbyDiveSpotsBySealifeContaining.count" result-set-mapping="cntColumnResult">
        <query>
            SELECT count(*) as cnt
            FROM dive_spot u
                     JOIN dive_spot_sealife dss on u.id = dss.dive_spot_id
                     JOIN sealife s on dss.sealife_id = s.id
            WHERE s.object_id = :objectId
            -- I think this is a bug in spring-data-jpa
--             or s.latitude = :latitude   
--             or s.longitude = :longitude
       </query>
    </named-native-query>

I need the objectId parameter, but the lat/lng parameters I only need for the data query, so I don't use them here. But when I don't use them, I'm getting an exception:

Caused by: java.lang.IllegalArgumentException: Could not locate named parameter [latitude], expecting one of [objectId]
	at org.hibernate.query.internal.ParameterMetadataImpl.getNamedParameterDescriptor(ParameterMetadataImpl.java:229)
	at org.hibernate.query.internal.ParameterMetadataImpl.getQueryParameter(ParameterMetadataImpl.java:198)
	at org.hibernate.query.internal.QueryParameterBindingsImpl.getBinding(QueryParameterBindingsImpl.java:189)
	at org.hibernate.query.internal.AbstractProducedQuery.setParameter(AbstractProducedQuery.java:501)
	at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:650)
	at org.hibernate.query.internal.NativeQueryImpl.setParameter(NativeQueryImpl.java:66)
	at org.springframework.data.jpa.repository.query.QueryParameterSetter$BindableQuery.setParameter(QueryParameterSetter.java:326)
	at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.lambda$setParameter$4(QueryParameterSetter.java:117)
	at org.springframework.data.jpa.repository.query.QueryParameterSetter$ErrorHandling$1.execute(QueryParameterSetter.java:140)
	at org.springframework.data.jpa.repository.query.QueryParameterSetter$NamedOrIndexedQueryParameterSetter.setParameter(QueryParameterSetter.java:117)
	at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:82)
	at org.springframework.data.jpa.repository.query.ParameterBinder.bind(ParameterBinder.java:74)

As a workaround, I'm just doing something silly with the parameters, but could this be a bug?

bodiam avatar Jul 05 '22 12:07 bodiam