Get query from file in SPRING BOOT using @Query

Question

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.

Answer 1

If you need to load SQL from resources folder, you can try spring-data-sqlfile library. It supports loading SQL queries from resources. So you just need to put your SQL queries to the resources folder and than you can reference them in SqlFromResource annotation:

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @SqlFromResource(path = "select_user_by_id.sql")
    User findById(int userId);
}

The output will be like:

@Repository
public interface UserRepositoryGenerated extends JpaRepository<User, Integer> {    
  @Query(
      value = "SELECT *     FROM users     WHERE id = :userId",
      nativeQuery = true
  )
  User findById(int userId);
}

Answer 2

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);