[스프링 프레임워크] Multiple ResultSets, SELECT 결과 TABLE 여러개일 때

결과 테이블이 여러개 - ResultSet이 여러개 리턴하는 저장 프로시저 SP

저장 프로시저(Stored Procedure) 의 실행 결과가 여러 테이블을 출력할 수 있습니다. 이러한 경우 단순히 JdbcTemplate 등에서 사용하는 queryForObject() 나 queryForList() 등의 메서드로는 하나의 테이블 결과만을 다룰 수 있기 때문에 여러 테이블이 결과로 리턴되는 경우 처리할 수 없습니다.

JDBC API 측면에서 보면 쿼리의 실행 결과가 여러 테이블을 리턴한다는 것은 ResultSet을 여러개 리턴한다는 것입니다.

MySQL 샘플

다음은 여러 테이블을 결과로 리턴하는 매우 심플한 예제입니다. 별 의미 있는 DB는 아니며 단순히 예로 사용하기 위한 두 개의 테이블입니다. 다시 보니 인코딩도 제대로 설정해주지 않았네요. 그러나 테스트용이므로 예제를 학습하는데에 별다른 영향이 없습니다.

또한 꼭 MySQL이 아니더라도 다른 DBMS의 프로시저인 경우에도 상관없으며, 여기서는 편의상 DB를 MySQL로 사용했습니다.

먼저 사용자 정보 테이블 생성 쿼리입니다.

