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!
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.
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.
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.
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
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