Spring + iBatis 으로 프레임워크가 구성됐을 때 오라클의 LOB타입인 CLOB, BLOB을 이용하는 방법입니다.
테이블은 아래와 같이 구성되어 있습니다.
CREATE TABLE lob_test (
key NUMBER,
clob CLOB,
blob BLOB,
dt DATE DEFAULT SYSDATE
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE UNIQUE INDEX lob_test_pk
ON lob_test (key)
LOGGING
NOPARALLEL;
ALTER TABLE lob_test ADD (
CONSTRAINT lob_test_pk
PRIMARY KEY
(key)
USING INDEX lob_test_pk);
HTML 대충 아래와 같이 구성합니다. 이전 브라우저에서는 .attr("encoding", "multipart/form-data")이렇게 해도 되더니.. 안되서 .attr("enctype", "multipart/form-data") 추가했습니다.
<%@ page language="java" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<!DOCTYPE html>
<html lang="ko">
<head>
<meta charset="utf-8">
<title></title>
<link rel="stylesheet" href="">
<style type="text/css">
body{
font-family:"맑은 고딕", "굴림", "굴림체", "Arial", "Verdana";
font-size:12px;
}
</style>
<script type="text/javascript" src="http://ajax.microsoft.com/ajax/jquery/jquery-1.7.1.min.js" charset="utf-8"></script>
<script type="text/javascript" charset="UTF-8">
$(document).ready(function(){
$("#insertHandler").click(function(){
//alert("insert");
var form = $("form");
form
.attr("encoding", "multipart/form-data")
.attr("enctype", "multipart/form-data")
.attr("method","post")
.attr("action","<c:url value='/insert.do'/>")
.submit();
});
});
</script>
</head>
<body>
<!-- <form method="post" enctype="multipart/form-data"> -->
<form>
<div>
<h1>CLOB, BLOB TEST</h1>
</div>
<hr/>
<div>
<h2>INSERT</h2>
<textarea id="clob" name="clob" style="width:300px; height:100px;"></textarea>
<br/>
<input type="file" id="blob" name="blob" style="width:300px;" />
<br/>
<input type="button" id="insertHandler" name="insertHandler" value="insert" />
</div>
<hr/>
<div>
<h2>LIST</h2>
<table border="1" width="100%">
<tr>
<th width="5%">NO.</th>
<th width="5%">KEY</th>
<th width="20%">CLOB</th>
<th width="10%">BLOB</th>
<th width="10%">DATE</th>
<th width="35%">ORIGINAL IMAGE</th>
<th width="15%">RESIZING IMAGE</th>
</tr>
<c:forEach items="${list}" var="result" varStatus="i">
<tr>
<td>${i.count}</td>
<td>${result.key}</td>
<td>${result.clob}</td>
<td>${result.blob}</td>
<td>${result.dt}</td>
<td align="center"><img src="<c:url value='/image.do'/>?key=${result.key}&resize=false" /></td>
<td align="center"><img src="<c:url value='/image.do'/>?key=${result.key}&resize=true" /></td>
</tr>
</c:forEach>
</table>
</div>
</form>
</body>
</html>
Spring controller를 아래와 같이 구성합니다.
package com.tistory.aircook.controller;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import com.tistory.aircook.service.LobService;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.ServletRequestUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
/**
* <p>
* LobController 클래스
* </p>
* <p>
* Copyright(c) 2012 aircook.tistory.com. All rights reserved.
* </p>
*
* @author Francis Lee
* @since 2012. 2. 20.
* @version 1.0
*/
@Controller
public class LobController {
private final Log logger = LogFactory.getLog(getClass());
@Autowired
private LobService lobService;
@RequestMapping("/list.do")
public String list(HttpServletRequest request, ModelMap model) throws Exception {
Map params = new HashMap();
model.addAttribute("list", lobService.getList(params));
return "/list";
}
@RequestMapping("/insert.do")
public String insert(HttpServletRequest request, ModelMap model) throws Exception {
// 테스트코드 시작
Enumeration p = request.getParameterNames();
int z = 1;
while (p.hasMoreElements()) {
String name = (String) p.nextElement().toString();
String value = request.getParameter(name);
if (logger.isDebugEnabled()) {
logger.debug("[" + (z++) + "]" + name + " : " + value);
}
}
// 테스트코드 끝
String clob = ServletRequestUtils.getStringParameter(request, "clob", "");
MultipartHttpServletRequest multipartHttpServletRequest = (MultipartHttpServletRequest) request;
MultipartFile multipartFile = multipartHttpServletRequest.getFile("blob");
if (logger.isDebugEnabled()) {
logger.debug("multipartFile.isEmpty() : " + multipartFile.isEmpty());
logger.debug("multipartFile.getContentType() : " + multipartFile.getContentType());
logger.debug("multipartFile.getName() : " + multipartFile.getName());
logger.debug("multipartFile.getOriginalFilename() : " + multipartFile.getOriginalFilename());
logger.debug("multipartFile.getSize() : " + multipartFile.getSize());
}
Map params = new HashMap();
params.put("clob", clob);
params.put("blob", multipartFile.getBytes());
lobService.setInsert(params);
return "redirect:/list.do";
}
@RequestMapping("/image.do")
public String image(HttpServletRequest request, ModelMap model) throws Exception {
String key = ServletRequestUtils.getStringParameter(request, "key", "");
String resize = ServletRequestUtils.getStringParameter(request, "resize", "");
Map map = (HashMap) lobService.getImage(key);
byte[] bytes = (byte[]) map.get("blob");
model.addAttribute("resize", resize);
model.addAttribute("bytes", bytes);
return "blobView";
}
}
Service, Dao 클래스는 대충 알아서 만들고, ibatis mapping파일을 아래와 같이 구성합니다.
DTO 클래스를 생성안했기 때문에, resultMap을 그냥 HashMap을 이용해서 구성했습니다.
Insert 할때도 parameterMap을 구성해도 되나 귀찮아서 :CLOB :BLOB를 이용했습니다.
참고로 아래 캐시 설정중 는 flushOnExecute동작안합니다. 이유는 아직 못찾았습니다. Spring 설정시 mappingLocations를 사용하지 않고 iBatis 설정파일의 <sqlMap resource="com/tistory/aircook/dao/Lob.xml"/> 처럼 설정하면 동작하더군요.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Lob">
<!-- 캐시모델 설정 -->
<cacheModel type="MEMORY" id="defaultCache">
<flushInterval minutes="5"/>
<flushOnExecute statement="Lob.setInsert"/>
<property name="reference-type" value="SOFT" />
</cacheModel>
<typeAlias alias="caseInsensitiveMap" type="org.apache.commons.collections.map.CaseInsensitiveMap"/>
<resultMap id="listMap" class="java.util.HashMap">
<result property="key" column="key" javaType="java.lang.Integer" jdbcType="NUMBER" />
<result property="clob" column="clob" javaType="java.lang.String" jdbcType="CLOB" />
<result property="blob" column="blob" javaType="[B" jdbcType="BLOB" />
<result property="dt" column="dt" javaType="java.util.Date" jdbcType="DATE" />
</resultMap>
<!-- <select id="getList" parameterClass="java.util.HashMap" resultClass="caseInsensitiveMap"> -->
<!-- <select id="getList" parameterClass="java.util.HashMap" resultClass="java.util.HashMap"> -->
<select id="getList" parameterClass="java.util.HashMap" resultMap="listMap" cacheModel="defaultCache">
<![CDATA[
SELECT key, clob, blob, dt FROM lob_test
ORDER BY key
]]>
</select>
<resultMap id="imageMap" class="java.util.HashMap">
<result property="blob" column="blob" javaType="[B" jdbcType="BLOB" />
</resultMap>
<select id="getImage" parameterClass="java.lang.String" resultMap="imageMap" cacheModel="defaultCache">
SELECT blob FROM lob_test
WHERE key = #key#
</select>
<insert id="setInsert" parameterClass="java.util.HashMap">
INSERT INTO lob_test
(key,
clob,
blob
)
VALUES ((SELECT NVL (MAX (key), 0) + 1 FROM lob_test),
#clob:CLOB#,
#blob:BLOB#
)
</insert>
</sqlMap>
BLOB의 경우 이미지를 넣고 화면에 표현하기 위해 Controller에 Request Mapping(@RequestMapping("/image.do"))을 걸었는데 이것에 대한 View를 아래와 같이 따로 정의하였습니다.
package com.tistory.aircook.common.view;
import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.net.URLConnection;
import java.util.Map;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.tistory.aircook.common.util.ImageUtil;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.servlet.view.AbstractView;
/**
* <p>
* blob view
* </p>
* <p>
* Copyright(c) 2012 aircook.tistory.com. All rights reserved.
* </p>
*
* @author Francis Lee
* @since 2012. 4. 19.
* @version 1.0
*/
public class BlobView extends AbstractView {
/*
* public BlobView(){ setContentType(contentType); }
*/
@Override
protected void renderMergedOutputModel(Map model, HttpServletRequest request, HttpServletResponse response)
throws Exception {
// 이미지 resize
double xyRatio = 1.0;
int maxWidth = 100;
byte[] bytes = (byte[]) model.get("bytes");
boolean isResize = Boolean.parseBoolean((String) model.get("resize"));
if (bytes.length > 0) {
// byte --> InputStream 타입으로 변환
InputStream is = new ByteArrayInputStream(bytes);
String contentType = URLConnection.guessContentTypeFromStream(is);
if (logger.isDebugEnabled()) {
logger.debug("contentType : " + contentType);
}
// 이미지일때만
if (contentType.equals("image/jpeg") || contentType.equals("image/png") || contentType.equals("image/gif")) {
if (logger.isDebugEnabled()) {
logger.debug("isResize : " + isResize);
}
// 이미지를 리사이징해야된다면
if (isResize) {
bytes = ImageUtil.generateImage(bytes, maxWidth, xyRatio);
is = new ByteArrayInputStream(bytes);
}
response.setContentType(contentType);
response.setContentLength((int) bytes.length);
// OutputStream 으로 출력
ServletOutputStream os = response.getOutputStream();
FileCopyUtils.copy(is, os);
os.flush();
}
}
}
}
마지막으로 이미지 리사이징하는 클래스 인터넷 검색(http://blog.naver.com/PostView.nhn?blogId=ppant&logNo=70107766793)해서 참조(복사!)했습니다.
package com.tistory.aircook.common.util;
import java.awt.Color;
import java.awt.Graphics2D;
import java.awt.RenderingHints;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import javax.imageio.ImageIO;
public class ImageUtil {
public static byte[] generateImage(byte[] imageContent, int maxWidth, double xyRatio) throws IOException {
BufferedImage originalImg = ImageIO.read(new ByteArrayInputStream(imageContent));
// get the center point for crop
int[] centerPoint = { originalImg.getWidth() / 2, originalImg.getHeight() / 2 };
// calculate crop area
int cropWidth = originalImg.getWidth();
int cropHeight = originalImg.getHeight();
if (cropHeight > cropWidth * xyRatio) {
// long image
cropHeight = (int) (cropWidth * xyRatio);
}
else {
// wide image
cropWidth = (int) ((float) cropHeight / xyRatio);
}
// set target image size
int targetWidth = cropWidth;
int targetHeight = cropHeight;
if (targetWidth > maxWidth) {
// too big image
targetWidth = maxWidth;
targetHeight = (int) (targetWidth * xyRatio);
}
// processing image
BufferedImage targetImage = new BufferedImage(targetWidth, targetHeight, BufferedImage.TYPE_INT_RGB);
Graphics2D graphics2D = targetImage.createGraphics();
graphics2D.setBackground(Color.WHITE);
graphics2D.setPaint(Color.WHITE);
graphics2D.fillRect(0, 0, targetWidth, targetHeight);
graphics2D.setRenderingHint(RenderingHints.KEY_INTERPOLATION, RenderingHints.VALUE_INTERPOLATION_BILINEAR);
graphics2D.drawImage(originalImg, 0, 0, targetWidth, targetHeight, centerPoint[0] - (int) (cropWidth / 2),
centerPoint[1] - (int) (cropHeight / 2), centerPoint[0] + (int) (cropWidth / 2), centerPoint[1]
+ (int) (cropHeight / 2), null);
ByteArrayOutputStream output = new ByteArrayOutputStream();
ImageIO.write(targetImage, "png", output);
return output.toByteArray();
}
}
왜 이미지를 BLOB에 넣는지 이해는 안되지만.. 고객의 needs인지라… ㅎㅎㅎ
사용한 라이브러리 버전은 다음과 같습니다.
aopalliance-1.0.jar / 4,467
aspectjrt.jar / 116,219
aspectjweaver.jar / 1,749,970
cglib-nodep-2.2.jar / 322,362
commons-beanutils-1.8.3.jar / 232,019
commons-codec-1.6.jar / 232,771
commons-collections-3.2.1.jar / 575,389
commons-fileupload-1.2.2.jar / 59,590
commons-io-1.4.jar / 109,043
commons-lang-2.4.jar / 261,809
commons-logging-1.1.1.jar / 60,686
ibatis-2.3.4.726a.jar / 380,731
jstl-api-1.2.jar / 30,690
jstl-impl-1.2.jar / 392,431
log4j-1.2.16.jar / 481,534
org.springframework.aop-3.1.0.RELEASE.jar / 331,474
org.springframework.asm-3.1.0.RELEASE.jar / 53,079
org.springframework.beans-3.1.0.RELEASE.jar / 589,253
org.springframework.context-3.1.0.RELEASE.jar / 829,601
org.springframework.core-3.1.0.RELEASE.jar / 442,400
org.springframework.expression-3.1.0.RELEASE.jar / 176,283
org.springframework.jdbc-3.1.0.RELEASE.jar / 401,762
'spring' 카테고리의 다른 글
| Spring을 이용한 RESTful 서비스 2 (1) | 2012.07.11 |
|---|---|
| Spring을 이용한 RESTful 서비스 1 (3) | 2012.07.11 |
| AJAX 호출시 Exception 처리방법 (2) | 2012.01.02 |
| 스프링 2.5 스키마와 네임스페이스 (1) | 2009.09.30 |
| org.springframework.util.StringUtils Test (0) | 2008.07.09 |
