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!
spring jpa spring-data-jpa
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
add a comment |
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!
spring jpa spring-data-jpa
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
You are configuring Spring Security right? you don't need to extend JPARepsoitory you can use its super type and just create a repository that uses raw JDBC or whatever. With JPARepository Spring knows how to set up the JPA persistence up. If you use raw JDBC you would need to configure your bean with the db connection. If you know JPA then sure you can configure JPA to map queries into value objects or map a custom entity class hierarchy to a set of user tables, Spring is all about configuring objects and using interfaces you just implement the interfaces and have Spring wire them up.
– simbo1905
Nov 13 '15 at 21:42
add a comment |
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!
spring jpa spring-data-jpa
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
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!
spring jpa spring-data-jpa
spring jpa spring-data-jpa
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
asked Nov 13 '15 at 21:16
topcan5topcan5
53961432
53961432
You are configuring Spring Security right? you don't need to extend JPARepsoitory you can use its super type and just create a repository that uses raw JDBC or whatever. With JPARepository Spring knows how to set up the JPA persistence up. If you use raw JDBC you would need to configure your bean with the db connection. If you know JPA then sure you can configure JPA to map queries into value objects or map a custom entity class hierarchy to a set of user tables, Spring is all about configuring objects and using interfaces you just implement the interfaces and have Spring wire them up.
– simbo1905
Nov 13 '15 at 21:42
add a comment |
You are configuring Spring Security right? you don't need to extend JPARepsoitory you can use its super type and just create a repository that uses raw JDBC or whatever. With JPARepository Spring knows how to set up the JPA persistence up. If you use raw JDBC you would need to configure your bean with the db connection. If you know JPA then sure you can configure JPA to map queries into value objects or map a custom entity class hierarchy to a set of user tables, Spring is all about configuring objects and using interfaces you just implement the interfaces and have Spring wire them up.
– simbo1905
Nov 13 '15 at 21:42
You are configuring Spring Security right? you don't need to extend JPARepsoitory you can use its super type and just create a repository that uses raw JDBC or whatever. With JPARepository Spring knows how to set up the JPA persistence up. If you use raw JDBC you would need to configure your bean with the db connection. If you know JPA then sure you can configure JPA to map queries into value objects or map a custom entity class hierarchy to a set of user tables, Spring is all about configuring objects and using interfaces you just implement the interfaces and have Spring wire them up.
– simbo1905
Nov 13 '15 at 21:42
You are configuring Spring Security right? you don't need to extend JPARepsoitory you can use its super type and just create a repository that uses raw JDBC or whatever. With JPARepository Spring knows how to set up the JPA persistence up. If you use raw JDBC you would need to configure your bean with the db connection. If you know JPA then sure you can configure JPA to map queries into value objects or map a custom entity class hierarchy to a set of user tables, Spring is all about configuring objects and using interfaces you just implement the interfaces and have Spring wire them up.
– simbo1905
Nov 13 '15 at 21:42
add a comment |
2 Answers
2
active
oldest
votes
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
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
add a comment |
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:
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.
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f33701905%2fspringdatajpa-custom-data-mapping-with-native-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
add a comment |
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
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
add a comment |
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
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
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
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
answered Nov 13 '15 at 21:37
Rossi RobinsionRossi Robinsion
97221329
97221329
add a comment |
add a comment |
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:
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.
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
add a comment |
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:
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.
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
add a comment |
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:
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.
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
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:
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.
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
answered Nov 14 '18 at 21:36
gavenkoagavenkoa
23.1k11143186
23.1k11143186
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f33701905%2fspringdatajpa-custom-data-mapping-with-native-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You are configuring Spring Security right? you don't need to extend JPARepsoitory you can use its super type and just create a repository that uses raw JDBC or whatever. With JPARepository Spring knows how to set up the JPA persistence up. If you use raw JDBC you would need to configure your bean with the db connection. If you know JPA then sure you can configure JPA to map queries into value objects or map a custom entity class hierarchy to a set of user tables, Spring is all about configuring objects and using interfaces you just implement the interfaces and have Spring wire them up.
– simbo1905
Nov 13 '15 at 21:42