Externalize query in spring boot application

Question

I am using spring-boot 1.5.6 RELEASE. I want to do a basic thing, move my queries from the @Query annotation in the repository to any xml file.

After some reading, I figured out that we can use orm.xml or jpa-named-queries.properties to write my custom query.

I don't understand the file structure as to where these XML files have to be present. And I don't have a META-INF folder in my project.

Example:

POJO Class:

@Entity
public Class Customer {

private int id;
private String name;

// getters and setters
}

Repository:

public interface CustomerRepository extends PagingAndSortingRepository<Customer,Integer> {

// this query I need from an external xml file as it might be quite complex in terms of joins
@Query("Select cust from Customers cust")
public List<Customer> findAllCustomers();

}

EDIT: Referred to this stackoverflow question. I need to know where these files (orm.xml and persistence.xml) need to be stored as I don't have a META-INF folder.

Thanks in advance!!

Possible duplicate of [How to use XML configuration for named queries in Spring data JPA?](https://stackoverflow.com/questions/24931248/how-to-use-xml-configuration-for-named-queries-in-spring-data-jpa) — Todd, Sep 05 '17 at 17:46
Yes, I have come across this question. But like I mentioned, I do not have a `META-INF` folder, and I am very much confused as to where these `properties/ xml` files have to be stored. @Todd — Anusha, Sep 05 '17 at 17:47
Please check github example at https://github.com/eclipse/examples/tree/master/jpa/employee.dynamic/src/main/resources/META-INF and http://webdev.jhuep.com/~jcs/ejava-javaee/coursedocs/605-784-site/docs/content/html/hibernate-migration-orm.html, if META-INF is not present then you have to create it. — Amit K Bist, Sep 05 '17 at 18:15

score 10 · Accepted Answer · answered Sep 05 '17 at 18:03

10

Create META-INF inside resources folder . Now create jpa-named-queries.properties file inside that META-INF folder.

Write your query inside this properties file like this:

Customer.findAllCustomerByNamedQuery=Select c from Customer c

Where Customer denote name of entity class and findAllCustomerByNamedQuery is the query name

In your customer repository write this to call your query written in properties file:

List<Customer> findAllCustomerByNamedQuery();

answered Sep 05 '17 at 18:03

Ajit Soman

3,926
3
22
41

Thanks! creating META-INF manually worked for me! Is there any reason why it won't be created along with files like application.properties? – Anusha Sep 06 '17 at 01:05
i don't know the reason for that – Ajit Soman Sep 06 '17 at 04:24
THANK YOU! Exactly what is was looking for. Where did you find these info? – Sebastiandg7 Oct 15 '17 at 05:13
1

@SebastianDuque thanks for you comment . I found these infos from spring-data-example repository , Here is the link :https://github.com/spring-projects/spring-data-examples – Ajit Soman Oct 15 '17 at 06:24
Note that this jpa-named-queries.properties is not being syntax-highlighted by Intellij IDEA – Teddy Oct 18 '21 at 12:08
its not loading for me with spring boot version 2.5.1 – SPS Dec 11 '21 at 19:00
@SPS Can you check the logs . Are you getting any warning or error ? – Ajit Soman Dec 14 '21 at 09:12

score 0 · Answer 2 · answered Apr 14 '23 at 04:17

There is possibility to use named queries from spring data jpa.

 <named-query name="MyData.ultimateFind">
    <query>select d from MyData d where d.column3 = ?1</query>
 </named-query>

To bind it with repository method, your repository class can look like this:

public interface MyDataRepository extends JpaRepository<MyData, Long> {
  List<User> ultimateFind(String dataForColumn3);   
}

Other approach (more sexy, but as of now, having problem with java 17) is to use spring-native-query, which reads sql filed from resources. It allows you to have custom repository type with nice bindings directly to sql files (also with conditional parts of sql if needed.).

Externalize query in spring boot application

2 Answers2

Linked

Related