[java] Is it possible to use raw SQL within a Spring Repository

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

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.

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

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

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