spring+jdbc操控LOB对象

spring对jdbc操控LOB也提供了简单的方式，以oracle数据库为例，方式如下：

一.spring配置文件

<!-- 提供对lob字段的支持 -->
<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
lazy-init="true" />

<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler"
lazy-init="true">
<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor" />
</bean>

<!-- 在需要的dao中加入对LOB的支持-->

<bean id="initTopicDao" class="com.netqin.bbs.initTopicData.dao.InitTopicDataDao">

<property name="dataSource" ref="dataSource" />
<property name="lobHandler" ref="oracleLobHandler" />
</bean>

 

二.通过dao实现插入和查询

这里使用SimpleJdbcDaoSupport，为了举例，这里使用POSTS_TEXT 表，它有三个属性：

POSTS_TEXT number(20)

POST_TEXT blob

POST_SUBJECT clob

InitTopicDataDao 代码如下：

package com.netqin.bbs.initTopicData.dao;

import java.io.UnsupportedEncodingException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;

import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import com.netqin.bbs.utils.Constant;

public class InitTopicDataDao extends SimpleJdbcDaoSupport{
private static Logger logger=Logger.getLogger(InitTopicDataDao.class);

// INSERT 增加BBS PostText
public static String INSERT_BBS_POST_TEXT = "INSERT INTO POSTS_TEXT T (T.POST_ID,T.POST_TEXT,T.POST_SUBJECT) VALUES(?,?,?)";

//SELECT 查询BBS PostText Content

public static String SELECT_BBS_POST_TEXT_CONTENT = "SELECT T.POST_TEXT,T.POST_SUBJECT FROM POSTS_TEXT T WHERE T.POST_ID = ? ";
private LobHandler lobHandler;

public LobHandler getLobHandler() {
return lobHandler;
}

public void setLobHandler(LobHandler lobHandler) {
this.lobHandler = lobHandler;
}

/**
* 描述 : 增加一个PostText记录. <br>
*<p>

* @param postId
* @param topicName
* @param topicContent
*/
public void addBBSPostText(final long postId, final String topicName, final String topicContent){
getJdbcTemplate().execute(InitTopicDataDao.INSERT_BBS_POST_TEXT,
new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) {
protected void setValues(PreparedStatement ps,LobCreator lobCreator)
throws SQLException {
ps.setLong(1, postId);
lobCreator.setBlobAsBytes(ps, 2, topicContent.getBytes());//设置blob，可以设置编码，如UTF-8

lobCreator.setClobAsString(ps, 3, topicName);//设置clob

}
});
logger.debug("sql==="+InitTopicDataDao.INSERT_BBS_POST_TEXT);
logger.debug("param==="+postId+","+topicContent+","+topicName);

}

/**
* 描述 : 取出postText数据. <br>
*<p>

* @param id
* @return
*/
@SuppressWarnings("unchecked")
public List getBBSPostText(long id){
List object = getJdbcTemplate().query(
InitTopicDataDao.SELECT_BBS_POST_TEXT_CONTENT,new Object[] {id},
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

byte[] attach = lobHandler.getBlobAsBytes(rs, 1); //取出blob

String name = lobHandler.getClobAsString(rs, 2);//取出clob

String[] post = new String[2];//存入时如果使用UTF-8等进行编码，这里在取出后也需要进行转码
post[0] = name;
post[1] = new String(attach);

return post;
}
});
return object;
}


}

这里在为大家推荐一篇介绍spring操控LOB的文章：

http://www.ibm.com/developerworks/cn/java/j-lo-spring-lob/