'Get query from file in SPRING BOOT using @Query

I have a proyect that use to load queries this:

@Query(value = SELECT_BY_USER_ID, nativeQuery = true)
Employee findByUserId(@Param("userId") String userId);

On "SELECT_BY_USER_ID" is a normal String query.

I have a YML configuration outside jar, that I use to load differents configurations, and I want to use this YML, to load queries too.

Example YML:

file:
    query1: SELECT * FROM DUAL;

But I don't know how to load directly from my file in @Query value, I tried like that:

@Query(value = ("${file.query1}"), nativeQuery = true)
List<Employee> findByCost();

How can I do? Thank you.

javaspringoracleyaml


Solution 1:[1]

Configured queries cannot be loaded from YML, as far as I know. They can be loaded from another file. Create a file in your resource project folder named /META-INF/jpa-named-queries.properties and then place your query:

Employee.findById=select * from Employee e where e.id=?1

and then call your query:

@Query(name = "Employee.findById")
Employee findByUserId(String id);

Solution 2:[2]

Unfortunately, spring data doesn't seem to support direct properties reference withing @Query anno (like in @Value). Despite that and assuming you use spring-data-jpa and Hibernate it is possible to use an external .xml file to store your queries as named queries and refer to them by method name, or like 

@Query(nativeQuery = true, name="Repository1.query1")

This is a nice article on this matter : JPA Queries in XML File and it describes how to place your .xml file elsewhere than the expected orm.xml

Solution 3:[3]

You can use the mirage-sql library Here .It's really great. You can easily pass the Java parameter to the sql file, or pass a dynamic sql string from java code.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Lorelorelore
Solution 2 gmotux
Solution 3 Justin Nguyen

Related Questions