19

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();
membersound
  • 74,158
  • 163
  • 522
  • 986
  • 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

3 Answers3

17

Use below steps.

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

  2. Defile your query in given properties file. enter image description here 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

    List item

Swarit Agarwal
  • 2,276
  • 22
  • 31
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

membersound
  • 74,158
  • 163
  • 522
  • 986
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. – Pavlo Morozov 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
1

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();
souser
  • 796
  • 2
  • 8
  • 25