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?

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