ojdbc

oracle-faq-jdbc
https://www.oracle.com/database/technologies/faq-jdbc.html

mybatis procedure 파라미터 배열

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

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;

ojdbc 경로

C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar
C:\dev\sqldeveloper\jdbc\lib\ojdbc8.jar

1. pom.xml
   ````javascript com.oracle.database.jdbc ojdbc10 19.3.0.0

ojdbc10-19.3.0.0.jar ons-19.3.0.0.jar oraclepki-19.3.0.0.jar osdt_cert-19.3.0.0.jar osdt_core-19.3.0.0.jar simplefan-19.3.0.0.jar ucp-19.3.0.0.jar ````