public class TypeUpDataVibHandler extends BaseTypeHandler<List<TypeUpDataVibVo>>{ 
	  @Override 
	  public void setNonNullParameter(PreparedStatement ps, 
	      int i, List<TypeUpDataVibVo> parameter, JdbcType jdbcType) 
	      throws SQLException { 
	    SQLServerDataTable tvp = new SQLServerDataTable(); 

        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 (TypeUpDataVibVo param : parameter) { 
	    	tvp.addRow( 

	    			param.getC1(), 
	    			param.getC2(), 
	    			param.getC3(), 
	    			param.getC4(), 
	    			param.getC5(), 
	    			param.getC6()
				); 
	    } 
	    ps.unwrap(SQLServerPreparedStatement.class).setStructured(i, "TYPE_UP_DATA_VIB_BULK", tvp); 
	  } 
	  @Override 
	  public List<TypeUpDataVibVo> getNullableResult(ResultSet rs, String columnName) throws SQLException { 
	    return null; 
	  } 
	  @Override 
	  public List<TypeUpDataVibVo> getNullableResult(ResultSet rs, int columnIndex) throws SQLException { 
	    return null; 
	  } 
	  @Override 
	  public List<TypeUpDataVibVo> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { 
	    return null; 
	  } 
}

VO

public class TypeUpDataVibVo { 
	String C1; 
	String C2; 
	String C3; 
	String C4; 
	String C5; 
	String C6; 


	public String getC1() { 
		return C1; 
	} 
	public void setC1(String c1) { 
		C1 = c1; 
	} 
	public String getC2() { 
		return C2; 
	} 
	public void setC2(String c2) { 
		C2 = c2; 
	} 
	public String getC3() { 
		return C3; 
	} 
	public void setC3(String c3) { 
		C3 = c3; 
	} 
	public String getC4() { 
		return C4; 
	} 
	public void setC4(String c4) { 
		C4 = c4; 
	} 
	public String getC5() { 
		return C5; 
	} 
	public void setC5(String c5) { 
		C5 = c5; 
	} 
	public String getC6() { 
		return C6; 
	} 
	public void setC6(String c6) { 
		C6 = c6; 
	} 
}

xml 

