如何使用JPA& amp;执行存储过程？春季数据？ [英] How can I execute a stored procedure with JPA & Spring Data?

问题描述

我正在尝试在我的数据库中调用 Terminal_GetTicket 存储过程但仍然遇到以下异常：

I am trying to call the Terminal_GetTicket stored procedure in my database but keep getting the following exception:

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.

这是我的域实体（ 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;

所有实例变量都正确定义了getter和setter，存储过程总共有5个输出匹配 TicketInfo 属性的参数。

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.

此外，这是我的存储库界面：

Furthermore, here is my repository interface:

public interface TicketInfoRepository extends CrudRepository<TicketInfo, Long> {
    @Transactional(timeout = 5)
    @Procedure
    TicketInfo getTicket(Integer sys_id_game);
}

此外，这是我的 context.xml 文件（对于Spring）：

Also, here is my context.xml file (for 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

现在，每当我尝试设置 @Autowired 注释时，我都会得到上面提到的异常。

Now, whenever I try setting the @Autowired annotation, I get the exception mentioned above.

推荐答案

我记得我一直在努力学习MS SQL存储过程和spring-data-jpa。这就是我能够成功运行它的方法：

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:

型号：

@NamedNativeQueries({
    @NamedNativeQuery(
            name = "yourInternalName",
            query = "EXEC [procedure_name] :param1, :param2",
            resultClass = Foo.class
    )
 })
 @Entity
 public class Foo{

     /* Fields, getters, setters*/
}

这非常简单。这种方法是不同的，你不是直接声明程序（这也是你决定改变RDBS时不必工作的原因）。

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).

然后你必须扩展你的存储库：

Then you have to extend your repository:

public interface FooRepositoryCustom {

     Foo fancyMethodName(arg1, arg2);
}

并直接实施：

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 {

}

请注意，如果您决定返回例如 Foo 对象的列表，则只需编辑自定义中的返回值库。

Note that if you decide to return for example a List of Foo objects you only edit return value in your custom repository.

这篇关于如何使用JPA& amp;执行存储过程？春季数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！