데이터베이스 질문과 답변
웹에서 엑셀파일 드래그-복사 해서 db에 등록하려고 합니다.
거진 될 것 같은데
Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.
이런 문제 때문에 막히네요.
List 를 파라미터로 pgsql로 전달하는데 이때 발생되는 문제가 아닌가 예상(?) 해 보지만 도무지 알 수가 없네요.
작업 환경 : STS / mybatis / PostgreSQL 9.5.2
// 컨트롤러
@RequestMapping(value ="/cart/addExcelUSA" , method=RequestMethod .GET)
public ModelAndView excel_cart(/*ErpDto erpDto, */
@RequestParam(value = "partNum[]") List<String> partNum ,
@RequestParam(value = "qty[]") List<Integer> qty ,
@RequestParam(value = "vinNum[]") List<String> vinNum ,
@RequestParam(value = "memo[]") List<String> memo ,
Principal principal
){
Stringid=principal.getName();
dao = sqlSession .getMapper (ErpCartDao.class) ;
dao. excel_cartUSA(id , partNum, qty, vinNum , memo);
String url = "redirect:/erp/cart.html";
return new ModelAndView( url);
// dao
public void excel_cartUSA( String id , List <String > partNum , List < Integer> qty, List< String > vinNum , List <String > memo ) ;
// Mapper
<select id="excel_cartUSA" statementType= "CALLABLE" parameterType ="java.util.List">
SELECT "excel_cartUSA"(#{param1}, #{param2}, #{param3}, #{param4}, #{param5})
</select>
// pgsql Function
// pgsql에서 배열을 데이터로 저장할 수 있다는 것을 알고서 아래처럼 작성하였습니다.
CREATE OR REPLACE FUNCTION excel_cartUSA(p_id varchar, p_partNum varchar[], p_qty smallint[], p_vinNum varchar[], p_memo varchar[])
RETURNS void AS
$BODY$
DECLARE
-- x varchar;
v_len integer;
v_country varchar(3);
v_b_name varchar(20);
v_partNum varchar(20);
v_description varchar(255);
v_msrp numeric(9,2);
BEGIN
v_len := array_length(p_partNum, 1);
FOR i IN 1..v_len
Loop
SELECT country, b_name, partNum, description, msrp INTO v_country, v_b_name, v_partNum, v_description, v_msrp
FROM vParts_All
WHERE partNum=p_partNum AND country='USA';
WITH upsert AS(UPDATE cart SET qty=p_qty, vinNum=p_vinNum, memo=p_memo WHERE partNum=p_partNum RETURNING *)
INSERT INTO cart (country, b_Name, partNum, description, qty, id, vinNum, memo)
SELECT v_country, v_b_name, v_partNum, v_description, p_qty, p_id, p_vinNum, p_memo WHERE NOT EXISTS (SELECT * FROM upsert);
END LOOP;
END
$BODY$ LANGUAGE plpgsql;
여기까지 해서 실행해 보면
SEVERE: Servlet.service() for servlet [action] in context with path [/parts] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.
### The error may exist in file [C:\dev\workspace\parts\.metadata\.plugins\org.eclipse.wst.server.core\tmp0\wtpwebapps\parts\WEB-INF\classes\global\erp\mapper\ErpCartMapper.xml]
### The error may involve global.erp.dao.ErpCartDao.excel_cartUSA-Inline
### The error occurred while setting parameters
### SQL: SELECT "excel_cartUSA"(?, ?, ?, ?, ?)
### Cause: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.
; bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.] with root cause
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.util.ArrayList. Use setObject() with an explicit Types value to specify the type to use.
) 버튼을 클릭하여 작성 하시면 됩니다.