20

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.

Improve this question
4

6 Answers 6

Reset to default
20

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 {
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

@Manu it's been some time I did this so I honestly can't remember the "prepend the entity's name with a ." part, but I pasted above what is currently in production.
The Prepend entity name part clarified since it is important. If your nativequery name is obterPerimetroDosSetores and the entity name is GrupoCensitario, then you need to name it GrupoCensitario.obterPerimetroDosSetores in the @NamedNativeQuery for Spring to be able to figure out from the repo that the obterPerimetroDosSetores query is in the GrupoCensitario class. Hope that helps.
For the 1 come after me: You will face this issue soon (need to add @Query(nativeQuery=true) for the function in repo) and can you put this thing above some where else: No, complier don't like it, put in entity pls stackoverflow.com/questions/49056084/…
11

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.

Improve this answer

10 Comments

Interesting stuff. I'll give it a try before accepting as the answer. But I wonder: what if I didn't have an Entity? I wouldn't be able to use org.springframework.data.repository.*. In such case I would say johncena's answer is the one.
@SidneydeMoraes PersonSummary is not an entity
@SidneydeMoraes if you don't have entities at all, why use spring-data repositories anyway? This finder can only act as a complementary method to an already created Spring data repository. Otherwise I would go with plain jdbcTemplate+rowmapper solution .
I'd like to keep the convenience of using an interface repository with a single annotation such as @Query instead of having to deal with injection of Entity Managers and all that stuff.
The ticket you link to is fixed for versions 1.10.12 and 1.11.8 too, but still the given code doesn't work.. :-( According to the author you need Hibernate Hibernate 5.2.11 or newer too..
|
3

You will have to use sql result set mapping which is part of JPA.

Improve this answer

2 Comments

I found that SqlResultSetMapping natively works only with Entities and wouldn't work for non-entity POJO's as seen on this post. I'll give it a try with ConstructorResult.
So, ConstructorResult is the way to go. Please check the answer I added.
0

If you are using a recent version of spring-data and also making use of the Repositories, I personally think that the answer from Itsallas leads to the right solution.

I actually did't now about (Spring Data) Projections yet and needed a moment to understand what he was showing in his example.

Therefore I just want to add a link to the Spring Data JPA - Reference Documentation, have a look at the Projections chapter.

Spring Data query methods usually return one or multiple instances of the aggregate root managed by the repository. However, it might sometimes be desirable to create projections based on certain attributes of those types. Spring Data allows modeling dedicated return types, to more selectively retrieve partial views of the managed aggregates.

Improve this answer

Comments

0

The answer I found:

public interface UserEventRepository extends JpaRepository<UserEvent, Long> {

    List<UserEvent> findAllByUserId(Long userId);

    @Query(value = "SELECT user_id FROM user_event ue " +
                   "WHERE ue.user_id = :userId", nativeQuery = true)
    List<Long> findUserIdByEventId(@Param("userId") Long userId);
}

That way we return List of Long - list of ids. The key here is that we are setting the nativeQuery property to true. The value itself is the query we want to be executed.

I hope that helps. It seems a clear solution.

Improve this answer

3 Comments

I appreciate your contribution but this question already has an accepted answer. Moreover the question relates to returning custom value objects instead of entities, which is the regular usage of spring-data repositories.
@reneta is native queries fast?
@KumaresanPerumal - I am not sure, I do not think there is a speed difference - the native queries are usually used when you have a more complex query or unusual query to make.
0

The projections solutions was the best. (query by id just an example, you can just use extends CRUD Operations)

Just add the Interface as response to the query as

Example repository :

@Query(select * from tableA where tableA = :id)
DisplayLangLongI findTableAbyId(@Param(value = "id") Long id)

Example Interface (DisplayLangLongI.java)

public interface DisplayLangLongI () {
        BigDecimal getLatitude();
        BigDecimal getLongitude();
        ...... (you can add more)
    }

In interface, you can select which parameter/object just to display

Improve this answer

1 Comment

did not see it ;(

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.