Spring data jpa, externalizing native queries

Question

I am using Spring data jpa for executing native Query, here is the example.

@Query(value = "select name from customer", nativeQuery = true) 
public List<String> findNameNative() ;

Now, due to company constraint can't put the entire query here, but query is pretty huge, like 100 lines.

N we have many such queries.

Is there a way i can define queries in a separate file like xml or properties file and refer them here. (to keep the code clean)

Appericiate the help.

Thanks.

Answer 1

After many efforts and tries found the solution.

1) create the xml file (with any name) in resources folder of your project. Say testSQL.xml inside resources /query

2) follow the xml standard of 'orm.xml' in testSQL.xml, this copy paste the header and create the tags of,

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"
version="1.0">
<named-native-query>
</named-native-query>
</entity-mapping>

3) in this xml create the tag with named-native-query tag.

<named-native-query name="XyzEntity.methodName">
<query>
<! [CDATA[
Your native query
] ] >
</query>
</named-native-query>

Note - > multiple such native named query tags can be added and all of them must reside between

<entity-mapping> </entity-mapping>

4) "XyzEntity" mentioned in name tag in above step, should have a Jpa repository and in that repository we should have method with the same name as the tag. I. E.

public interface XyzRepo extends JpaRepository <XyzEntity, Long> {
Tuple methodName() ; 

}

5) add the testSQL.xml in application property file as below

spring.jpa.mapping-resources = query/testSQL.xml

N then you can call this method normal spring way.

Kindly let me know if someone is stuck on this and need detail solution.