How can I call a stored procedure using spring with jpa

Question

New in SPRING with JPA methods.

I am trying to call a stored procedure written in mysql 5. When I try to retrieve data using a stored procedure, throw this spewing exception.

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

my persistence.xml

 <?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 the code

 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 a stored procedure using the jpa template.

How can I call a stored procedure from SPRING JPA?

please give a solution to get out of this problem.

Answer 1

Use EntityManager.createNativeQuery() . I don’t think it is possible to call a stored procedure through a JPA request.

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

You can also use @NamedNativeQuery .

Answer 2

Calling a stored procedure means executing some SQL statement. You cannot do this with a JPQL query (what you get in your code when you do em.createQuery(...) ). You must create your own query that allows you to send your own 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

JPA 2.1 introduced support for stored procedures. You might want to try instead of regular queries.

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