336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.


안녕하세요

PROCEDURE CURSOR 사용입니다.


ORACLE 프로시저구문

1
2
3
4
5
6
7
8
9
10
11
12
create or replace PROCEDURE pro_testTableSel(
varNameCol IN VARCHAR2,
c_result OUT SYS_REFCURSOR)
IS
 
BEGIN
OPEN c_result FOR
    SELECT idCol, nameCol, valueCol
    FROM testTable
    WHERE nameCol = varNameCol;
 
END pro_testTableSel;
cs


home.jsp

1
2
3
4
5
프로시저 실행 <br/>
<form name="procedureForm" id="procedureForm" method="POST" action="./springProcedure.do">
    <input type="text" value="글쓴이" id="nameCol" name="nameCol"/>
     <input type="submit" value="프로시저 호출" />
</form>
cs


HomeController.java

1
2
3
4
5
6
7
8
9
10
11
@RequestMapping(value = "/springProcedure.do", method = RequestMethod.POST)
    public void springProcedure(HttpServletRequest request){
        
        //request Map은 수정이 불가능해서 따로 담아서 Procedure 처리
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("NAMECOL", request.getParameter("nameCol"));
        
        logger.info("map check : "+map.toString());
        
        homeService.getProcedureSel(map);
    }
cs


HomeService.java

1
public void getProcedureSel(Map<String, Object> map);
cs


HomeServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Override
    public void getProcedureSel(Map<String, Object> map) {
        map.put("resultList", new ArrayList<HashMap<?,?>>());
        homeMapper.testDbProcedure(map);
        
        //가져온 사이즈 체크
        int size = map.get("resultList").toString().length();
        
        //가져온값
        List<?> list = (List<?>) map.get("resultList");
        
        //출력
        for(int i = 0; i < size - 1; i++){
            logger.info("cursor list Check ServiceImpl : "+list.get(i));
        }
        
    }
cs


HomeMapper.java

1
public void testDbProcedure(Map<String, Object> map);
cs


testMapper.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<!-- cursor 값 내용물 -->
<resultMap type="java.util.Map" id="resultMap">
    <result property="idCol" column="idCol" javaType="integer"/>
    <result property="nameCol" column="nameCol" javaType="string"/>
    <result property="valueCol" column="valueCol" javaType="string"/>
</resultMap>
 
<!-- 프로시저 (NUMBER -> NUMERIC 문자열(VARCHAR)), resultMap = 상위 id -->
    <select id="testDbProcedure" parameterType="java.util.Map" statementType="CALLABLE">
        CALL pro_testTableSel(
        #{NAMECOL, mode=IN, jdbcType=VARCHAR},
        #{resultList, mode=OUT, jdbcType=CURSOR, javaType=java.sql.ResultSet, resultMap=resultMap}
        )
    </select>
cs


결과


감사합니다.

저작자표시

'SPRING' 카테고리의 다른 글

SPRING PROCEDURE 사용하기 1  (0) 2019.04.01
SPIRNG EXCEL UPLOAD 하기  (1) 2019.04.01
SPRING FILE DOWNLOAD하기  (0) 2019.03.28
Spring FileUpload 하기  (0) 2019.03.28
Spring Excel down하기  (0) 2019.03.28
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

안녕하세요.

 

참고 프로시저

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE PROCEDURE pro_testTableSel(
varIdCol IN OUT NUMBER,
varNameCol OUT VARCHAR2,
varValueCol OUT VARCHAR2)
IS
 
BEGIN
    SELECT idCol, nameCol, valueCol
    INTO varIdCol, varNameCol, varValueCol
    FROM testTable
    WHERE idCOl = varIdCol;
 
END pro_testTableSel;
 cs


 


home.jsp

1
2
3
4
5
프로시저 실행 <br/>
<form name="procedureForm" id="procedureForm" method="POST" action="./springProcedure.do">
    <input type="text" value="2" id="idCol" name="idCol"/>
     <input type="submit" value="프로시저 호출" />
</form>
 cs


HomeController.java

1
2
3
4
5
6
7
8
9
10
11
@RequestMapping(value = "/springProcedure.do", method = RequestMethod.POST)
    public void springProcedure(HttpServletRequest request){
        
        //request Map은 수정이 불가능해서 따로 담아서 Procedure 처리
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("IDCOL", Integer.parseInt(request.getParameter("idCol")));
        
        logger.info("map check : "+map.toString());
        
        homeService.getProcedureSel(map);
    }
 cs


