Java Spring Boot项目中的存储过程返回null作为输出 [英] Stored Procedure in Java Spring Boot Project returns null as Output

问题描述

我在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作为输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！