CODEWITHSUNDEEP
spring-bootspring-data-jpa
DarkKnight
DarkKnight

Reputation: 235

Spring data jpa, externalizing native queries

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.

Upvotes: 12

Views: 13071

Answers (4)

DarkKnight
DarkKnight

Reputation: 235

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,

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

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

<entity-mapping> </entity-mapping>

  1. "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() ; }

  2. 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.

Upvotes: 7

nilknow
nilknow

Reputation: 379

I think DarkKnight's solution is the best, set entity mappings with xml can take advantage of IDE to have highlight and indentation.

But if you are using spring boot, there will be an optimization.

There is a related spring boot property for it: spring.jpa.mapping-resources, you can set entity mappings path into this property. And this property can be an array, you can set more than one value into it.

Upvotes: 0

Alexandre Campos
Alexandre Campos

Reputation: 290

You can externalize de value, which is the query itself. Inside the src/main/resources, create a folder called META-INF. Inside it, create a file called jpa-named-queries.properties.

Suppose your entity is called Customer and the table is TBL_CUSTOMER.

When you keep the query inside the code, on your repository, you have the code you wrote. You can externalize it this way:

CustomerRepository.java

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {

    @Query(nativeQuery = true) 
    public List<String> findNameNative();

}

jpa-named-queries.properties

Customer.findNameNative=\
SELECT C.NAME \
FROM TBL_CUSTOMER C \
WHERE CONDITIONS

Names must match and you must use \ for line breaks.

Upvotes: 9

fiveobjects
fiveobjects

Reputation: 4279

Under resources create META-INF/jpa-named-queries.properties. In this file define your queries this way: MyEntity.fetchEntityBySomething=select name from Customer

I have not tried native queries however, usual queries would work this way.

However, check this out: https://github.com/gasparbarancelli/spring-native-query

Upvotes: 2

Related Questions