如何在SPRING DATA JPA中调用存储过程Mysql [英] How to call stored procedures Mysql in SPRING DATA JPA

问题描述

我阅读了如何在 JPA 中调用 mysql 存储过程

I read How to call mysql stored procedures in JPA

call-stored-procedures-jpa

但是我的程序 api 没有输出参数！

but my procedure api no out put parament !

如何在spring数据jpa中调用并返回结果

How to call it and return result in spring data jpa

CREATE DEFINER=`tester`@`%` PROCEDURE `GET_TERMINAL_TRANSACTION_KEY` (in a_id_term integer unsigned)
BEGIN
declare v_trn_key bigint unsigned;
    set v_trn_key = null;
    start transaction;
    select seq from TERMINAL_TRANSACTION_KEY where id_term = a_id_term into v_trn_key for update;
    if v_trn_key is null then
        set v_trn_key = 1;
        insert into TERMINAL_TRANSACTION_KEY(id_term,seq) values(a_id_term,v_trn_key);
    else
        set v_trn_key = v_trn_key + 1;
        update TERMINAL_TRANSACTION_KEY set seq = v_trn_key where id_term = a_id_term;
    end if;
    commit;
    select v_trn_key;
END$$

Java 实体

@Entity
@Table(name = "TERMINAL_TRANSACTION_KEY")
@XmlRootElement
@NamedStoredProcedureQueries({
   @NamedStoredProcedureQuery(name = "getNextTransactionKeyByTerminalId", 
                              procedureName = "GET_TERMINAL_TRANSACTION_KEY",
//                              resultClasses = {Object.class},
                              parameters = {
                                 @StoredProcedureParameter(mode = ParameterMode.IN, name = "a_id_term", type = Long.class)
                              })
})

public class TerminalTransactionKey implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Long id;
    @Basic(optional = false)
    @NotNull
    @Column(name = "id_term" ,unique=true, nullable=false)
    private int idTerm;
    @Column(name = "seq")
    private BigInteger seq;

    public TerminalTransactionKey() {
    }

    public TerminalTransactionKey(Long id) {
        this.id = id;
    }

我的仓库

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;



public interface TerminalTransactionKeyRepository extends  CrudRepository<TerminalTransactionKey, Long>{


    @Procedure(name = "getNextTransactionKeyByTerminalId")
    Long nextIdByTerminalId(@Param("a_id_term") Long terminalId);

    @Query("UPDATE TerminalTransactionKey SET seq=:nextExtTrnId WHERE idTerm=:terminalId")
    int updateSequence(@Param("nextExtTrnId")Long nextExtTrnId, @Param("terminalId") Long terminalId);

}

和我的测试

@Test
public void testNextId() {
    log.info(repository.nextIdByTerminalId(1L));

}

out p6spy 调用没问题！但在日志中

out p6spy call is ok! but in log

16:26:30 [信息] [主要] [] []LocalContainerEntityManagerFactoryBean:初始化的JPAEntityManagerFactory 用于持久化单元PersistanceUnitTest"16:26:31 [信息] [主要] [] [] p6spy:1490189191865|connectionId2|经过 118|类别声明{call GET_TERMINAL_TRANSACTION_KEY(1)} 测试运行:1，失败:0，错误:1，跳过:0，经过的时间:5.18 秒 <<<失败！- 在ru.service.TerminalTransactionKeyServiceImplTesttestNextId(ru.service.TerminalTransactionKeyServiceImplTest)经过的时间:0.47秒＜＜＜错误！org.springframework.dao.InvalidDataAccessApiUsageException:无效命名参数和位置参数的混合；嵌套异常是java.lang.IllegalArgumentException:命名和无效的混合位置参数

16:26:30 [INFO ] [main] [] [] LocalContainerEntityManagerFactoryBean:Initialized JPA EntityManagerFactory for persistence unit 'PersistanceUnitTest' 16:26:31 [INFO ] [main] [] [] p6spy:1490189191865|connectionId 2|elapsed 118|category statement {call GET_TERMINAL_TRANSACTION_KEY(1)} Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 5.18 sec <<< FAILURE! - in ru.service.TerminalTransactionKeyServiceImplTest testNextId(ru.service.TerminalTransactionKeyServiceImplTest) Time elapsed: 0.47 sec <<< ERROR! org.springframework.dao.InvalidDataAccessApiUsageException: Invalid mix of named and positional parameters; nested exception is java.lang.IllegalArgumentException: Invalid mix of named and positional parameters

推荐答案

最佳解决方案

@Override
public Long nextIdByTerminalId(Long terminalId) {
    return jdbcTemplate.queryForObject("CALL GET_TERMINAL_TRANSACTION_KEY(?)", new Object[]{terminalId}, Long.class);
}

这篇关于如何在SPRING DATA JPA中调用存储过程Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！