MyBatis 프로시저와 TypeHandler

1. FileVO

@Data
public class FileVO {
	private String userid;
	private String filename;
	private String filetype;
}

2. UserVO

@Data
public class UserVO{
	private String userid;
	private String name;
	private String[] hobby;
	private List<FileVO> files;
}

String[] 타입과 List\ 타입의 파라미터 전달

3. OracleArrayHandler

public class OracleArrayHandler implements TypeHandler<Object> {
  @Override
  public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    OracleConnection conn = ps.getConnection().unwrap(OracleConnection.class); 		
	Array reportsArray = (Array)conn.createOracleArray("STRINGARRAY", (String[])parameter);		
	ps.setArray(i, reportsArray);
  }

4. mapper xml과 인터페이스

	<insert id="insertUser" statementType="CALLABLE">
	call insert_user(
		  #{userid},
		  #{name},
		  #{hobby, typeHandler=com.yedam.app.OracleArrayHandler},
		  #{files, typeHandler=com.yedam.app.OracleArrayStructHandler}
		)
	</insert>	

	public void insertUser(UserVO vo);

5. 테스트파일

@SpringBootTest
public class UserMapperTest {

	@Autowired 
	UserMapper userMapper;
	
	@Test
	public void testStruct() {
		UserVO vo = new UserVO();
		vo.setUserid("9999");
		vo.setName("test");
		vo.setHobby(new String[] {"read","ski"});
		vo.setFiles(List.of(new FileVO("test1","jpg"),
				            new FileVO("test2","pdf")));
		userMapper.insertUser(vo);	
	}
}

6. 오라클 타입 생성

create or replace type STRINGARRAY as table of varchar2(30);


create or replace TYPE FILETYPE AS OBJECT 
(   USERID VARCHAR2(20),
    FILENAME	VARCHAR2(20),
    TYPE	VARCHAR2(20)
);

create or replace type fileArray as table of FILETYPE;

7. procedure

create or replace PROCEDURE INSERT_USER 
(
    p_userid VARCHAR,
    p_name VARCHAR,
    p_hobby STRINGARRAY,
    p_files FILEARRAY 
) AS 
BEGIN
    --profile 등록
    
    --hobby (String[]) 등록
    for i in 1..p_hobby.count loop
        insert into hobbys values (p_userid, p_hobby(i));
    end loop;
    
    --file (List<FileVO>) 등록
     for i in 1..p_files.count loop
        insert into files values (p_userid, p_files(i).filename, p_files(i).filetype);
    end loop;
    
END INSERT_USER;