Is it possible to use raw SQL within a Spring Repository

Question

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

Answer 1

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.

Answer 2

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. —Source

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

Your Response class is like this:

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. The 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 the 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 :)

Answer 3

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

for Example :

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

Answer 4

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

Answer 5

This is how you can use in simple form

@RestController
public class PlaceAPIController {

    @Autowired
    private EntityManager entityManager;

    @RequestMapping(value = "/api/places", method = RequestMethod.GET)
    public List<Place> getPlaces() {
        List<Place> results = entityManager.createNativeQuery("SELECT *  FROM places p limit 10").getResultList();
        return results;
    }
}

Answer 6

It is also possible to use Spring Data JDBC, which is a fully supported Spring 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.