How to read a query from a resource with Spring?

Question

Here is my repository:

public interface MyObjectRepository extends CrudRepository<MyObject, Long> {

    @Query("<a-complex-query>")
    List<MyObject> doBusinessSpecificComplexQuery();

}

I can type directly my complex query in the @Query annotation but the code loose its readability.

I would like to do something like below instead:

public interface MyObjectRepository extends CrudRepository<MyObject, Long> {

    @Query(load("classpath:my-complex-query.sql"))
    List<MyObject> doBusinessSpecificComplexQuery();

}

This won't work since @Query value parameter expects a constant value.

How can I read my complex query from a resource ?

Answer 1

You can use JPA Named Query. You need to put the query in the orm.xml file like this:

 <named-query name="User.findByLastname">
    <query>select u from User u where u.lastname = ?1</query>
 </named-query>

For example and then you just need to declare the method findByLastname in your repository class:

public interface UserRepository extends JpaRepository<User, Long> {
  List<User> findByLastname(String lastname);   
}

You can see the documentation in Spring Data Documentation or for 2023 version of dependency have a look at Spring Data JPA 3.0.3 Documentation.