-- 유저 테이블 생성
CREATE TABLE user (
  user_name char(50),
  user_code char(50)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;                                                        

다음은 부서테이블 생성 쿼리입니다.

-- 부서 테이블 생성
CREATE TABLE departments (
  dept_name char(50),
  dept_code char(50)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;                                                        

그리고 각각의 테이블에 예제 데이터를 INSERT 합니다.

-- 유저 샘플 데이터 입력                                                                        
INSERT INTO user VALUES
('kim', 'u001'),
('lee', 'u002'),
('park', 'u003'),
('ryu', 'u004'),
('choi', 'u005');
 
 
-- 부서 샘플 데이터 입력
INSERT INTO departments VALUES
('Customer Service', 'a001'),
('Development', 'a002'),
('Finance', 'a003'),
('Human Resources', 'a004'),
('Marketing', 'a005'),
('Production', 'a006'),
('Quality Management', 'a007'),
('Research', 'a008'),
('Sales',  'a009');

그리고 다음과 같이 단순히 두 개의 테이블을 SELECT 하는 프로시저를 작성합니다.

--프로시저 정의
DELIMITER $$
CREATE PROCEDURE multiSelect()
BEGIN
    Select * from springdb.user;
    Select * from springdb.departments;                                                        
END$$
DELIMITER ;

스프링 DataSource 설정

스프링을 기준으로 설명하지만 일반 Java SE 어플리케이션이나 스프링 기반이 아닌 Servlet/JSP 환경의 웹 어플리케이션인 경우에도 크게 다른점은 없습니다. 다만 여기서는 Bean 설정만 스프링 컨테이너를 사용하는 것 뿐이며 핵심 로직은 JDBC API인 javax.sql.DataSource 와 java.sql.CallableStatement 이 두 가지 클래스를 사용할 것입니다.

스프링 프로젝트는 다음과 같이 메이븐으로 구성합니다. 꼭 똑같이 맞추어 주지 않아도 상관없습니다.

pom.xml dependencies 설정입니다. DataSource 구현체는 c3p0의 com.mchange.v2.c3p0.ComboPooledDataSource 를 사용합니다. 별다를게 없습니다. 스프링, mysql-jdbc, c3p0 정도가 핵심이며 log4j는 없어도 상관 없습니다.

    <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.1.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.1.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.2.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>                                    
            <version>5.1.30</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
    </dependencies>

 

 

먼저 스프링 컨테이너 appCtx.xml bean 설정입니다. DataSource 빈 하나만 컨테이너에 띄워주면 됩니다. 자신의 환경이 있다면 그에 맞게 설정해주면 됩니다. DB 권한 및 계정은 미리 만들어 두었고, MySQL에 맞게 설정해 주었습니다.

<?xml version="1.0" encoding="UTF-8"?>
 
 
<beans xmlns="http://www.springframework.org/schema/beans" 
    xmlns:tx="http://www.springframework.org/schema/tx" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd"> 
 
 
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" 
        destroy-method="close">
        <property name="driverClass" value="com.mysql.jdbc.Driver" />
        <property name="jdbcUrl" value="jdbc:mysql://192.168.111.128:3306/springdb?noAccessToProcedureBodies=true" />
        <property name="user" value="springuser" />
        <property name="password" value="123456" />
    </bean>
 
 
</beans>

프로시저 처리 소스

Main.java 소스입니다. 이 소스 하나에 모든 내용이 담겨있습니다.

package main;
 
 
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
 
import javax.sql.DataSource;
 
 
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.GenericXmlApplicationContext;
 
 
import com.mchange.v2.c3p0.ComboPooledDataSource; 
 
 
public class Main {
 
 
    private static ApplicationContext applicationContext = null;
    private static DataSource dataSource = null;
    
    
    private final static int USER_TABLE = 1;
    private final static int DEPT_TABLE = 2;
 
 
    public static void main(String[] args) throws IOException {
        applicationContext = new GenericXmlApplicationContext("classpath:appCtx.xml");
        dataSource = applicationContext.getBean(ComboPooledDataSource.class);
        multipleResultSets();
    }
    
    public static void multipleResultSets() {
        List<Map<String, Object>> userRows = new ArrayList<>();
        List<Map<String, Object>> deptRows = new ArrayList<>();
                
        final String storedProcedure = "{call multiSelect()}"; // 파라미터가 있는 경우{call multiSelect (?, ?)}
        Connection connection = null;
 
 
        try{
            connection = dataSource.getConnection();
            CallableStatement callableSt = connection.prepareCall(storedProcedure);
//            callableSt.setString(1, "첫번째 파라미터 값"); //프로시저 파라미터가 있는 경우
//            callableSt.setString(2, "두번째 파라미터 값"); //프로시저 파라미터가 있는 경우
            
            boolean isExistRusult = callableSt.execute();
 
 
            int currentTable = 1;
            while (isExistRusult) {
                ResultSet rs = callableSt.getResultSet();
 
 
                //한 행씩 데이터 추출
                while (rs.next()) {
                    Map<String, Object> rowMap = new HashMap<>();
                    
                    if (currentTable == USER_TABLE) {
                        rowMap.put("userName", rs.getString("user_name"));
                        rowMap.put("userCode", rs.getString("user_code"));
                        userRows.add(rowMap);
                    } else if (currentTable == DEPT_TABLE) {
                        rowMap.put("deptName", rs.getString("dept_name"));
                        rowMap.put("deptCode", rs.getString("dept_code"));
                        deptRows.add(rowMap);
                    }
                    
                }
                
                rs.close();           
                
                //ResultSet이 더 있는지 확인
                isExistRusult = callableSt.getMoreResults();
                currentTable++;
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        
        //결과 확인
        System.out.println(userRows);
        System.out.println(deptRows);
    }
 
 
}
 

main 메서드에서는 DataSource를 얻기 위한 로직뿐입니다. 스프링 환경이 아니라면 자신의 환경에 맞게 DataSource를 생성하시면 됩니다. DataSource는 Connection을 얻기 위함인데, 처음부터 곧바로 Connection을 생성하셔도 관계없습니다. DataSource를 얻고 multipleResultSets() 메서드를 호출합니다.

    public static void main(String[] args) throws IOException {
        applicationContext = new GenericXmlApplicationContext("classpath:appCtx.xml");
        dataSource = applicationContext.getBean(ComboPooledDataSource.class);
        multipleResultSets();
    }

multipleResultSets() 메서드의 초반 부분입니다. CallableStatement 를 통해 프로시저를 호출할 때에는 {call 프로시저명(param1, param2, ...)} 형태의 문자열 표현식을 Connection에 넘겨주고 prepareCall() 메서드를 통해 얻을 수 있습니다.

파라미터가 있는 경우에는 프로시저를 실행하기 전에 callableSt.setXXX() 메서드를 통해서 자료형별로 프로시저 호출 파라미터를 세팅해줄 수 있습니다.

        final String storedProcedure = "{call multiSelect()}"; // 파라미터가 있는 경우{call multiSelect (?, ?)}
        Connection connection = null;
 
 
        try{
            connection = dataSource.getConnection();
            CallableStatement callableSt = connection.prepareCall(storedProcedure);
//            callableSt.setString(1, "첫번째 파라미터 값"); //프로시저 파라미터가 있는 경우
//            callableSt.setString(2, "두번째 파라미터 값"); //프로시저 파라미터가 있는 경우

프로시저 파라미터까지 셋팅한 이후에는 excute() 메서드로 프로시저를 호출하고 호출 결과가 CallableStatement에 담깁니다. excute()의 호출 결과가 있는 경우에는 true를 리턴합니다. 없으면 false입니다. 결과가 있는것을 isExistResult 변수로 확인하고 있으면 while문을 실행합니다. 결과가 있다는 것은 ResultSet으로 결과 테이블을 처리할 수 있다는 뜻입니다.

callableSt.getResultSet() 메서드를 통해 ResultSet을 얻습니다. callableSt.getResultSet()로 얻는 ResultSet은 프로시저가 출력하는 테이블 순서와 동일하므로 반복문을 돌 때 if문으로 분기해주어야 합니다. 저는 상수로 USER_TABLE, DEPT_TABLE을 선언해 두었습니다. currentTable은 다음 결과 ResultSet을 얻어올 때마다 1씩 증가시켜 if문을 통해 테이블마다 다른 처리를 해줍니다. ResultSet에서 데이터를 뽑아오는 것은 평소 사용하던 JDBC API를 그대로 사용하면 됩니다. rs.next()로 커서를 옮기면서 데이터를 추출합니다.

            boolean isExistRusult = callableSt.execute();
 
 
            int currentTable = 1;
            while (isExistRusult) {
                ResultSet rs = callableSt.getResultSet();
 
 
                //한 행씩 데이터 추출
                while (rs.next()) {
                    Map<String, Object> rowMap = new HashMap<>();
                    
                    if (currentTable == USER_TABLE) {
                        rowMap.put("userName", rs.getString("user_name"));                    
                        rowMap.put("userCode", rs.getString("user_code"));
                        userRows.add(rowMap);
                    } else if (currentTable == DEPT_TABLE) {
                        rowMap.put("deptName", rs.getString("dept_name"));
                        rowMap.put("deptCode", rs.getString("dept_code"));
                        deptRows.add(rowMap);
                    }
                    
                }

 

 

마지막으로 ResultSet 자원을 반납해줍니다. 그 다음 핵심은 callableSt.getMoreResults() 메서드를 호출해 isExistResult 변수에 할당해 주는 것인데, 현재 가리키는 ResultSet 이외에 ResultSet이 더 존재하는지 확인하는 것입니다. 그리고 ResultSet 분기처리를 위해 currentTable을 1 증가시켜줍니다. 이후에는 계속 반복일 뿐입니다.

                rs.close();           
                
                //ResultSet이 더 있는지 확인
                isExistRusult = callableSt.getMoreResults();                                
                currentTable++;

최종 출력 결과입니다. List<Map<String, Obejct>> 형태로 데이터를 담아서 출력했습니다.

[{userName=kim, userCode=u001}, {userName=lee, userCode=u002}, {userName=park, userCode=u003}, {userName=ryu, userCode=u004}, {userName=choi, userCode=u005}]
[{deptName=Customer Service, deptCode=a001}, {deptName=Development, deptCode=a002}, {deptName=Finance, deptCode=a003}, {deptName=Human Resources, deptCode=a004}, {deptName=Marketing, deptCode=a005}, {deptName=Production, deptCode=a006}, {deptName=Quality Management, deptCode=a007}, {deptName=Research, deptCode=a008}, {deptName=Sales, deptCode=a009}]

ResultSet 테이블명, 컬럼명 등 메타 정보 얻기

ResultSet을 처리하면서 테이블명 컬럼명이 필요한 경우에는 java.sql.ResultSetMetaData 클래스로 스키마에 대한 메타정보를 얻을 수 있습니다. 이전 예제에서 while문을 다음과 같이 수정합니다. ResultSet#getMetaData() 메서드를 통해 ResultSetMetaData  인스턴스를 얻을 수 있습니다.

            while (isExistRusult) {
                ResultSet rs = callableSt.getResultSet();
                ResultSetMetaData rsmd = rs.getMetaData();
                String tableName = "";
                
                int cols = rsmd.getColumnCount();
                for (int i = 1; i <= cols; i++) {
                    System.out.println("NAME: " + rsmd.getColumnName(i) + " " + "TYPE: " + rsmd.getColumnTypeName(i));
                    tableName = rsmd.getTableName(i);
                    System.out.println(tableName);
                }
                
                rs.close();           
                
                //ResultSet이 더 있는지 확인
                isExistRusult = callableSt.getMoreResults();
                currentTable++;
            }

출력 결과입니다. 이는 프로시저에 담긴 쿼리 작성 방법이나, DBMS에 따라 정보가 출력되지 않을 수도 있습니다.

NAME: user_name TYPE: CHAR
user
NAME: user_code TYPE: CHAR
user
NAME: dept_name TYPE: CHAR
departments
NAME: dept_code TYPE: CHAR                                                                    
departments
 

 

추가 - Spring JAVA Config 사용시

이부분은 혹시 모를 편의를 위해 추가합니다. 스프링 DataSource 빈 설정을 Java로 하는 경우 Java 설정 클래스 작성 후 main() 메서드 처음 부분만 수정합니다.

DataSourceConfig.java

package config;
 
 
import java.beans.PropertyVetoException;
 
 
import javax.sql.DataSource;
 
 
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
 
import com.mchange.v2.c3p0.ComboPooledDataSource; 
 
 
@Configuration
public class DataSourceConfig {
 
 
    @Bean(destroyMethod = "close")
    public DataSource dataSource() {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        try {
            dataSource.setDriverClass("com.mysql.jdbc.Driver");
        } catch (PropertyVetoException e) {
            throw new RuntimeException(e);
        }
        dataSource.setJdbcUrl("jdbc:mysql://192.168.111.128:3306/springdb?noAccessToProcedureBodies=true");
        dataSource.setUser("springuser");
        dataSource.setPassword("123456");
        return dataSource;
    }
 
 
 
 
}
 

 

Main.java

    public static void main(String[] args) throws IOException {
        applicationContext = new AnnotationConfigApplicationContext(DataSourceConfig.class);
        dataSource = applicationContext.getBean(ComboPooledDataSource.class);
        multipleResultSets();
    }
 

도움이 되셨다면 공감을 눌러주세요~ 감사합니다.