문제 발생 시나리오
프로시저를 콜하는 곳에서 아래 오래가 발생하였다.
Unable to register CallableStatement out parameter [Parameter number 9 is not an OUT parameter] [n/a]
Caused by: java.sql.SQLException: Parameter number 9 is not an OUT parameter
문제는 아래에서 보듯 9번째 매개변수라는 건 없다는 것이다.
@Procedure(name = "sp_searchingRecipeCntByConditionsAndSortByConditions")
long findRecipeCntByConditions(
@Param("recipeName") String recipeName,
@Param("createdDate") LocalDateTime createdDate,
@Param("cookMethod") String cookMethod,
@Param("ingredientAndCon") Boolean ingredientAndCon,
@Param("ingredientDelimString") String ingredientDelimString,
@Param("ServingConditionMin") Integer ServingConditionMin,
@Param("ServingConditionMax") Integer ServingConditionMax,
@Param("cookCategory") String cookCategory);
문제 해결 시도
프로시저에 in을 사용하여 입력값임을 확실해 해주었다.
create procedure sp_searchingRecipeCntByConditionsAndSortByConditions
(
in recipeName varchar(255),
in createdDate datetime,
in cookMethod varchar(255),
in ingredientAndCon boolean,
in ingredientDelimString varchar(255),
in ServingConditionMin int,
in ServingConditionMax int,
in cookCategory varchar(255),
)
begin
DECLARE delimiter VARCHAR(255);
DECLARE startPoint INT;
DECLARE endPoint INT;
DECLARE sortFlag INT;
DECLARE ingreCnt INT;
DECLARE off_set INT;
(...)
drop TEMPORARY TABLE IF EXISTS temp_table;
drop TEMPORARY TABLE IF EXISTS temp_table2;
end;
drop procedure sp_searchingRecipeCntByConditionsAndSortByConditions;
그러나 문제는 해결되지 않았다.
작동하는 간단한 테스트용 프로시저를 작성하였다.
CREATE procedure plus1inout (OUT res int)
BEGIN
select count(*) into res from recipe;
end
@Procedure("plus1inout2")
Integer explicitlyNamedPlus1inout();
위처럼 호출하면 recipe의 개수를 반환해준다. 정상 작동하였다. 재밌는 점이 이와 똑같이 아래 프로시저를 작성하였다.
CREATE procedure sp_searchingRecipeCntByConditionsAndSortByConditions (OUT res int)
BEGIN
select count(*) into res from recipe;
end
이는 작동하는 것을 확인하였다. spring data jpa의 문서를 확인하였다.
https://docs.spring.io/spring-data/jpa/reference/jpa/stored-procedures.html
요지는 내가 한 것처럼 name=을 붙이는 것이 아닌 procedureName= 을 붙이라는 것, 혹은 붙이지 말라는 것이었다.
또한 spring data jpa에서는 출력값을 프로시저의 out 변수에서 가져온다. 즉 out 변수를 세팅해야한다. 아래는 바르게 고친 프로시저이다.
create procedure sp_searchingRecipeCntByConditionsAndSortByConditions
(
recipeName varchar(255),
createdDate datetime,
cookMethod varchar(255),
ingredientAndCon boolean,
ingredientDelimString varchar(255),
ServingConditionMin int,
ServingConditionMax int,
cookCategory varchar(255),
out res int
)
begin
(...)
select count(distinct result.recipe_id) into res
from(
select r.*, i.name, COUNT(lr.recipe_id) as like_cnt from recipe r
left join like_recipe as lr on r.recipe_id = lr.recipe_id
left join ingredient as i on r.recipe_id = i.recipe_id
where
(recipeName is null or match(r.recipe_name) against(recipeName)) and
(createdDate is null or r.created_at >= createdDate) and
(cookMethod is null or r.cook_method = cookMethod) and
(ServingConditionMin is null or (r.servings >= ServingConditionMin and r.servings <= ServingConditionMax)) and
(cookCategory is null or (r.categorie = cookCategory)) and
(
ingredientDelimString IS NULL OR
(...)
)
)
group by r.recipe_id, i.name
) as result;
drop TEMPORARY TABLE IF EXISTS temp_table;
drop TEMPORARY TABLE IF EXISTS temp_table2;
end;
올바르게 작동한다!!
결과 및 결론
나에게 한 가지 안 좋은 습관이 있는 것 같다. 바로 쉬워보이면 일단 해버리는 것. 그리고 공식 문서가 아닌 다른 블로그나 스택 오버 플로우를 우선 참조하는 것. 그러나 이번에는 프로시저를 통한 호출을 하는 사람이 많지 않았고, 그에 따라 관련 래퍼런스가 부족했다.
하지만 처음부터 공식 문서를 봤으면 시간을 훨씬 줄였을 것이다. 명심하자. 블로그 글을 찾고 바로 해결이 안되면 공식문서를 바로 보자! 스택 오버 플로우를 하루종일 뒤지는 건 그
댓글