Reputation: 1117
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?
Upvotes: 36
Views: 226172
Reputation: 1117
Based on @jelies answer, I am using the following approach
You can create another interface for your custom methods (as example MyQueryCustom) and then implement it as follows.
public class MyQueryRepositoryImpl implements MyQueryRepositoryCustom {
@PersistenceContext
private EntityManager entityManager;
public int executeQuery(String query) {
return entityManager.createNativeQuery(query).executeUpdate();
}
}
This will execute a custom query.
Upvotes: 7
Reputation: 21
if you want to add custom query you should add @Param
@Query("from employee where name=:name")
employee findByName(@Param("name)String name);
}
this query will select unique record with match name
.this will work
Upvotes: 2
Reputation: 81998
There is no special support for this. But what you can do is create a custom method with a String
parameter and in your implementation get the EntityManager
injected and execute it.
Possibly helpful links:
How to access entity manager with spring boot and spring data
Note: I would reconsider if what you are trying to do is a good idea because it bleeds implementation details of the repository into the rest of the application.
Upvotes: 4
Reputation: 1018
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);
}
Upvotes: 63
Reputation: 812
Thank you @ilya. Is there an alternative approach to achieve this task using Spring Data JPA? Without @Query annotation?
I just want to act on this part. 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.
then from your repository instance you will be exposed to all the methods that allow CRUD operations on your database such as
interface UserRepository extends CrudRepository<User, Long> {
long deleteByLastname(String lastname);
List<User> removeByLastname(String lastname);
}
with these methods spring data will understand what you are trying to archieve and implement them accordingly.
Also put in mind that the basic CRUD operations are provided from the base class definition and you do not need to re define them. for instance this is the JPARepository class as defined by spring so extending it gives you all the methods.
public interface CrudRepository<T, ID extends Serializable>
extends Repository<T, ID> {
<S extends T> S save(S entity);
Optional<T> findById(ID primaryKey);
Iterable<T> findAll();
long count();
void delete(T entity);
boolean existsById(ID primaryKey);
}
For more current information check out the documentation at https://docs.spring.io/spring-data/jpa/docs/current/reference/html/
Upvotes: 1