Creating a custom query with Spring DATA JPA?

Question

I'm working on a project with Spring Data JPA. I have a table in the database as my_query.

I want to create a method which takes a string as a parameter, and then execute it as a query in the database.

Method:

executeMyQuery(queryString) 

As example, when I pass

queryString= "SELECT * FROM my_query" 

then it should run that query in DB level.

The repository class is as follows.

public interface MyQueryRepository extends JpaRepository<MyQuery, Long>{     public MyQuery findById(long id);      @Modifying(clearAutomatically = true)     @Transactional     @Query(value = "?1", nativeQuery = true)     public void executeMyQuery(String query);  } 

However, it didn't work as I expected. It gives the following error.

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''select * from my_query;'' at line 1 

Is there any other way, that I could achieve this goal. Thanks in advance

Answer 1

The only part of it you can parameterise are values used in WHERE clause. Consider this sample from official doc:

public interface UserRepository extends JpaRepository<User, Long> {   @Query(value = "SELECT * FROM USERS WHERE EMAIL_ADDRESS = ?1", nativeQuery = true)   User findByEmailAddress(String emailAddress); }