java datasource <-> mssql bulk 삽입 (테이블 반환 매개 변수 사용 SQLServerDataTable )

테이블 반환 매개 변수 사용

https://learn.microsoft.com/ko-kr/sql/connect/jdbc/using-table-valued-parameters?view=sql-server-ver16

테이블 반환 매개 변수 사용 - JDBC Driver for SQL Server

 

 

멀티 로우를 데이터 삽입시에 SQLServerDataTable 를 사용하여 한번에 table parameter로 넘기는 방법 넘기는 방법

 

pom.xml

<dependency> 
    <groupId>com.microsoft.sqlserver</groupId> 
    <artifactId>mssql-jdbc</artifactId> 
    <version>9.2.1.jre8</version> 
</dependency>

 

controller

List<HashMap<String, Object>> mapList = new ArrayList<>(); 
for( int i = 0; i < list1.size(); i++ ) { 
    HashMap<String, Object> paramMap = (HashMap<String, Object>) params.clone(); 
    String key = list1.get(i).get(0); 
    paramMap.put("x1", list1.get(i).get(1)); 
    paramMap.put("x2", list1.get(i).get(3)); 
    paramMap.put("y1", list2.get(i).get(key).getField1()); 
    paramMap.put("y2", list2.get(i).get(key).getField2()); 
    paramMap.put("z1", list3.get(i).get(key).getField1()); 
    paramMap.put("z2", list3.get(i).get(key).getField2()); 
    mapList.add(paramMap); 
}				 
tsServiceImpl.saveQma003Upload_bulk(mapList);

 

service

@Autowired 
private DataSource dataSource;

table parameter에 row를 add하여 프로시저 호출

 

public boolean saveUpload_bulk(List<HashMap<String, Object>> mapList) throws SQLServerException { 
	        SQLServerDataTable tvp = new SQLServerDataTable(); 
	        // Define metadata for the data table.   
	        
	        tvp.addColumnMetadata("C1" ,java.sql.Types.NVARCHAR); 
	        tvp.addColumnMetadata("C2" ,java.sql.Types.NVARCHAR); 
	        tvp.addColumnMetadata("C3" ,java.sql.Types.NVARCHAR); 
	        tvp.addColumnMetadata("C4" ,java.sql.Types.NVARCHAR); 
	        tvp.addColumnMetadata("C5" ,java.sql.Types.NVARCHAR); 
	        tvp.addColumnMetadata("C6" ,java.sql.Types.NVARCHAR); 
	         
	        for (HashMap<String, Object> params : mapList) { 
	        	 tvp.addRow( 
	        				StringUtil.blankIfNull(params.get("x1")),//c1 
	        				StringUtil.blankIfNull(params.get("x2")),//c2 
	        				StringUtil.blankIfNull(params.get("y1")),//c3 
	        				StringUtil.blankIfNull(params.get("y2")),//c4 
	        				StringUtil.blankIfNull(params.get("z1")),//c5 
	        				StringUtil.blankIfNull(params.get("z2")),//c6 
	                ); 
			}			 
	         
	        String SQL_COMMAND = "{call P_BULK (?)}"; 
	         
	        try  { 
	        	CallableStatement  callableStmt  = dataSource.getConnection().prepareCall(SQL_COMMAND); 
	        	SQLServerPreparedStatement sqlServerStatement = callableStmt.unwrap(SQLServerPreparedStatement.class); 
	        			 
	        	sqlServerStatement.setStructured(1, "TYPE_UP_DATA_VIB_BULK", tvp); 
	        	return sqlServerStatement.execute(); 
	        } catch (SQLTimeoutException e) { 
				// TODO Auto-generated catch block 
				e.printStackTrace(); 
			} catch (SQLException e1) { 
				// TODO Auto-generated catch block 
				e1.printStackTrace(); 
			} 
	         
	        return false; 
	    }

 

MSSQL

테이블 반환 매개 변수 형식

CREATE TYPE [dbo].[TYPE_UP_DATA_VIB_BULK] AS TABLE( 
	[C1] [nvarchar](50) NULL, 
	[C2] [nvarchar](50) NULL, 
	[C3] [nvarchar](50) NULL, 
	[C4] [nvarchar](50) NULL, 
	[C5] [nvarchar](50) NULL, 
	[C6] [nvarchar](50) NULL
)

 

프로시저

ALTER PROCEDURE [dbo].[P_BULK]  
( 
    @itblParam TYPE_UP_DATA_VIB_BULK Readonly, 
	@p_error_code       VARCHAR(100) = ''   OUTPUT,     -- 사용자 에러코드 리턴   
    @p_row_count        INT = 0             OUTPUT,     -- 실행/리턴하는 레코드행수  
    @p_error_note       NVARCHAR(100) = ''  OUTPUT,     -- 사용자 지정 문자열   
    @p_return_str       NVARCHAR(100) = '' OUTPUT,     -- 사용자 지정 반환값   
    @p_error_str        NVARCHAR(1000) = '' OUTPUT,     -- 오류메세지  
    @ErrorState         VARCHAR(500) = ''   OUTPUT,     -- 오류번호/심각도/오류상태번호오류발생루틴내의 줄번호 
    @ErrorProcedure     NVARCHAR(200) = ''  OUTPUT      -- 오류발생 프로시저/트리거   
)  
AS  
SET NOCOUNT ON   
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
--select * from UP_DATA_VIB_VURK; 
BEGIN  
    BEGIN TRY 
       -----------------------------------------------------------------------------------------------------------------------  
        -- 첫번째 리턴 레코드셋 처리  --첫 번째 Excel Insert 
		INSERT INTO UP_DATA_VIB_VURK  
		(C1, C2, C3, C4, C5, C6) 
		SELECT  
		C1, C2, C3, C4, C5, C6
		FROM @itblParam AS nr; 
		 SELECT @p_row_count = @@ROWCOUNT 
        -- 에러가 없을 경우에 정상적인 처리 진행 
        IF @p_row_count > 0 
            SET @p_error_code = 'MSG0002'                                   -- 정상적으로 등록되었습니다. 
        ELSE 
            SET @p_error_code = 'MSG0029'                                   -- 등록된 레코드가 없습니다.   
        -----------------------------------------------------------------------------------------------------------------------  
    END TRY 
    BEGIN CATCH 
        SELECT @p_error_code =  CASE SUBSTRING('N',1,1) 
                                  WHEN 'Q' THEN 'ERR0006'                 -- 조회시 에러가 발생하였습니다.  
                                  WHEN 'N' THEN 'ERR0008'                 -- 등록시 오류가 발생하였습니다. 
                                  WHEN 'U' THEN 'ERR0009'                 -- 수정시 오류가 발생하였습니다. 
                                  WHEN 'D' THEN 'ERR0010'                 -- 삭제시 오류가 발생하였습니다. 
                                           ELSE 'ERR0000' 
                                  END 
        SELECT @ErrorState = CONVERT(VARCHAR(100), ERROR_NUMBER()) + '|' + CONVERT(VARCHAR(100), ERROR_SEVERITY()) + '|' + CONVERT(VARCHAR(100), ERROR_STATE()) + '|' + CONVERT(VARCHAR(100), ERROR_LINE()), 
                @ErrorProcedure = 'PRC=' + ERROR_PROCEDURE(), @p_error_str = ERROR_MESSAGE(); 
    END CATCH; 
END