Externalize native query in Spring data JPA [duplicate]

Question

I am using spring-data JpaRepository. I have the following native query :

@Query(value = "SELECT SUBSTRING_INDEX(u.email, '@', -1) as domain, COUNT(*) as domainCount, r.invite_organization_id"
        + "  FROM srs_users as u,srs_user_registrations as r where u.user_id=r.user_id and r.invite_organization_id=:orgId"
        + "  GROUP BY "
        + "SUBSTRING_INDEX(u.email, '@', -1) ORDER BY domainCount DESC", nativeQuery = true)
List<Object[]> countTopDomain(@Param("orgId") String orgId );

Can we externalize the above native query in jpa-named-queries.properties just like other named queries.

Answer 1

You can use JPA Named queries. But I'm afraid that is not exactly what you are looking for. Other than that there is no support of externalizing SQL statements.

Answer 2

At resources create a folder META-INF. At META-INF create a default file jpa-named-queries.properties

In this file put your query at any unique key. Say,

chk.test1=SELECT SUBSTRING_INDEX(u.email, '@', -1) as domain, COUNT(*) as domainCount, r.invite_organization_id FROM srs_users as u, srs_user_registrations as r where u.user_id=r.user_id and r.invite_organization_id=:orgId GROUP BY SUBSTRING_INDEX(u.email, '@', -1) ORDER BY domainCount DESC

To use this use below code:

@Query(name = "chk.test1", nativeQuery = true) List<Object[]> countTopDomain(@Param("orgId") String orgId );