CODEWITHSUNDEEP
sqlhibernatespring-bootspring-data-jpa
Odin
Odin

Reputation: 580

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

Upvotes: 1

Views: 225

Answers (1)

eatSleepCode
eatSleepCode

Reputation: 4637

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));

Upvotes: 1

Related Questions