Is it possible to use raw SQL within a Spring Repository

spring data jpa native query mapping
spring data jpa nested query
spring data jpa complex query
spring data jpa custom query
jpa native query example with parameters
spring boot raw sql query
spring data jpa insert query example
spring data jpa join query example

I need to use raw SQL within a Spring Data Repository, is this possible? Everything I see around @Query is always entity based.


The @Query annotation allows to execute native queries by setting the nativeQuery flag to true.

Quote from Spring Data JPA reference docs.

Also, see this section on how to do it with a named native query.

Spring Data JPA @Query, In this tutorial on Spring Data JPA, I am going to share with you how to use the @​Query annotation to run custom SQL queries. Spring Data JPA  The easiest way to do so is by using the Spring namespace that is shipped with each Spring Data module that supports the repository mechanism. XML configuration Each Spring Data module includes a repositories element that allows you to simply define a base package that Spring scans for you.


YES, You can do this on bellow ways:

1. By CrudRepository (Projection)

Spring Data Repositories usually return the domain model when using query methods. However, sometimes, you may need to alter the view of that model for various reasons.

Suppose your entity is like this :

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

Now your Projection class is like bellow. It can those fields that you needed.

public interface IUserProjection {
     int getId();
     String getName();
     String getRollNo();
}

And Your Data Access Object(Dao) is like bellow :

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.ArrayList;

public interface UserInfoTestDao extends CrudRepository<UserInfoTest,Integer> {
    @Query(value = "select id,name,roll_no from USER_INFO_TEST where rollNo = ?1", nativeQuery = true)
    ArrayList<IUserProjection> findUserUsingRollNo(String rollNo);
}

Now ArrayList<IUserProjection> findUserUsingRollNo(String rollNo) will give you the list of user.

2. Using EntityManager

Suppose 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 and this Thread

Thanks :)

Spring Data JPA Native SQL Query, I need to use raw SQL within a Spring Data Repository, is this possible? Everything I see around @Query is always entity based. 128 Is it possible to use raw SQL within a Spring Repository Apr 11 '13 84 spring junit load application context for tests Feb 18 '13 84 My Application Could not open ServletContext resource Feb 19 '13


It is also possible to use Spring Data JDBC repository, which is a community project built on top of Spring Data Commons to access to databases with raw SQL, without using JPA.

It is less powerful than Spring Data JPA, but if you want lightweight solution for simple projects without using a an ORM like Hibernate, that a solution worth to try.

Is it possible to use raw SQL within a Spring Repository, In these situations, you better use Spring Data JPA's @Query annotation to specify a custom JPQL or native SQL query. Using Spring Data JPA, you can define the sorting of your query results in 2 ways: You can add an  In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.


we can use createNativeQuery("Here Nagitive SQL Query ");

for Example :

Query q = em.createNativeQuery("SELECT a.firstname, a.lastname FROM Author a");
List<Object[]> authors = q.getResultList();

Ultimate Guide: Custom Queries with Spring Data JPA's @Query , But you can also use native queries to perform native SQL statements. I could use a standard JPQL query for this, but I want to focus on the JPA part and not bother you with As you can see, the created Query can be used in the same way as any JPQL query. I was looking the same, how can I write dynamic quries. Always use parameterization for raw SQL queries When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.


It is possible to use raw query within a Spring Repository.

      @Query(value = "SELECT A.IS_MUTUAL_AID FROM planex AS A 
             INNER JOIN planex_rel AS B ON A.PLANEX_ID=B.PLANEX_ID  
             WHERE B.GOOD_ID = :goodId",nativeQuery = true)

      Boolean mutualAidFlag(@Param("goodId")Integer goodId);

Native Queries, How can we create the query methods that invokes our named queries We can specify named queries with Spring Data JPA by using a properties file, annotations, or the orm.xml file In other words, our properties file must use the following format: Add a named-native-query element to the orm.xml file. Spring Data JPA facilitates the implementation of JPA based repositories. It enhances support for JPA based data access layers. It makes it easier to build Spring-powered applications that use data access technologies. Spring Data JPA is a part of the larger Spring Data family. Spring Data JPA @Query


Spring Data JPA Tutorial: Creating Database Queries With Named , This blog post describes how you can create query methods by using the @​Query annotation. If you are not familiar with Spring Data JPA, you should read the following blog In other words, if we create a query method called findbyId() and Is there a way to eager load enities wher usin native queries? For spring data to be able to match the NamedNativeQuery, the Class name of the domain entity followed by a dot, needs to be prefixed to the name of the NamedNativeQuery. So the name should be (Assuming the domain entity is Group) 'Group.getGroupDetails'.


Spring Data JPA Tutorial: Creating Database Queries With the , In this JPA native query example, we will learn to use JPA named query there is a possibility that you will overwrite the correct version already stored in the  Using this element looks up Spring Data repositories as described in Section 1.2.3, “Creating repository instances”.Beyond that it activates persistence exception translation for all beans annotated with @Repository to let exceptions being thrown by the JPA persistence providers be converted into Spring's DataAccessException hierarchy.


JPA native query example, The @Query annotation can also be used to declare native queries. extends CrudRepository<Employee, Long> { @Query(value = "SELECT  Rest Repository for building a data repository and a client to access the repository. JPA and H2 in the SQL section for a JPA and a relational database. We will use an in-memory relational database, H2, to simplify the database setup and configuration. Finally, we click Generate Project.