Menu
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.
767
Spring Data JPA doesn't provide an automatic mapping of class-based DTOs for native queries. The easiest way to use this projection is to define your query as a @NamedNativeQuery and assign an @SqlResultSetMapping that defines a constructor result mapping.
Interface-based DTO projections You first need to define an interface that defines a getter method for each attribute your projection shall contain. At runtime, Spring Data JPA then generates a class that implements that interface. you can then use that interface as the return type of a repository method.
We can use @Query annotation to specify a query within a repository. Following is an example. In this example, we are using native query, and set an attribute nativeQuery=true in Query annotation to mark the query as native. We've added custom methods in Repository in JPA Custom Query chapter.
The easiest way to map a query result to an entity is to provide the entity class as a parameter to the createNativeQuery(String sqlString, Class resultClass) method of the EntityManager and use the default mapping. The following snippet shows how this is done with a very simple query.
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 {
This is https://jira.spring.io/browse/DATAJPA-980 and Here is a project that demonstrates the issue.
@Query(value = "SELECT name AS name, age AS age FROM Person", nativeQuery = true)
List<PersonSummary> findAllProjectedNativeQuery();
It is fixed in the Spring Data JPA 2.0 GA (Kay) release which comes with Hibernate 5.2.11.
The issue is also fixed for Spring Data 1.10.12 (Ingalls) and 1.11.8 (Hopper) but will need to be run on Hibernate 5.2.11 to work.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
