How to populate @Query value for native queries in Spring boot Repository when Queries are stored as external files

Question

Similar question has been asked and answered back in 2015 here.

However , rather than storing queries in XML files , I would like to store them in external .sql files and read query from there. To achieve this in our spring boot application - 2.1.5.RELEASE with Java 8 , we are storing queries in resources folder as below

src/main/resources - queries - A.sql - B.sql

To read the above queries , I'm reading them in QueryReader class as below

@Component
public class QueryReader {

    public String getQueryFromFile(String fileName) {
        System.out.println("getQueryFromFile() : " + fileName);
        return this.convertInputStreamToString(this.getTemplateAsStream(fileName));
    }




     private InputStream getTemplateAsStream(String queryFileName){
            ClassLoader classLoader = getClass().getClassLoader();
            InputStream iStream =classLoader.getResourceAsStream("queries/" + queryFileName);

            return iStream;
        }
}

And to use it anywhere in the code , i'm having below class , so that i can call it's methods

@Component
public class MyQueries {


    @Autowired
    private QueryReader qfr;

    public String queryErroredRecords() {
        return qfr.getQueryFromFile("A.sql");
    }

While using it with JDBCTemplate , this works as expected but when I'm trying to use this method from @Query annotation from Repository , i'm not able to do so with errors as below.

Repository Code

@Repository
public interface AdminRepository extends JpaRepository<OrderBackupRecordEO, BigDecimal>{

    @Autowired
    public MyQueries queries;


@Query(value = queries.queryErroredRecords() , nativeQuery= true)
    List<String> findX();
}

Above repository is giving errors as below :

1.The value for annotation attribute Query.value must be a constant expression (For @Query annotation)

2.The blank final field queries may not have been initialized (For @Autowired annotation)

How can I make it work?

score 2 · Answer 1 · answered Dec 18 '19 at 09:33

2

I would just add a note that may be helpful for you.

Your solution cannot work just because in java in an interface all the fields (variables) are by default public, static and final and that why you cannot @Autowire any dependencies inside the interface.

That's why it works for your case for JDBCTemplate (which is a class) and doesn't work for spring data repository (which is an interface).

answered Dec 18 '19 at 09:33

star67

1,505
7
16

Yes , you are right .Thought there might other way or annotation to make it work. – Sid Dec 19 '19 at 10:15

score 1 · Answer 2 · answered Dec 18 '19 at 09:27

Spring already has a solution "from the box", you don't need to make all these manipulations.

@Repository
public interface AdminRepository extends JpaRepository<OrderBackupRecordEO, BigDecimal> {

    List<String> findX();
}

File src/main/resources/META-INF/jpa-named-queries.properties:

OrderBackupRecordEO.findX=\
    SELECT record FROM OrderBackupRecordEO record WHERE ...

That's all! No any loading, manipulations, manual handling - an implementation is hidden in Spring - very simple and reliable.

Every solution ends up with this. But I have 1000 line sql with several layers of inline views etc. Would prefer to keep in `.sql` file for readability. Is there just no way? — Sodved, Sep 23 '20 at 00:56

How to populate @Query value for native queries in Spring boot Repository when Queries are stored as external files

2 Answers2