spring data jpa, native query not setting query parameter

Question

I have a Spring JPA repository, with a native query defined, the query appears to execute but doesn't produce any results.

Repository class

@Repository
public interface AddressBaseRepository extends JpaRepository<Address, String> {

    @Query(value = "select ADDRESSES.UPRN,ADDRESSES.FID,ADDRESSES.ORGANISATION,ADDRESSES.SUB_BUILDING,BUILDINGS.NAME as BUILDING,ADDRESSES.BUILDING_NUMBER,STREETS.NAME as STREET,TOWNS.NAME as TOWN,LOCALITY.NAME as LOCALITY,POSTCODES.POSTCODE,"
            + "EASTING,"
            + "NORTHING,"
            + "LATITUDE,"
            + "LONGITUDE"
            + " FROM ADDRESSES "
            + " INNER JOIN STREETS ON STREETS.ID = STREET"
            + " INNER JOIN TOWNS ON TOWNS.ID = TOWN"
            + " INNER JOIN POSTCODES ON POSTCODES.ID = POST_CODE"
            + " LEFT JOIN LOCALITY ON LOCALITY.ID = LOCALITY"
            + " LEFT JOIN BUILDINGS ON BUILDINGS.ID = BUILDING_NAME"
            + " LEFT JOIN ORGANISATIONS ON ORGANISATIONS.ID = ORGANISATION"
            + " WHERE ADDRESSES.UPRN = ?1", nativeQuery = true)
    List<Address> getAddressByUprn(String uprn);
}

On inspection of the logs it can be seen that the query parameter is not being set

Hibernate: select ADDRESSES.UPRN,ADDRESSES.FID,ADDRESSES.ORGANISATION,ADDRESSES.SUB_BUILDING,BUILDINGS.NAME as BUILDING,ADDRESSES.BUILDING_NUMBER,STREETS.NAME as STREET,TOWNS.NAME as TOWN,LOCALITY.NAME as LOCALITY,POSTCODES.POSTCODE,EASTING,NORTHING,LATITUDE,LONGITUDE FROM ADDRESSES  INNER JOIN STREETS ON STREETS.ID = STREET INNER JOIN TOWNS ON TOWNS.ID = TOWN INNER JOIN POSTCODES ON POSTCODES.ID = POST_CODE LEFT JOIN LOCALITY ON LOCALITY.ID = LOCALITY LEFT JOIN BUILDINGS ON BUILDINGS.ID = BUILDING_NAME LEFT JOIN ORGANISATIONS ON ORGANISATIONS.ID = ORGANISATION WHERE ADDRESSES.UPRN = ?

Could somebody point me in the right direction as to why the parameter is not being set, I have also tried named parameters and get the same result.

You don't print logs with proper configurations! Add your log configuration. — Eklavya, May 31 '21 at 17:50

score 1 · Accepted Answer · answered May 31 '21 at 21:09

The SQL gets send with a placeholder and the values for the bind parameters get send separately. This is not the reason why you don't see the expected results. Configure logging correctly and you'll be able to see the bound parameters.

See How to print a query string with parameter values when using Hibernate for how to do that for Hiberante.

spring data jpa, native query not setting query parameter

1 Answers1