Java 如何使用 JPA 和 Spring Data 执行存储过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29612550/
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 execute a stored procedure with JPA & Spring Data?
提问by Rys
I am trying to call the Terminal_GetTicketstored procedure in my database but keep getting the following exception:
我试图Terminal_GetTicket在我的数据库中调用存储过程，但不断收到以下异常：
PropertyReferenceException: No property getTicket found for type TicketInfo
I have cross validated my configuration with a very simple test entity and everything seems to work fine, however for the actual case, something is wrong.
我已经用一个非常简单的测试实体交叉验证了我的配置，一切似乎都正常，但是对于实际情况，有些地方是错误的。
Here is my domain entity (TicketInfo):
这是我的域实体 ( TicketInfo)：
@Entity
@NamedStoredProcedureQuery(name = "TicketInfo.getTicket", procedureName = "Terminal_GetTicket", resultClasses = TicketInfo.class, parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "sys_id_game", type = Integer.class)})
public class TicketInfo {
@Id @GeneratedValue
private Long id;
private String idTicket;
private Integer externalTicketCode;
private Short sequenseAlert;
private Integer dlTimeStamp;
All the instance variables have their getters and setters properly defined and the stored procedure has a total of 5 output parameters matching the attributes of TicketInfo.
所有实例变量都正确定义了它们的 getter 和 setter，并且存储过程共有 5 个与TicketInfo.
Furthermore, here is my repository interface:
此外，这是我的存储库界面：
public interface TicketInfoRepository extends CrudRepository<TicketInfo, Long> {
@Transactional(timeout = 5)
@Procedure
TicketInfo getTicket(Integer sys_id_game);
}
Also, here is my context.xmlfile (for Spring):
另外，这是我的context.xml文件（适用于 Spring）：
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:jpa="http://www.springframework.org/schema/data/jpa"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:repository="http://www.springframework.org/schema/data/repository"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-4.1.xsd
http://www.springframework.org/schema/data/jpa
http://www.springframework.org/schema/data/jpa/spring-jpa-1.8.xsd
http://www.springframework.org/schema/data/repository
http://www.springframework.org/schema/data/repository/spring-repository-1.5.xsd">
<context:component-scan base-package="ar.com.boldt.godzilla" />
<jpa:repositories base-package="xx.xxx.xxx.godzilla.business.dao" />
<bean id="jpaVendorAdapter"
class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
<property name="showSql" value="${dataSource.show.sql}" />
<property name="generateDdl" value="false" />
<property name="database" value="SQL_SERVER" />
</bean>
<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="jpaVendorAdapter" ref="jpaVendorAdapter" />
<!-- spring based scanning for entity classes -->
<property name="packagesToScan" value="xx.xxx.xxx.godzilla.business.dao" />
</bean>
<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager" />
<bean id="cacheManager" class="org.springframework.cache.ehcache.EhCacheCacheManager">
<property name="cacheManager" ref="ehcache" />
</bean>
<bean id="ehcache"
class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean">
<property name="configLocation" value="classpath:ehcache.xml" />
</bean>
</beans>
And finally a watered-down version of the stored procedure itself:
最后是存储过程本身的淡化版本：
ALTER PROCEDURE [Terminal_GetTicket](
@arg int
,@res int output
,@res2 int output
)
as
Declare @error int
select 0, 1, 2
RETURN @error
Now, whenever I try setting the @Autowiredannotation, I get the exception mentioned above.
现在，每当我尝试设置@Autowired注释时，都会遇到上面提到的异常。
回答by SirKometa
I remember that I have been struggling with the MS SQL stored procedures and spring-data-jpa. This is how I have been able to successfully run it:
我记得我一直在努力使用 MS SQL 存储过程和 spring-data-jpa。这就是我能够成功运行它的方式：
Model:
模型：
@NamedNativeQueries({
@NamedNativeQuery(
name = "yourInternalName",
query = "EXEC [procedure_name] :param1, :param2",
resultClass = Foo.class
)
})
@Entity
public class Foo{
/* Fields, getters, setters*/
}
That's pretty straightforward. This approach is different though, you are not declaring procedures directly (that's also the reason why it doesn't have to work if you decide to change RDBS).
这很简单。但是，这种方法不同，您不是直接声明过程（这也是如果您决定更改 RDBS 就不必工作的原因）。
Then you have to extend your repository:
然后你必须扩展你的存储库：
public interface FooRepositoryCustom {
Foo fancyMethodName(arg1, arg2);
}
And directly implement it:
并直接实现它：
public class FooRepositoryImpl implements FooRepositoryCustom {
@PersistenceContext
EntityManager entityManager;
@Override
public Foo fancyMethodName(arg1, arg2) {
Query query = entityManager.createNamedQuery("yourInternalName");
query.setParameter("param1", arg1);
query.setParameter("param2", arg2);
return query.getResultList();
}
Let's put it all together:
让我们把它放在一起：
public interface FooRepository extends CrudRepository<Foo, Long>, FooRepositoryCustom {
}
Note that if you decide to return for example a List of Fooobjects you only edit return value in your custom repository.
请注意，如果您决定返回例如Foo对象列表，则您只能在自定义存储库中编辑返回值。
回答by Keyhan
I followed SirKometas advice but I could not get it to work so I came up with something that worked for me and I think from syntax point of view is better. First create your entity class like below.
我遵循了 SirKometas 的建议，但我无法让它发挥作用，所以我想出了一些对我有用的东西，我认为从语法的角度来看更好。首先创建您的实体类，如下所示。
@NamedStoredProcedureQueries({//
@NamedStoredProcedureQuery(//
name = "MySP"//
, procedureName = "my_sp"//
, parameters = { //
@StoredProcedureParameter(mode = ParameterMode.IN, name = "arg", type = String.class)}//
, resultClasses = Foo.class)//})
@Entity
public class Foo {
Then the Implementation class of the repository would be:
那么存储库的实现类将是：
@Component
public class FooRepositoryImpl implements FooCustomRepository {
@PersistenceContext
EntityManager entityManager;
@Override
public List<Foo> foo(String arg) {
Query query = entityManager.createNamedStoredProcedureQuery("MySP");
query.setParameter("arg", arg);
return query.getResultList();
}
}
The rest of the implementation is like the answer from SirKometa above. Think also that you have to create a EntityManager bean in your applicationfor this to work.
其余的实现就像上面来自 SirKometa 的答案。还认为您必须在应用程序中创建一个 EntityManager bean才能使其工作。
