Java Spring Boot 项目中的存储过程返回 null 作为输出

问题描述

我在 Spring Boot 项目中使用存储过程并尝试获取输出值，但在我的项目中它总是返回 null.但是，如果我通过 HeidiSQL 调用该过程，它会起作用并给我正确的值.

I'm using a stored procedure in a Spring Boot project and trying to get an output value, but in my project it always returns null. But if I'm calling the procedure through HeidiSQL it works and gives me the right value.

所以它必须对我的 java 代码做一些事情.我调试了受影响的方法，但找不到它返回 null 的原因.

So it has to do something with my java code. I debugged the affected method, but couldn't find out why it returns null.

我已经尝试查找其他帖子，但找不到与我的特定问题相匹配的内容.

I already tried to look up other posts, but couldn't find something that matches my specific issue.

这是我尝试使用存储过程的方法:

This is my method where I try to use the stored procedure:

CompanyResourceServiceImpl

@Service
public class CompanyResourceServiceImpl implements CompanyResourceService {

@PersistenceContext
    private EntityManager entityManager;

...

private int getMetalResourceByPlayerId(int theId) {

        StoredProcedureQuery theQuery = entityManager.createStoredProcedureQuery("getAllMetalFromCompaniesByPlayerId");

        theQuery.registerStoredProcedureParameter(1, Integer.class, ParameterMode.IN);
        theQuery.registerStoredProcedureParameter(2, BigDecimal.class, ParameterMode.OUT);

        theQuery.setParameter(1, theId);

        theQuery.execute();

        BigDecimal outAmount = (BigDecimal) theQuery.getOutputParameterValue(2);

        return outAmount.intValue();
    }

...

}

以下是存储过程:

getAllMetalFromCompaniesByPlayerId

CREATE DEFINER=`root`@`localhost` PROCEDURE `getAllMetalFromCompaniesByPlayerId`(
    IN `playerId` INT,
    OUT `metalSum` DECIMAL(19,2)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
SELECT sum(cr.amount) as metalSum
FROM company_resource cr
JOIN company c ON (c.id = cr.company_id) WHERE c.player_id = playerId and cr.resource_id = 1;
END

我的目标是获取输出值并在 @Scheduled 方法中使用它.就像我说的，在 HeidiSQL 中存储过程是有效的.

My goal is to get the output value and use it in a @Scheduled method.And like I said, in HeidiSQL the stored procedure works.

推荐答案

经过几个小时的尝试，我找到了让它工作的方法.

After hours of trying I found a way to make it work.

首先我将 @NamedStoredProcedureQuery 添加到我的 CompanyResource 实体类中:

First I add @NamedStoredProcedureQuery to my CompanyResource entity class:

CompanyResource.java

@Entity
@Table(name = "company_resource")
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "getAllMetalFromCompaniesByPlayerId",
                                    procedureName = "getAllMetalFromCompaniesByPlayerId",
                                    parameters = {
                                        @StoredProcedureParameter(mode = ParameterMode.IN, name = "playerId", type = Integer.class),
                                        @StoredProcedureParameter(mode = ParameterMode.OUT, name = "metalSum", type = BigDecimal.class)
                                    })
})
@IdClass(CompanyResourcePK.class)
public class CompanyResource {
...
}

然后我在 CompanyResourceServiceImpl 中更改了我的 getMetalResourceByPlayerId() 方法，如下所示:

Then I changed my getMetalResourceByPlayerId() method in CompanyResourceServiceImpl as followed:

CompanyResourceServiceImpl.java

@Service
public class CompanyResourceServiceImpl implements CompanyResourceService {

@PersistenceContext
    private EntityManager entityManager;

...

private int getMetalResourceByPlayerId(int theId) {

        StoredProcedureQuery theQuery = entityManager.createNamedStoredProcedureQuery("getAllMetalFromCompaniesByPlayerId");

        theQuery.setParameter("Param1", theId);

        BigDecimal outAmount = (BigDecimal) theQuery.getSingleResult();

        return  outAmount.intValue();
    }

...

}

这篇关于Java Spring Boot 项目中的存储过程返回 null 作为输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！