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();

I know this is a bit obvious using the same syntax you usually use for `@Value`? i.e. `@Query(value = "${my.property.name}", nativeQuery = true)` and then put it in a properties file that you load? — Crembo, Jan 12 '15 at 12:49
Ok I did not know this works. Anyhow, how could I then load a property that is spread over multiple lines? — membersound, Jan 12 '15 at 12:51
To be honest I don't know if it works, I cannot really try at the moment, I'm just suggesting to try it (that's why I posted it as a comment rather than an answer). For your other question: http://stackoverflow.com/questions/8975908/how-to-write-multiple-line-property-value-using-propertiesconfiguration — Crembo, Jan 12 '15 at 12:53
@membersound you can try https://github.com/VEINHORN/spring-data-sqlfile library. — veinhorn, Apr 13 '19 at 21:27

score 17 · Answer 1 · answered May 18 '18 at 10:42

17

Use below steps.

Create jpa-named-queries.property file in src/main/resources-->META-INF Folder
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.
Interface method method with property name

answered May 18 '18 at 10:42

Swarit Agarwal

2,520
1
26
33

This answer is not any different than the accepted one. Thanks for the response though. – membersound May 18 '18 at 16:33
10

Indeed, tried to provide graphics representation for quick understanding. – Swarit Agarwal May 21 '18 at 07:09

score 7 · Accepted Answer · edited Sep 25 '17 at 11:28

7

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

edited Sep 25 '17 at 11:28

membersound

81,582
193
585
1,120

answered May 17 '17 at 16:33

Rajkumar

172
2
3

1

@see https://github.com/spring-projects/spring-data-jpa/blob/master/src/test/resources/META-INF/jpa-named-queries.properties – membersound May 17 '17 at 21:11
6

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. – P_M Aug 02 '17 at 06:49
@rajkumar how to get the xml properties file in spring data jpa custom implementation class ? – Krish Feb 13 '19 at 13:58
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? – Sodved Sep 23 '20 at 00:57
@membersound Dead link bro – TheRealChx101 May 08 '23 at 07:55
How does it know which repository to pick up if there is more than one file with similar names under different packages? – TheRealChx101 May 08 '23 at 08:05

souser · Answer 3 · 2015-01-15T16:45:10.070

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();

score 1 · Answer 4 · answered Oct 25 '22 at 08:23

Starting from Java 15 you can store queries as Text blocks in a separate class:

public class SomeLongQuery {
    public static final QUERY = 
        """
        SELECT * 
        FROM table
        WHERE
        ...
        ORDER BY some_column;
        """;
}

And use it as a string constant:

@Query(value = SomeLongQuery.QUERY, native = true)
Iterable<SomeEntity> findByLongQuery();

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

4 Answers4

Linked