benji benji -4 years ago 209
MySQL Question

Creating a custom query with Spring DATA JPA?

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 Source

Unfortunately, your approach will not work. If you use @Query annotation, you provide one correct concrete query in JPA or native notation per a method.

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);
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download