Menu
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
923
We can use @Query annotation to specify a query within a repository. Following is an example. In this example, we are using JPQL, Java Persistence Query Language. We've added name query custom methods in Repository in JPA Named Query chapter.
yes there is a way you can go about it without using the @query annotation. what you need is to define a derived query from your interface that implements the JPA repository instance.
Select Query In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.
Creating SQL QueriesAdd a query method to our repository interface. Annotate the query method with the @Query annotation, and specify the invoked query by setting it as the value of the @Query annotation's value attribute. Set the value of the @Query annotation's nativeQuery attribute to true.
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); } If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
