CODEWITHSUNDEEP
mysqlsqlspringspring-dataspring-data-jpa
B378
B378

Reputation: 1117

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?

Upvotes: 36

Views: 226171

Answers (5)

B378
B378

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

milan zadfiya
milan zadfiya

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

Jens Schauder
Jens Schauder

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:

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.

Upvotes: 4

ilya
ilya

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

Austine Gwa
Austine Gwa

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

Related Questions