How to store @Query sql in external file for CrudRepository?

Question

I'm using spring with CrudRepositorys for database connection.

Now I require a quite long (several lines) sql query that I'd prefer to maintain in a file in classpath, rather than directly inside the code.

But how could I achieve this? My repo looks as follows:

@Query(value = "<my very long sql query>", nativeQuery = true) //how to inject file content?
@Modifying
@Transactional
public void executeSpecificSql();

Solution 1

if your project set up has resources folder, create under /META-INF/jpa-named-queries.properties file and add key value pair as repoClass.methodName=yoursql. Spring data will pick up.

For longer queries it's probably best to use xml properties file with CDATA tags: https://stackoverflow.com/a/19128259/1194415

Solution 2

Use below steps.

1. Create jpa-named-queries.property file in src/main/resources-->META-INF Folder

2. Defile your query in given properties file. Above screenshot look closely.Here Group is Entity name, while Method should match with method define in Repository interface. Query should have object name instead table name and instead of column name provide variable name given in entity for respective field.

3. Interface method method with property name

   

Solution 3

Not sure if it fits your setup, but, this can be done by :

1) Adding your query to a hibernate mapping file using the <sql-query> tag

<sql-query name="MyQuery">.......

2) Define a hibernate config file that includes the above file using the <mapping> tag

<mapping resource="MyQuery.sql.xml"/>

3) Defining a persistence file with a property "hibernate.ejb.cfgfile" that points to the above config file

<property name="hibernate.ejb.cfgfile" value="hibernate.cfg.xml"/>

4) Use the above property file to build the EntityManagerFactory

Now, the above Query can be used in the Repository method :

@Query(name = "MyQuery", nativeQuery = true)
[return type] executeMyQuery();

Comments

[membersound]

@see github.com/spring-projects/spring-data-jpa/blob/master/src/t‌​est/…

[Pavlo Morozov]

This solution not answers initial question as it should contain: "quite long (several lines) sql query". Though in config.properties it will be one line or ugly assignment on every new line of code.

[membersound]

This answer is not any different than the accepted one. Thanks for the response though.

[Swarit Agarwal]

Indeed, tried to provide graphics representation for quick understanding.

[Krish]

@rajkumar how to get the xml properties file in spring data jpa custom implementation class ?

[Sodved]

Every solution ends up with this. But I have 1000 line sql with several layers of inline views etc. Would prefer to keep in .sql file for readability. Is there just no way?