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?

score 57 · Accepted Answer · edited Sep 03 '19 at 11:50

57

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

edited Sep 03 '19 at 11:50

Community

1
1

answered Mar 23 '17 at 04:12

ilya

958
2
9
16

Thank you @ilya. Is there an alternative approach to achieve this task using Spring Data JPA? Without `@Query` annotation? – B378 Mar 23 '17 at 04:21
3

@benji If you really need such behaviour, then you need to get EntityManager and run queries using it directly. As a reference, see this answer: http://stackoverflow.com/a/15341601/187241. But think twice before. If you need this in many places, probably, Spring Data is not right library for you. Its main idea is to hide queries from you, and let you just tune them a little bit, for example, using `@Query` annotation. – ilya Mar 23 '17 at 04:51
Does `USERS` have to be name of the model(domain) class or you have to look the name of the table in the DB? – hane Smitter Nov 21 '22 at 12:39
In this case `USERS` is a name of a table as it's a native SQL query: `nativeQuery = true`. – ilya Nov 23 '22 at 17:03

Jens Schauder · Answer 2 · 2019-09-03T16:43:48.417

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:

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.custom-implementations

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.

score 2 · Answer 3 · answered Dec 10 '19 at 18:36

2

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

answered Dec 10 '19 at 18:36

milan zadfiya

21
3

score 1 · Answer 4 · answered Jul 05 '18 at 01:41

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/

score 1 · Answer 5 · answered Aug 24 '18 at 10:01

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.

Creating a custom query with Spring DATA JPA?

5 Answers5

Linked