I need to use raw SQL within a Spring Data Repository, is this possible? Everything I see around @Query
is always entity based.
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.
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.
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.
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 ...
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.
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 :)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With