How can i call stored procedure using spring with jpa

Question

Am new to SPRING with JPA techniques.

am trying to call the stored procedure which is written in mysql 5. when i am trying to get the data using stored procedure call it spewing exception.

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.QueryException: query must begin with SELECT or FROM: call [call st_proc_getusers()]; nested exception is java.lang.IllegalArgumentException: org.hibernate.QueryException: query must begin with SELECT or FROM: call [call st_proc_getusers()]

my persistence.xml is

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit  name="SPT3" transaction-type="RESOURCE_LOCAL">
            <mapping-file>META-INF/persistence-query.xml</mapping-file>
            <class>com.spt3.pojo.Users</class>
            <properties>
                    <property name="hibernate.dialect" value=">org.hibernate.dialect.MySQLDialect" />
                    <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/spring_security" />
                    <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />
                    <property name="hibernate.connection.username" value="user" />
                    <property name="hibernate.connection.password" value="pass" />
                    <property name="cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
                    <property name="hibernate.max_fetch_depth" value="3"/>
                    <property name="hibernate.query.factory_class" value="org.hibernate.hql.classic.ClassicQueryTranslatorFactory"/>
                    <property name="hibernate.query.substitutions" value="true 1, false 0"/>
                    <property name="hibernate.show_sql" value="true"/>
                    <property name="hibernate.hbm2ddl.auto" value="create"/>
            </properties>
    </persistence-unit>
</persistence>

i tried code is

package com.spt3.dao;

import com.spt3.pojo.Users;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceException;
import org.springframework.orm.jpa.JpaCallback;
import org.springframework.orm.jpa.support.JpaDaoSupport;
import org.springframework.transaction.annotation.Transactional;


@Transactional
public class JpaUsersDao extends JpaDaoSupport {

    public void getResultsByStoredProcedure() {       
        List list=(ArrayList)getJpaTemplate().execute(new JpaCallback() {
            public List doInJpa(EntityManager em) throws PersistenceException {
                javax.persistence.Query query=em.createQuery("call st_proc_getusers()"); // Here the procedure call 
                return query.getResultList(); // returning result list
            }
        });        
    }

}

Actually i don't know how to call the stored procedure using jpa template.

How can i call stored procedure from spring JPA?

please give the solution to get out from this issue.

Answer 1

JPA 2.1 introduced support for stored procedures. You might want to give it a try instead of a native queries.

http://docs.oracle.com/javaee/7/api/javax/persistence/EntityManager.html#createStoredProcedureQuery%28java.lang.String%29

Answer 2

Calling a stored procedure means executing some SQL statement. You can't do that with with a JPQL query (what you get in your code when you do em.createQuery(...)). You must create a native query that allows you to send native SQL to the database, execute it and get the results:

 String query = "call st_proc_getusers(?)";
 NativeQuery nq = em.createNativeQuery(query);
 //the following line is necessary only if your stored procedure accepts a parameter:
 nq.setParameter(1, "paramValue");
 List<?> results = em.createNativeQuery(query).getResultList();

Answer 3

Use EntityManager.createNativeQuery() instead. I don't think it's possible to call a stored procedure through a JPA query.

public List doInJpa(EntityManager em) throws PersistenceException {
  javax.persistence.Query query=em.createNativeQuery("call st_proc_getusers()"); 
  return query.getResultList(); 
}

You could also use @NamedNativeQuery.