How to store @Query sql in external file for CrudR

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

Answer 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

Answer 2

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

Answer 3

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