Mixing
Custom JPA repository query with spring boot
I am trying to execute a custom query through jpa repository interface like this:
@Query(
value = "SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1",
nativeQuery = true)
public List<String> getNamesFromView( String viewName);
I want to pass the name of the table to fetch from, dynamically by the user.
I am getting runtime exception
nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
If I hardcode the table name, this is working fine
sql hibernate spring-boot spring-data-jpa
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
asked Nov 20 '18 at 17:55
OdinOdin
404718
add a comment |
I am trying to execute a custom query through jpa repository interface like this:
@Query(
value = "SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1",
nativeQuery = true)
public List<String> getNamesFromView( String viewName);
I want to pass the name of the table to fetch from, dynamically by the user.
I am getting runtime exception
nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
If I hardcode the table name, this is working fine
sql hibernate spring-boot spring-data-jpa
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
asked Nov 20 '18 at 17:55
OdinOdin
404718
add a comment |
I am trying to execute a custom query through jpa repository interface like this:
@Query(
value = "SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1",
nativeQuery = true)
public List<String> getNamesFromView( String viewName);
I want to pass the name of the table to fetch from, dynamically by the user.
I am getting runtime exception
nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
If I hardcode the table name, this is working fine
sql hibernate spring-boot spring-data-jpa
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
asked Nov 20 '18 at 17:55
OdinOdin
404718
I am trying to execute a custom query through jpa repository interface like this:
@Query(
value = "SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1",
nativeQuery = true)
public List<String> getNamesFromView( String viewName);
I want to pass the name of the table to fetch from, dynamically by the user.
I am getting runtime exception
nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
If I hardcode the table name, this is working fine
sql hibernate spring-boot spring-data-jpa
sql hibernate spring-boot spring-data-jpa
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
asked Nov 20 '18 at 17:55
OdinOdin
404718
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
asked Nov 20 '18 at 17:55
OdinOdin
404718
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
edited Nov 21 '18 at 7:31
Billy Frost
1,79098
1,79098
asked Nov 20 '18 at 17:55
OdinOdin
404718
asked Nov 20 '18 at 17:55
OdinOdin
404718
asked Nov 20 '18 at 17:55
OdinOdin
404718
404718
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Table names can not be parametarized, that's the reason why your getting error.
The bind variables exist primarily for performance reasons, parametarized query will be compiled only once by DB and for later subsequent executions same compiled version is used.
The value for the placeholder would be a string.
So SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1 with xyz as the table name would actually translates to
SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM 'xyz', which is invalid SQL
As you mentioned you have entityManager's instance, you can execute the query like below:
entityManager.query(String.format("SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM %s", viewName));
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
1
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
|
show 1 more 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%2f53398812%2fcustom-jpa-repository-query-with-spring-boot%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Table names can not be parametarized, that's the reason why your getting error.
The bind variables exist primarily for performance reasons, parametarized query will be compiled only once by DB and for later subsequent executions same compiled version is used.
The value for the placeholder would be a string.
So SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1 with xyz as the table name would actually translates to
SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM 'xyz', which is invalid SQL
As you mentioned you have entityManager's instance, you can execute the query like below:
entityManager.query(String.format("SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM %s", viewName));
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
1
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
|
show 1 more comment
Table names can not be parametarized, that's the reason why your getting error.
The bind variables exist primarily for performance reasons, parametarized query will be compiled only once by DB and for later subsequent executions same compiled version is used.
The value for the placeholder would be a string.
So SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1 with xyz as the table name would actually translates to
SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM 'xyz', which is invalid SQL
As you mentioned you have entityManager's instance, you can execute the query like below:
entityManager.query(String.format("SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM %s", viewName));
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
1
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
|
show 1 more comment
Table names can not be parametarized, that's the reason why your getting error.
The bind variables exist primarily for performance reasons, parametarized query will be compiled only once by DB and for later subsequent executions same compiled version is used.
The value for the placeholder would be a string.
So SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1 with xyz as the table name would actually translates to
SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM 'xyz', which is invalid SQL
As you mentioned you have entityManager's instance, you can execute the query like below:
entityManager.query(String.format("SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM %s", viewName));
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
Table names can not be parametarized, that's the reason why your getting error.
The bind variables exist primarily for performance reasons, parametarized query will be compiled only once by DB and for later subsequent executions same compiled version is used.
The value for the placeholder would be a string.
So SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM ?1 with xyz as the table name would actually translates to
SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM 'xyz', which is invalid SQL
As you mentioned you have entityManager's instance, you can execute the query like below:
entityManager.query(String.format("SELECT (TRIM(TYPE_NAME) ||'.'|| TRIM(NAME)) AS NAMES FROM %s", viewName));
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
edited Nov 20 '18 at 18:12
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
answered Nov 20 '18 at 18:08
eatSleepCodeeatSleepCode
2,26632463
2,26632463
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
1
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
|
show 1 more comment
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
1
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
Is there any possible alternative to execute this custom query using spring boot with jpa ?I have Entity manager and transaction factory at my disposal
– Odin
Nov 20 '18 at 18:10
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
check my updated answer. @Odin
– eatSleepCode
Nov 20 '18 at 18:15
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
should I build a new dao class for adding this lines? Which class do I need to extend?
– Odin
Nov 20 '18 at 18:18
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
@Odin no need to extend any class.. You can write it as any other class.
– eatSleepCode
Nov 20 '18 at 18:20
1
1
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
you might need to create correct entityManager instance using emf for the db that you want. Create EMF using PersistenceUnit of the db on which you need to run the query. @Odin
– eatSleepCode
Nov 20 '18 at 18:42
|
show 1 more 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%2f53398812%2fcustom-jpa-repository-query-with-spring-boot%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
