Use application properties in native spring @Query

Question

I have a repository with a method that executes a native query to get data from the database:

public interface ObjRepository extends CrudRepository<Obj, Long> {
    @Query(value = "SELECT obj.*\n" +
            "FROM obj\n" +
            "WHERE obj.value < #{${property.name}} and obj.value2 < :arg", nativeQuery = true)
    public List<Obj> getObjs(@Param("arg") int arg);

I'm trying to use a property as a constant in the query. The property is defined in the application.properties file as property.name=300. When I try to run the query, I get an exception:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "{"

If I replace #{${property.name}} with the actual number in the string, the query works fine. Is my SpEL syntax wrong? How can I use a property defined in application.properties in a query string?

Answer 1

Based on this answer:

arbitrary expressions are not implemented/supported.

Please check on Spring Data JPA documentaiton regarding Using SpEL expressions

As of Spring Data JPA release 1.4 we support the usage of restricted SpEL template expressions in manually defined queries via @Query

Spring Data JPA supports a variable called entityName.

Other Soultion

There was a similar question. And you can try something from its answer.

Change your ObjRepository

public interface ObjRepository extends CrudRepository<Obj, Long> {
    @Query(value = "SELECT obj.*\n" +
                "FROM obj\n" +
                "WHERE obj.value < :value and obj.value2 < :arg", nativeQuery = true)
    public List<Obj> getObjs(@Param("arg") int arg, @Param("value") int value);

And then you can use it like this

@Service
public class SomeService {

    @Value("${property.name}")
    private int value;

    @Autowired
    private ObjRepository objRepository ;

    public List<Obj> getObjs(int arg) {
        return objRepository.getObjs(arg, value);
    }
}