Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 727
Webnet Avatar asked Apr 11 '13 12:04

Webnet


People also ask

How execute native SQL query in Spring data JPA?

We can use @Query annotation to specify a query within a repository. Following is an example. In this example, we are using native query, and set an attribute nativeQuery=true in Query annotation to mark the query as native. We've added custom methods in Repository in JPA Custom Query chapter.

Should I use raw SQL?

Conclusion. Raw SQL is for sure the most powerful way to interact with your database as it is the databases native language. The drawback is that you might use features which are specific to that database, which makes a future database switch harder.

Can we execute native SQL query in Hibernate?

Hibernate provide option to execute native SQL queries through the use of SQLQuery object. Hibernate SQL Query is very handy when we have to execute database vendor specific queries that are not supported by Hibernate API.

Which is faster JPQL or native query?

In some cases it can happen Hibernate/JPA does not generate the most efficient statements, so then native SQL can be faster - but with native SQL your application loses the portability from one database to another, so normally is better to tune the Hibernate/JPA Query mapping and the HQL statement to generate more ...


2 Answers

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.

like image 116
zagyi Avatar answered Sep 20 '22 03:09

zagyi


YES, You can do this in 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 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 :)

like image 43
Md. Sajedul Karim Avatar answered Sep 20 '22 03:09

Md. Sajedul Karim