Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SpringDataJPA: custom data mapping with Native Query

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?0", nativeQuery = true)
  User findByEmailAddress(String emailAddress);
}

Let's say I have the code above where I select * from user. What should I do if I don't want this method to return User object. Is there a way I can manually map the data to a custom object MyUser? Can I do all this in the UserRepository interface?

Thanks!

like image 992
topcan5 Avatar asked Nov 13 '15 21:11

topcan5


People also ask

How execute native SQL query in Spring data JPA?

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm. xml file.

How do you return Dtos from native queries with Spring data JPA?

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.

How write native SQL query in JPA?

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.


2 Answers

What about interface based projection?

Basically you write interface with getters that correspond to SQL query parameters.

In this way you even don't need to force @Id parameter on projection:

@Entity
public class Book {
     @Id
     private Long id;
     private String title;
     private LocalDate published;
}

public interface BookReportItem {
     int getYear();
     int getMonth();
     long getCount();
}

public interface BookRepository extends Repository<Book, Long> {
     @Query(value = "select " +
                    "  year(b.published) as year," +
                    "  month(b.published) as month," +
                    "  count(b) as count," +
                    " from Book b" +
                    " group by year(b.published), month(b.published)")
     List<BookReportItem> getPerMonthReport();
}

It uses org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap underneath as proxy for interface in current Spring implementation.

It works for nativeQuery = true too.

like image 175
gavenkoa Avatar answered Oct 19 '22 12:10

gavenkoa


You can do something like this

@Query(value = "SELECT YOUR Column1, ColumnN FROM USERS WHERE EMAIL_ADDRESS = ?0", nativeQuery = true)
List<Object[]> findByEmailAddress(String emailAddress);

You have to do the mapping. Take a look at the Spring Data Repository as well. Source

like image 20
Rana_S Avatar answered Oct 19 '22 12:10

Rana_S