如何在SPRING DATA JPA中调用存储过程Mysql

Question

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

调用存储过程jpa

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

如何在spring数据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 p6间谍通话没问题！但在日志中

16:26:30[INFO][main] [] [] LocalContainerEntityManagerFactoryBean：初始化的JPA EntityManagerFactory为持久性单元'PeressanceUnitTest'16:26:31[INFO][main] [] [] p6spy：1490189191865|ConnectionId 2|elapse 118|类别语句{调用GET_TERMINAL_TRANSACTION_KEY（1）}测试运行：1，失败：0，错误：1，跳过：0，时间过去：5.18秒

Answer 1

最佳解决方案

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