[FIXED] Spring data jpa, externalizing native queries

Issue

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.

Solution

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.

Answered By - DarkKnight
Answer Checked By - Cary Denson (JavaFixing Admin)