JPA native queries: hack for column and table names as variables at runtime

Question

I understand it's not possible to dynamically modify table names and column names in a JPA query. They have to be hard-coded.

However, I have a schema that has lots of different tables all with the same structure for the first three columns. Rather than having to write a class or a method for querying each of these I wonder if there's a quick hack for modifying the table name and column names using variables? My existing query looks like:

public interface ExampleRepository extends JpaRepository<Example, Long>, JpaSpecificationExecutor<Example> { @Query(value = "SELECT name_ln FROM ?1 WHERE ?2 = ?3", nativeQuery = true) String getName(String tableLookUp, String idColumn, long namespaceRefId); }

This doesn't work, with MySQL producing the following error due to quotes around the table and column names:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table_name' WHERE 'column_id' = 211833' at line 1

You cannot use variables like that... he JDBC api doesn't allow it. — M. Deinum
– M. Deinum, Commented Aug 25, 2016 at 11:05
Yep, I know. But wondered if there might be a quick hack to avoid a more laboured approach given it's only the quotation marks around the table and column names causing the issue here. — D2J2
– D2J2, Commented Aug 25, 2016 at 14:16

Bilal Demir · Accepted Answer · 2019-11-26 08:51:35Z

You can try Param annotation for the pass value into the queries.

For example:

@Query(value = "SELECT name_ln FROM :tableName WHERE :columnName = :value", nativeQuery = true) String getName(@Param("tableName") String tableLookUp, @Param("columnName") String idColumn, @Param("value") long namespaceRefId);

Make sure the query result will be a single record. This may cause an error.

you can indeed use @Param("parameterName") to pass variables into queries. However, your example doesn't work. JPA won't be able to resolve :tableName and throws an exception at runtime

Collectives™ on Stack Overflow

JPA native queries: hack for column and table names as variables at runtime

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related