我如何使用带有 jpa 的 spring 调用存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7886401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i call stored procedure using spring with jpa
提问by Mohan
Am new to SPRING with JPA techniques.
我是 SPRING 的新手，使用 JPA 技术。
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.
我正在尝试调用用 mysql 5 编写的存储过程。当我尝试使用存储过程获取数据时，调用它喷出异常。
org.springframework.dao.InvalidDataAccessApiUsageException:org.hibernate.QueryException: query must begin withSELECTorFROM:call [call st_proc_getusers()];nested exception isjava.lang.IllegalArgumentException:org.hibernate.QueryException: query must begin withSELECTorFROM:call [call st_proc_getusers()]
org.springframework.dao.InvalidDataAccessApiUsageException：org.hibernate.QueryException：查询必须首先SELECT或FROM：call [call st_proc_getusers()];嵌套异常是java.lang.IllegalArgumentException：org.hibernate.QueryException：查询必须首先SELECT或FROM：call [call st_proc_getusers()]
my persistence.xmlis
我的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 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.
其实我不知道如何使用 jpa 模板调用存储过程。
How can i call stored procedure from spring JPA?
如何从 Spring JPA 调用存储过程？
please give the solution to get out from this issue.
请给出解决方案以摆脱这个问题。
采纳答案by Xavi López
Use EntityManager.createNativeQuery()instead. I don't think it's possible to call a stored procedure through a JPA query.
使用EntityManager.createNativeQuery()来代替。我认为不可能通过 JPA 查询调用存储过程。
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.
您也可以使用@NamedNativeQuery.
回答by Shivan Dragon
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:
调用存储过程意味着执行一些 SQL 语句。你不能用 JPQL 查询来做到这一点（你在代码中得到的内容em.createQuery(...)）。您必须创建一个本机查询，允许您将本机 SQL 发送到数据库，执行它并获得结果：
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();
回答by JanM
JPA 2.1 introduced support for stored procedures. You might want to give it a try instead of a native queries.
JPA 2.1 引入了对存储过程的支持。您可能想尝试一下，而不是本机查询。
