1

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

Odin
  • 580
  • 11
  • 24

1 Answers1

1

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));
eatSleepCode
  • 4,427
  • 7
  • 44
  • 93
  • 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
  • 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
  • Actually @eatSleepCode I am having multiple entitymanagerfactory and transactionmanager in my application as I am talking to different db s.I need to run this only on a specific DB so how do I specify the particular EM in this case? – Odin Nov 20 '18 at 18:31
  • 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