Stored Procedure in Java Spring Boot Project returns null as Output

Question

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.

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();
    }

...

}

Following is the stored procedure:

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

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.

Philipson

Answer 1

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

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

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();
    }

...

}

Philipson