Execute native SQL Query in JPA / Spring Boot
Here is an example code of how to execute native SQL query with some parameters in JPA / Spring Boot.
First we need use Spring Boot's EntityManagerFactory bean. We can simply use @Autowired annotation to make this available in our class.
Then create new EntityManager session, execute the native SQL and finally close the session.
1package com.firstfewlines.service;
2
3import org.springframework.beans.factory.annotation.Autowired;
4import javax.persistence.EntityManager;
5import javax.persistence.EntityManagerFactory;
6import javax.persistence.NoResultException;
7import java.util.Date;
8
9public class NativeSqlExample {
10
11 @Autowired
12 private EntityManagerFactory entityManagerFactory;
13
14 public Double getDayPrice(String scrip, Date prdate) {
15 EntityManager session = entityManagerFactory.createEntityManager();
16 try {
17 Double daypr = (Double)session.createNativeQuery("Select lastpr FROM StockPrice WHERE scrip=:scrip and prdate = :prdate")
18 .setParameter("scrip", scrip)
19 .setParameter("prdate", prdate)
20 .getSingleResult();
21
22 return daypr;
23 }
24 catch (NoResultException e){
25 return null;
26 }
27 finally {
28 if(session.isOpen()) session.close();
29 }
30 }
31}
The above example shows how to read a single value. Now if we want to fetch a list of rows, instead of using getSingleResult() method we need to use getResultList() method. We can do a type casting to assign that list into a list of compatible domain class.
References:
comments powered by Disqus