CODEWITHSUNDEEP
spring-bootjpaspring-data-jpaspring-data
souvikc
souvikc

Reputation: 1031

CRUDRepository native query with parameter

I am using a native query in spring data JpaRepository like below : 

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

The above gives me the following exception : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'r.invite_organization_id FROM users u,registrations r where u.user' at line 1

How will I pass the value for invite_organization_id(in the query ) from the method countTopDomain() argument.

Upvotes: 0

Views: 2342

Answers (2)

Jeya Nandhana
Jeya Nandhana

Reputation: 358

Try this one

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

Upvotes: 0

AKS
AKS

Reputation: 11

'r.invite_organization_id FROM srs_users u,srs_user_registrations r', have syntax error. ',' is missing after count (*) as domainCount

Upvotes: 1

Related Questions