Use application properties file to pick the table name in a native query?

Question

I am using a native query in my Spring Boot application, and I want to be able to pick the table name from my properties file. I attempted using spel expressions and it does use the table name specified in the @Entity annotation...however I want to use something like @Value(${table.name} to inject the string "bookshelf" into the Entity annotation if that makes sense.

I have also attempted a different approach using a physical naming strategy from hibernate however it does not seem to replace the table name.

entity class:

   @Entity(name = "bookshelf")
   public class Object{
   
   private String color;
   private String shape;
}

Repository:

@Repository
public interface ObjectRepository extends CrudRepository<Object,Long>{

@Query(nativeQuery = true, value = "select color from #{#entityName} where shape =: shape)
public List<Object> findObjectsByShape(@Param("shape") String shape);

application.yml

table:
  name: bookshelf

How can I achieve this?

score 0 · Answer 1 · answered Dec 07 '20 at 16:08

AFAIK you can't do this with annotations.

What you can do:

Inject an EntityManager with for example:

@PersistenceContext
EntityManager em;

on the em you can call

em.createNativeQuery("your native query")

where can use any Value which you can access in your class.

BUT: please be aware this somehow already could be first step to open a sql injection attack on your application. if there is another way of achieving your goal than dynamically build queries, please go the other way.

score 0 · Accepted Answer · answered Dec 07 '20 at 16:15

It is not good practise to dynamically pass table name from security perspective.It is not possible in Hibernate however you may use Eclipse-Link implementation if that is still required,

String tableName = "tablename";
String query = "SELECT * FROM " +tablename +"WHERE salary > 10000";

Use application properties file to pick the table name in a native query?

2 Answers2