HomeService.java

1
public void getProcedureSel(Map<String, Object> map);
 cs


HomeServiceImpl.java

1
2
3
4
5
6
@Override
    public void getProcedureSel(Map<String, Object> map) {
        homeMapper.testDbProcedure(map);
        
        logger.info("map Check ServiceImpl : "+map.toString());
    }
 cs


HomeMapper.java

1
public void testDbProcedure(Map<String, Object> map);
 cs


testMapper.xml

1
2
3
4
5
6
7
8
<!-- 프로시저 (NUMBER -> NUMERIC 문자열(VARCHAR)) -->
    <select id="testDbProcedure" parameterType="java.util.Map" statementType="CALLABLE">
        CALL pro_testTableSel(
        #{IDCOL, mode=INOUT, jdbcType=NUMERIC, javaType=Integer},
        #{NAMECOL, mode=OUT, jdbcType=VARCHAR, javaType=String},
        #{VALUECOL, mode=OUT, jdbcType=VARCHAR, javaType=String}
        )
    </select>
 cs

 

결과



감사합니다.

저작자표시

'SPRING' 카테고리의 다른 글

SPRING PROCEDURE 사용하기 2  (0) 2019.04.01
SPIRNG EXCEL UPLOAD 하기  (1) 2019.04.01
SPRING FILE DOWNLOAD하기  (0) 2019.03.28
Spring FileUpload 하기  (0) 2019.03.28
Spring Excel down하기  (0) 2019.03.28
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.

안녕하세요

EXCEL UPLOAD입니다.

 

pom.xml

1
2
3
4
5
6
7
8
9
10
11
12
<!-- excel 처리 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>
 cs

 

home.jsp

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
$("#excelUpForm").change(function(){
                var form = $("#excelUpForm")[0];
 
                var data = new FormData(form);
                $.ajax({
                   enctype:"multipart/form-data",
                   method:"POST",
                   url: './excelUp.do',
                   processData: false,   
                   contentType: false,
                   cache: false,
                   data: data,
                   success: function(result){  
                       alert("업로드 성공!!");
                   }
                });
            });
 
 
 
엑셀업로드 : <br/>
<form name="excelUpForm" id="excelUpForm" enctype="multipart/form-data" method="POST" action="./excelDown.do">
    <input type="file" id="excelFile" name="excleFile" value="엑셀 업로드" />
</form>
 cs

 

ExcelController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
@RequestMapping(value = "/excelUp.do", method = RequestMethod.POST)
    public void ExcelUp(HttpServletRequest req, HttpServletResponse rep){
        logger.info("@@@@@@@@@@@@@@@ExcelUp START@@@@@@@@@@@@@@@");
 
        Map returnObject = new HashMap(); 
        
        try { // MultipartHttpServletRequest 생성 
            MultipartHttpServletRequest mhsr = (MultipartHttpServletRequest) req; 
            Iterator iter = mhsr.getFileNames(); 
            MultipartFile mfile = null; 
            String fieldName = ""; 
            
            // 값이 나올때까지
            while (iter.hasNext()) { 
                fieldName = iter.next().toString(); // 내용을 가져와서 
                mfile = mhsr.getFile(fieldName); 
                String origName; 
                origName = new String(mfile.getOriginalFilename().getBytes("8859_1"), "UTF-8"); //한글꺠짐 방지 // 파일명이 없다면 
                
                returnObject.put("params", mhsr.getParameterMap()); 
                
                
                //위치 및 파일
                homeService.getExcelUpload("D:\\"+origName);
            }
            
            } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block 
                e.printStackTrace(); 
            }catch (IllegalStateException e) { // TODO Auto-generated catch block 
                e.printStackTrace(); 
            } catch (IOException e) { // TODO Auto-generated catch block 
                e.printStackTrace(); 
            }
 
        
        logger.info("@@@@@@@@@@@@@@@ExcelUp END@@@@@@@@@@@@@@@");
        
    }
 cs

 

