CODEWITHSUNDEEP
hibernatejpaspring-bootspring-data-jpa
Grim
Grim

Reputation: 1835

NativeQuery with three single quotes

My query is failing due to three quotes. It could be another reason but from my knowledge it is caused by the three quotes. I can't seem to figure out a solution for this and I need additional help. I have tried escaping and everything else but can't seem to solve the issue.

I am using Spring Data JPA

@Query(value = "SELECT f FROM Fragrance f WHERE REPLACE(REPLACE(f.name, ' &', ''), '' ', '') = ?1" , nativeQuery = true)
Fragrance findByNameIgnoreSpecialCharacters(String name);

Error

java.lang.IllegalArgumentException: Parameter with that position [1] did not exist
    at org.hibernate.jpa.spi.BaseQueryImpl.findParameterRegistration(BaseQueryImpl.java:502) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]

Upvotes: 1

Views: 2003

Answers (1)

manish
manish

Reputation: 20135

I am unable to run the specified native query on any database so no wonder Hibernate finds it to be malformed.

Specified query

SELECT
  f
FROM
  Fragrance f
WHERE
  REPLACE(REPLACE(f.name, ' &', ''), '' ', '') = ?1

There are two errors in this query. The first error is that SELECT f FROM Fragrance f ... requires there to be a column named f in the Fragrance table and presumably there is none. Remember, this is a native query not a JPA query. So, the correct query should begin with SELECT * FROM Fragrance ....

The second error is that '' ' is invalid text for a query. '' means the single-quote character ('). So, this text does not start correctly. The correct text should be ''' '.

Correct query

SELECT
  *
FROM
  Fragrance
WHERE
  REPLACE(REPLACE(name, ' &', ''), ''' ', '') = ?1

Sample working project available on Github.

Upvotes: 3

Related Questions