Creating a custom query with Spring DATA JPA?

spring data jpa join query example
spring data jpa dynamic query
spring data jpa custom query
spring data jpa complex query
spring data jpa native query mapping
native insert query in jpa repository
spring data jpa custom query limit 1
how to execute sql query in spring boot

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

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

Spring Data JPA Tutorial Part Three: Custom Queries with Query , The only part of it you can parameterise are values used in WHERE clause. Consider this sample from official doc: public interface� The second part of my Spring Data JPA tutorial described how you can create a simple CRUD application with Spring Data JPA.This blog entry will describe how you can use query methods for creating custom queries with Spring Data JPA.

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.

Spring Data JPA @Query, Spring Boot Data JPA @Query tutorial shows how to create custom queries with Spring is a popular Java application framework for creating� As of Spring Data JPA release 1.4, we support the usage of restricted SpEL template expressions in manually defined queries that are defined with @Query. Upon query execution, these expressions are evaluated against a predefined set of variables. Spring Data JPA supports a variable called entityName. Its usage is select x from #{#entityName} x.

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/

Creating a custom query with Spring DATA JPA?, Defining Query Methods. Creating Repository Instances. Custom Implementations for Spring Data� Spring Data JPA also allows you to use the Spring Expression Language (SpEL) expressions in custom queries that are defined using the @Query annotation. Upon query execution, these expressions are evaluated against a predefined set of variables.

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 an object with id and a name column. Your Response class is like below:

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 an Object Array and set data with the 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 below:

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 .

Spring Boot Data JPA @Query tutorial, Let us get started with the creation of a sample Spring Data JPA application. Create an Application. We have already created� Defining a Custom JPQL Query. Creating a JPQL query with Spring Data JPA’s @Query annotation is pretty straightforward. You need to annotate a method on your repository interface with the @Query annotation and provide a String with the JPQL query statement.

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.

Spring Data JPA, you how to define and use custom queries with Spring Data JPA. and you want to learn how Duration: 15:09 Posted: Sep 25, 2019 In this article, we will show you how to add a custom method to Spring Data JPA CrudRepository and MongoDB MongoRepository. 1. CrudRepository. 1.1 Review a CustomerRepository, we will add a custom method to this repository.

Spring Data JPA Custom Queries using @Query Annotation, Take a look at how Spring Data allows us to add custom methods to a Spring Data Repository, complete with plenty of code examples. Created by gkatzioura on 6/2/16. So what we need is a native JPA query like this one:. As of version 1.1.0 Spring Data JPA ships with a custom CDI extension that allows using the repository abstraction in CDI environments. The extension is part of the JAR so all you need to do to activate it is dropping the Spring Data JPA JAR into your classpath.

Spring Data JPA: Ultimate Guide to Custom Queries with @Query , With Spring Data, we can easily write queries usinng @Query. lastName( lastName) // lastName from parameter .build(); return repository. For example, if you've used custom queries with Spring Data JPA before, you� In previous articles, I have talked about creating and using derived, custom, and named queries in Spring Data JPA to interact with the database. I also explained how to apply simple ordering to query results while retrieving data.

Add Custom Functionality to a Spring Data Repository, By default Spring Data JPA inspects the Id-Property of the given Entity. If the Id-Property is null, then the entity will be assumed as new, otherwise as not new. Implementing Persistable: If an entity implements the Persistable interface, Spring Data JPA will delegate the new-detection to the isNew - Method of the Entity.

Comments
  • Thank you @ilya. Is there an alternative approach to achieve this task using Spring Data JPA? Without @Query annotation?
  • @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: 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.