Spring Boot run native SQL Query

Here is an example code of how to run native SQL query with parameter in 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, run the native SQL and finally close the session.

package com.firstfewlines.service;

import org.springframework.beans.factory.annotation.Autowired;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.NoResultException;
import java.util.Date;

public class NativeSqlExample {

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    public Double getDayPrice(String scrip, Date prdate){
        EntityManager session = entityManagerFactory.createEntityManager();
        try {
            Double daypr = (Double)session.createNativeQuery("Select lastpr FROM StockPrice WHERE scrip=:scrip and prdate = :prdate")
                    .setParameter("scrip", scrip)
                    .setParameter("prdate", prdate)
                    .getSingleResult();

            return daypr;
        }
        catch (NoResultException e){
            return null;
        }
        finally {
            if(session.isOpen()) session.close();
        }
    }
}


References:


Hansaraj avatar
About Hansaraj
Hansaraj is a Software Engineer experienced in Java, Groovy, JavaScript, SQL, C#, C++
comments powered by Disqus