3

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.

souvikc
  • 991
  • 1
  • 10
  • 25
  • https://stackoverflow.com/questions/57164662/spring-data-jpa-externalizing-native-queries – David Jan 23 '20 at 07:12

2 Answers2

3

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
1

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

Spandan
  • 11
  • 6