Execute native SQL Query in JPA / Spring Boot

Sep 30, 2020 · 1 min read · Spring Boot JPA Native SQL  ·
Share on:

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