ExcelUtil.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
public static Workbook getWorkbook(String filePath) {
 
        /*
         * FileInputStream은 파일의 경로에 있는 파일을 읽어서 Byte로 가져온다.
         * 
         * 파일이 존재하지 않는다면은 RuntimeException이 발생된다.
         */
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
 
        Workbook wb = null;
 
        /*
         * 파일의 확장자를 체크해서 .XLS 라면 HSSFWorkbook에 .XLSX라면 XSSFWorkbook에 각각 초기화 한다.
         */
        if (filePath.toUpperCase().endsWith(".XLS")) {
            try {
                wb = new HSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        } else if (filePath.toUpperCase().endsWith(".XLSX")) {
            try {
                wb = new XSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
 
        return wb;
 
    }
 
    //Excel Upload시에 데이터를 얻어옵니다.
    @SuppressWarnings("deprecation")
    public static String cellValue(Cell cell) {
 
        String value = null;
        if (cell == null)
            value = "";
        else {
            switch (cell.getCellType()) { // cell 타입에 따른 데이타 저장
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // you should change this to your application date format
                    SimpleDateFormat objSimpleDateFormat = new SimpleDateFormat(
                            "yyyy-MM-dd");
                    value = ""
                            + objSimpleDateFormat.format(cell
                                    .getDateCellValue());
                } else {
                    value = ""
                            + String.format("%.0f",
                                    new Double(cell.getNumericCellValue()));
                }
                break;
            case Cell.CELL_TYPE_STRING:
                value = "" + cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BLANK:
                // value=""+cell.getBooleanCellValue();
                value = "";
                break;
            case Cell.CELL_TYPE_ERROR:
                value = "" + cell.getErrorCellValue();
                break;
            default:
            }
        }
 
        return value.trim();
    }
 cs

 

HomeService.java

1
public List<?> getExcelUpload(String excelFile);
 cs

 

 

HomeServiceImpl.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
public List<?> getExcelUpload(String excelFile){
        
        logger.info("@@@@@@@@@@@@@@@getExcelUpload START@@@@@@@@@@@@@@@ "+excelFile);
        
        Map<String, Object> map = new HashMap<String, Object>();
        List<?> list = null;
        
        try {
//            Workbook wbs = WorkbookFactory.create(new FileInputStream(excelFile));
            Workbook wbs = ExcelUtil.getWorkbook(excelFile);
            
            Sheet sheet = (Sheet) wbs.getSheetAt(0);
 
            //excel file 두번쨰줄부터 시작
            for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++) {
                
                logger.info("@@@@@@@@map @@@@@@@@@@@@@@@@ i : "+i);
                
                Row row = sheet.getRow(i);
                
                //map.put("IDCOL", ""+ExcelUtil.cellValue(row.getCell(0)));
                map.put("NAMECOL", ""+ExcelUtil.cellValue(row.getCell(1)));
                map.put("VALUECOL", ""+ExcelUtil.cellValue(row.getCell(2)));
                
                //신규삽입
                homeMapper.insertDB(map);
            }
 
            logger.info("@@@@@@@@map @@@@@@@@@@@@@@@@"+map.toString());
            //데이터가져옵니다.
            list = homeMapper.testDbList(map);
            
        }catch(Exception e){
            logger.error("error : "+e.getMessage());
            logger.error("error : "+e);
        }
        
        logger.info("@@@@@@@@@@@@@@@getExcelUpload END@@@@@@@@@@@@@@@");
        return list;
        
    }
 cs

HomeMapper.java

1
public int insertDB(Map<String, Object> map);
 cs

 

testMapper.xml

1
2
3
4
5
<!-- 데이터 삽입 -->
    <insert id="insertDB" parameterType="java.util.Map">
        INSERT INTO testTable(IDCOL, NAMECOL, VALUECOL)
        VALUES(seq_id.nextval, #{NAMECOL}, #{VALUECOL})
    </insert>
 cs

 

 

감사합니다.

저작자표시

'SPRING' 카테고리의 다른 글

SPRING PROCEDURE 사용하기 2  (0) 2019.04.01
SPRING PROCEDURE 사용하기 1  (0) 2019.04.01
SPRING FILE DOWNLOAD하기  (0) 2019.03.28
Spring FileUpload 하기  (0) 2019.03.28
Spring Excel down하기  (0) 2019.03.28

+ Recent posts

티스토리툴바