9

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.

Improve this question

4 Answers 4

Reset to default
10

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);
}
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Is this a tested library? Is it actively used in the community?
@geneb. its not actively used by community for now.
But it's a JPA repository
nice library, but you should definitely disclose that you're the author...
6

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);
Improve this answer

2 Comments

And If I want to call outisde resources folder? I mean, outside jar.
Then you should set your query in a property and use a repository with JPA EntityManager
3

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

Improve this answer

1 Comment

Hello. Please review your answer: The provided link is dead.
0

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.

Improve this answer

1 Comment

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - From Review

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.