13
  • I want to write query (properties or yaml) in external file to load database. -
  • This is long query and does not look good to eye when placed inside @Query("long query") in XXXRepository class.
  • Is there way to write this query in an external file (properties, yaml, xml or json) and call that file in @Query() in spring data jpa?
Ishtiaq Maqsood
  • 528
  • 4
  • 9
  • 24
  • 2
    Possible duplicate of [How to store @Query sql in external file for CrudRepository?](https://stackoverflow.com/questions/27902242/how-to-store-query-sql-in-external-file-for-crudrepository) – Jacob van Lingen Jan 22 '19 at 14:16
  • Is this answer relevant? https://stackoverflow.com/a/55667349/1005607 There's a library for this, it looks like. – gene b. Feb 03 '20 at 18:56

3 Answers3

10

You can use named queries, where the queries have to be defined in a file called META-INF/jpa-named-queries.properties. See the spring example:

User.findBySpringDataNamedQuery=select u from User u where u.lastname=?1

Reference the query by name in the annotation in your repository, here the corresponding repository example from spring:

@Query(name = "User.findBySpringDataNamedQuery", countProjection = "u.firstname")
Page<User> findByNamedQueryAndCountProjection(String firstname, Pageable page);
Ralf Stuckert
  • 2,120
  • 14
  • 17
  • 3
    thanks for the answer. My query spreads on 500 lines to load data in Neo4j, how I can incorporate such long query in properties file as query spreads on multiple lines. I see one way to add / at end of each line but its not convenient placing \ 500 times. Is there any better way to write query in properties file that spans on multiple lines ? – Ishtiaq Maqsood Jul 09 '17 at 23:20
  • Try to define your named query in the [META-INF/orm.xml](https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/resources/META-INF/orm.xml) instead. But I'm not sure if JPA eats multiline queries, let me know if this works. – Ralf Stuckert Jul 10 '17 at 07:16
  • Thanks again for the reply. I tried but it did not work, getting this error, ERROR 10384 --- [nio-8080-exec-8] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: Error executing Cypher; Code: Neo.ClientError.Statement.SyntaxError; Description: Invalid input 'a': expected 'r/R' or 'e/E' (line 1, column 2 (offset: 1)) "dataLoad" – Ishtiaq Maqsood Jul 10 '17 at 15:34
2

As suggested by Ralf Stuckert, use META-INF/jpa-named-queries.properties to store named queries

You can use \ to split the long query into multiple lines.

Example:

Customer.findNameNative=\
SELECT C.NAME \
FROM CUST_TABLE C \
WHERE CONDITIONS
Sastrija
  • 3,284
  • 6
  • 47
  • 64
0

I think you're finding a place to put your big query string as readable? Like Mybatis xml. As my knowledge There is no method to do that in data jpa.

But you can put that big query inside of a Stored Procedure and call it easily like below.

Call your_stored_procedure_name(param1,param2):
Mafei
  • 3,063
  • 2
  • 15
  • 37