Skip to content
Advertisement

Return custom object from Spring Data with Native Query

My question is based on another post. How can I achieve the same with a native query? Native queries do not allow JPQL thus do not allow new instances either.

My POJO.

class Coordinates {

    private final BigDecimal latitude
    private final BigDecimal longitude

    ...
}

My database table contains coordinates for cities perimeter, so there are three columns: city_name, latitude, longitude. Each city contains lots (really, LOTS) of perimeter coordinates that will be used to build a shadow area in Google Maps.

I intend to build a simple native query on that table that should return a list of coordinates.

Advertisement

Answer

Found the answer on another post. Basically I used SqlResultSetMapping along with ConstructorResult (no other way worked out) with a special attention to a comment on the accepted answer of the mentioned post: you need to add the @NamedNativeQuery annotation to the entity of the used interface AND prepend the entity’s name with a . otherwise it won’t work.

Example:

@Entity
@Table(name = "grupo_setorial")
@SqlResultSetMapping(
        name = "mapeamentoDeQuadrantes",
        classes = {
                @ConstructorResult(
                        targetClass = Coordenada.class,
                        columns = {
                                @ColumnResult(name = "latitude"),
                                @ColumnResult(name = "longitude")
                        }
                )
        }
)
@NamedNativeQuery(
        name = "GrupoCensitario.obterPerimetroDosSetores",
        query = "SELECT latitude as latitude, longitude as longitude FROM coordenadas where id_setor IN (:setores)",
        resultSetMapping = "mapeamentoDeQuadrantes"
)
public class GrupoCensitario {
9 People found this is helpful
Advertisement