spring+jdbc操控LOB对象

来源：互联网发布：淘宝不显示延长收货编辑：程序博客网时间：2024/05/16 04:50

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

一.spring配置文件

<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
lazy-init="true" />

二.通过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/