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. Thanks in advance

Answer 1

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/

Answer 2

Using EntityManager you can achieve this .

Suppose your entity class is like bellow:

import javax.persistence.*;
    import java.math.BigDecimal;

    @Entity
    @Table(name = "USER_INFO_TEST")
    public class UserInfoTest {
        private int id;
        private String name;
        private String rollNo;

        public UserInfoTest() {
        }

        public UserInfoTest(int id, String name) {
        this.id = id;
        this.name = name;
        }

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Column(name = "ID", nullable = false, precision = 0)
        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        @Basic
        @Column(name = "name", nullable = true)
        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        @Basic
        @Column(name = "roll_no", nullable = true)
        public String getRollNo() {
            return rollNo;
        }

        public void setRollNo(String rollNo) {
            this.rollNo = rollNo;
        }
    }

And your query is "select id,name from users where roll_no = 1001".

Here query will return a object with id and name column. Your Response class is like bellow:

Your Response class is like:

public class UserObject{
        int id;
        String name;
        String rollNo;

        public UserObject(Object[] columns) {
            this.id = (columns[0] != null)?((BigDecimal)columns[0]).intValue():0;
            this.name = (String) columns[1];
        }

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getRollNo() {
            return rollNo;
        }

        public void setRollNo(String rollNo) {
            this.rollNo = rollNo;
        }
    }

here UserObject constructor will get a Object Array and set data with object.

public UserObject(Object[] columns) {
            this.id = (columns[0] != null)?((BigDecimal)columns[0]).intValue():0;
            this.name = (String) columns[1];
        }

Your query executing function is like bellow :

public UserObject getUserByRoll(EntityManager entityManager,String rollNo) {

        String queryStr = "select id,name from users where roll_no = ?1";
        try {
            Query query = entityManager.createNativeQuery(queryStr);
            query.setParameter(1, rollNo);

            return new UserObject((Object[]) query.getSingleResult());
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

Here you have to import bellow packages:

import javax.persistence.Query;
import javax.persistence.EntityManager;

Now your main class, you have to call this function. First get EntityManager and call this getUserByRoll(EntityManager entityManager,String rollNo) function. Calling procedure is given bellow:

Here is the Imports

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

get EntityManager from this way:

@PersistenceContext
private EntityManager entityManager;

UserObject userObject = getUserByRoll(entityManager,"1001");

Now you have data in this userObject.

Note:

query.getSingleResult() return a object array. You have to maintain the column position and data type with query column position.

select id,name from users where roll_no = 1001

query return a array and it's [0] --> id and 1 -> name.

More info visit this thread .

Thanks :)

Answer 3

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.

Answer 4

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

Answer 5

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.