2

I am using Hibernate and Spring data JPA to build a web project.

In Eclipse-Link we can use native query like 

String tableName = "sometablename";
String query = "SELECT * FROM " +tableName +"WHERE id > 10";

In Hibernate I am using @Query annotation

    @Query(value = "SELECT COUNT(r.id) as resultsCount FROM #{#resultTable} r WHERE r.customerId= :customerId AND r.is_deleted=0 AND r.is_ignored=0 ", nativeQuery = true)
Integer getResultsCount(@Param("customerId") int customerId,
            @Param("resultTable") String resultTable);

I tried #{#resultTable} but this is getting replaced as a string with quote and i am getting an exception that 

You have a error in your SQL Syntax

I want to use table name dynamically from params. Is it possible? And if yes, Please tell me how?

Andronicus
  • 25,419
  • 17
  • 47
  • 88
Rakesh Kumar
  • 61
  • 1
  • 9

3 Answers3

4

It's not possible, @org.springframework.data.jpa.repository.Query takes only jpql, you cannot pass the name of the table since it's not recognized as any entity.

It states the javadoc of Query:

/**
 * Defines the JPA query to be executed when the annotated method is called.
 */
String value() default "";

The best solution would be not to pass tablename as string, but resolve it using for example inheritance (link) or rebuild your datamodel somehow. As a quick and dirty solution I would suggest creating a custom repository and using EntityManager.createNativeQuery and pass the sql there. But remember to validate the query you're composing (validate user input, use enums for table names), because it can lead to sql injection.

Andronicus
  • 25,419
  • 17
  • 47
  • 88
  • Thank you @Andronicus, Can you suggest a better way(or a way) to handle this problem? – Rakesh Kumar Jul 12 '19 at 05:52
  • @RakeshKumar no problem. Check out my edit. – Andronicus Jul 12 '19 at 06:00
  • 1
    While the actual answer ("It's not possible") is correct the statement that no preprocessing is done is wrong. You can actually use SpEL expressions in places of parameter with Spring Data JPA.https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query.spel-expressions – Jens Schauder Jul 12 '19 at 06:00
  • @JensSchauder thank you very much, thats interesting. Rakesh Kumar seems, that entity names can be used instead of table names as in spel. – Andronicus Jul 12 '19 at 06:03
  • It's possible with [native queries](https://www.baeldung.com/spring-data-jpa-query#2-native-1) which take underlying sql instead of jpql (takes the name of the underlying table instead of the entity). Just have to specify`nativeQuery = true` in the [Query](https://docs.spring.io/spring-data/jpa/docs/current/api/org/springframework/data/jpa/repository/Query.html) annotation above the repository method. – Antoine Dahan Jul 13 '23 at 16:42
1

Your own observations actually answer your question:

I tried #{#resultTable} but this is getting replaced as a string with quote and i am getting an exception

The placeholders which are used inside the @Query query string are intended to filled with literal values. Hence, the table name ended up appearing as literal string, inside single quotes. This means that behind the scenes @Query and Spring are probably using a JDBC prepared statement. It is not possible to bind the name of the table. Allowing this would be a major security hole.

The only possible workaround here would be to concatenate your query string together, and then trying to use that string with @Query. But note that this would not be a safe option.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

What you are doing is wrong, you are mixing business logic into DAO layer, i suggest you create two DAO's, each one with its own table and query, then into the business/service layer call the desired one.

sock_osg
  • 49
  • 4