CODEWITHSUNDEEP
spring-bootspring-mvcjpaspring-data-jpajpa-2.0
Charan
Charan

Reputation: 31

Spring JPA Query annotation - input a sql file

Can we pass or input a .sql file to the Query annotation in Spring JPA?

Something like this @Query(file:/sql/employeeList.sql , nativeQuery=true)

/sql/employeeList.sql - i would like to put this file somewhere in the code, which will have the select sql in it.

I am not looking to have native sql string @Query("select abc....", nativeQuery=true) -- I dont want to use like this. because my sql is too huge.

Upvotes: 3

Views: 4851

Answers (3)

Can
Can

Reputation: 1

You can check this repository.

You need to provide a JpaQueryMethodFactory bean to your configuration. You can extend the JpaQueryMethod class and override the getAnnotatedQuery and getRequiredAnnotatedQuery functions to read queries from the file.

Upvotes: 0

Ivan Vilanculo
Ivan Vilanculo

Reputation: 668

@Jens Schauder is right you can't specify a sql file in Spring Data Jpa but as an alternative solution you could use this library https://github.com/VEINHORN/spring-data-sqlfile or you could simply load the file on your own.
Note: im using lombok on this example

@Component
@Slf4j
@AllArgsConstructor
public class ArticleBatchRepository {
  @PersistenceContext
  EntityManager em;

  public List<ArticleBatch> findAll(String articleId) {
  Query nativeQuery =  em.createNativeQuery(loadQuery("/db/queries/articles/BatchByWarehouse.sql"), ArticleBatch.class);
    nativeQuery.setParameter(1, articleId);
    return (List<ArticleBatch>) nativeQuery.getResultList();
  }

  private String loadQuery(String path) {
    try {
      InputStream is = this.getClass().getResourceAsStream(path);
      java.util.Scanner s = new java.util.Scanner(is).useDelimiter("\\A");
      return s.hasNext() ? s.next() : "";
    } catch (Exception e) {
      log.error("Failed to load query {}: {}", path, e.getMessage(), e);
      return null;
    }
  }
}

Upvotes: 1

Jens Schauder
Jens Schauder

Reputation: 81882

You can't specify a sql file but Spring Data JPA will look in /META-INF/jpa-named-queries.properties to find named queries, which allows you to specify queries in a sql file.

This is described in this answer to a similar question.

Upvotes: 0

Related Questions