Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I execute a stored procedure with JPA & Spring Data?

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.

Here is my domain entity (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.

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

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

like image 778
Rys Avatar asked Apr 13 '15 18:04

Rys


People also ask

How do you execute a stored procedure?

Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure. In the Execute Procedure dialog box, specify a value for each parameter and whether it should pass a null value.


1 Answers

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:

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

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 Foo objects you only edit return value in your custom repository.

like image 175
SirKometa Avatar answered Sep 18 '22 08:09

SirKometa