<select id="saveUpload_bulk" statementType="CALLABLE" resultType="hashMap"> 
{ 
    call P_BULK(#{list, typeHandler=kr.common.data.TypeUpDataVibHandler}) 
} 
</select>

@Override 
public void saveUpload_bulk(List<TypeUpDataVibVo> list) throws SQLServerException { 
    tsMapper.saveUpload_bulk(list); 
}

테이블 반환 매개 변수 사용

pom.xml

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

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

@Autowired 
private DataSource dataSource;

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

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

1. 배포시에 war를 톰켓에 올리기 위하여 war를 export하기 위해서  

하기 가이드를 참고하여 적용해 보도록 하겠습니다

https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto-traditional-deployment

1. SpringBootApplication에 SpringBootServletInitializer 상속 

@SpringBootApplication
public class DemoApplication extends SpringBootServletInitializer{ 

	public static void main(String[] args) {
		SpringApplication.run(DemoApplication.class, args);
	}
	
	@Override protected SpringApplicationBuilder configure(SpringApplicationBuilder builder) { 
		return builder.sources(DemoApplication.class); 
	}
}

2. build.gradle 설정 

1. id 'war' 추가 

plugins {
	id 'org.springframework.boot' version '2.3.2.RELEASE'
	id 'io.spring.dependency-management' version '1.0.9.RELEASE'
	id 'java'
	id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
	id 'war'
}

2. bootwar 적용

docs.spring.io/spring-boot/docs/current/gradle-plugin/api/org/springframework/boot/gradle/tasks/bundling/BootWar.html

bootWar {
	archiveBaseName = 'demoproject'
 	archiveVersion="0.0.1-SNAPSHOT"
}

3. dependencies 추가 

dependencies {
	...
	providedRuntime 'org.springframework.boot:spring-boot-starter-tomcat' 
    ...
}

3. 확인

1. gradle bootWar실행 

2. war 확인 libs폴더 하위에 생성

위와 같이 war를 제대로 추출되는것을 확인 하였습니다. 

JPA는 기본적으로 테이블의 CRUD 메소드를 제공해주기 때문에 SQL문을 직접 사용할 필요가 없습니다. 

그러나 여러 테이블의 데이터를 조합하기 위해서 불필요한 데이터를 가져올 수 밖에 없는데 이러한 점을 해결하기 위해 JPQL (Java Persistence Query Language) 라는 쿼리 언어를 사용하고

QueryDsl은 JPQL의 빌더 역할을 하는 오픈소스입니다. 

gradle 설정

plugins {
     id 'org.springframework.boot' version '2.3.2.RELEASE'
     id 'io.spring.dependency-management' version '1.0.9.RELEASE'
     id 'java'
     id "com.ewerk.gradle.plugins.querydsl" version "1.0.10"
}
…

dependencies {
     implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
     implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
     implementation 'org.springframework.boot:spring-boot-starter-web'
     
     //log
     annotationProcessor 'org.projectlombok:lombok'
     compileOnly 'org.projectlombok:lombok'
     
     //querydsl
     compile("com.querydsl:querydsl-jpa") // querydsl
     compile("com.querydsl:querydsl-apt") // querydsl

     testImplementation('org.springframework.boot:spring-boot-starter-test') {
         exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
     }
     
     //db
     compile group: 'org.mariadb.jdbc', name: 'mariadb-java-client', version: '2.4.1'  
     compile group: 'mysql', name: 'mysql-connector-java', version: '8.0.16'
}

//querydsl 
def querydslSrcDir = 'src/main/generated'
querydsl {
    library = "com.querydsl:querydsl-apt"
    jpa = true
    querydslSourcesDir = querydslSrcDir
}
compileQuerydsl{
    options.annotationProcessorPath = configurations.querydsl
}
configurations {
    querydsl.extendsFrom compileClasspath
}
sourceSets {
    main {
        java {
            srcDirs = ['src/main/java', querydslSrcDir]
        }
    }
}
//querydsl

사용하기 

1. Entity클래스 만들기 

@Entity
@Table(name="lo_mst")  
@Getter
@Setter
public class EntityMst{
     @Id
     @Column(name = "id")
     @GeneratedValue(strategy=GenerationType.IDENTITY)
     private int id;
     @Column(name = "_order")
     private int _order;
     @Column(name = "num1")
     private int num1;
     @Column(name = "num2")
     private int num2;
     @Column(name = "num3")
     private int num3;
     @Column(name = "num4")
     private int num4;
     @Column(name = "num5")
     private int num5;
     @Column(name = "num6")
     private int num6;
     @Column(name = "num7")
     private int num7;
     @Column(name = "win_date")
     private String winDate;
     @Column(name = "reg_dt")
     private LocalDateTime regDt;
}

Entity클래스를 생성 후 빌드를 하게되면 gradle에서 설정한 generated폴더에 클래스가 생성하게 됩니다. 

2. QueryDsl 사용 

아래와 같이 생성된 클래스, 변수에 접근하여 쿼리를 작성할 수 있습니다. 

MainDto.LottoInfo result = queryFactory.select(Projections.bean(MainDto.LottoInfo.class 
                 , ExpressionUtils.as(
                        JPAExpressions.select(entityMst._order.max())
                        .from(entityMst),  "lastOrder")
                 ,entityMst._order
                 ,entityMst.num1
                 ,entityMst.num2
                 ,entityMst.num3
                 ,entityMst.num4
                 ,entityMst.num5
                 ,entityMst.num6
                 ,entityMst.num7
                 ,entityMst.winDate
                 ,entityMstEtc.etc
                 ,entityMstEtc.totalSalesPrc
                 ,entityMstEtc.payLimit
                 ))
               .from(entityMst)
               .innerJoin(entityMstEtc)
               .on(entityMst._order.eq(entityMstEtc._order))
               .where(builder)
               .orderBy(entityMst._order.desc())
               .fetchFirst();