Ibatis读写CLOB数据

Ibatis是一个高效，方便，易于学习的数据访问组件，在性能上比hibernate高，学习难度也比hibernate和jdo要低，而且它比直接使用jdbc方便和易于维护。所以Ibatis深入大家的喜爱，一些对性能有更高的要求的系统（如保险，金融行业系统），或改造遗留系统时，Ibatis是数据访问组件的首选。
在使用Oracle数据库时，读取CLOB和BLOB等大类型的数据一直是个比较犯难的事，一般都是通过JDBC代码来实现对CLOB和BLOB数据的读写，效果和性能都是最好的，但是代码也相当复杂，且代码难以重用。
在使用ibatis作为数据访问组件，也经常会遇到要读取CLOB，BLOB大类型数据。怎样使用Ibatis读取CLOB,BLOB数据也是一个难题，并且Oracle在这方面一直没有解决好。
公司的项目正好有这方面的需要，要求我给予解决。在网上找了很多的资料，都没有一个比较简单易用的解决办法，通过不断的验实，终于得出了比较好的解决办法，所以写成文字，大家可以分享。如果大家以后有这方面的需要就可以直接使用，少走弯路，当然如果大家有更好的办法，希望能告诉我，我当不胜感激。
准备工作：
1． 测试数据库

CREATE TABLE USERINFO(USERID VARCHAR2(5), 
                         USERNAME VARCHAR2(20),
                         MEMO CLOB,
						constraint PK_USERINFO  primary key(USERID));

2.域模型对象
UserInfoDTO.java

import java.io.Serializable;

public class UserInfoDTO implements Serializable {
  /*用户id*/
  private String userID;
  
  /*用户名*/
  private String userName;
  
  /*备注*/
  private String memo;

public String getMemo() {
	return memo;
}
public void setMemo(String memo) {
	this.memo = memo;
}

public String getUserID() {
	return userID;
}

public void setUserID(String userID) {
	this.userID = userID;
}

public String getUserName() {
	return userName;
}

public void setUserName(String userName) {
	this.userName = userName;
}
  
}

3.数据访问对象接口
UserInfoDao.java

public interface UserInfoDao {
 public List getUsers(UserInfoDTO user) throws DataAccessException;
 public void insertUser(UserInfoDTO user) throws DataAccessException;
}

一、 读取CLOB，BLOB类型数据的几种方法
1. jdbc实现
采用jdbc来读写是最原始，也是最直接的方法
UserInfoDaoImpl.java

public class UserInfoDaoImpl extends SqlMapClientDaoSupport implements
		UserInfoDao {
Connection con = null;
	private Connection getConnection() throws SQLException {
		Connection con = null;
		con = this.getSqlMapClientTemplate().getDataSource().getConnection();
		con.setAutoCommit(false);
		return con;
	}
public void insertUser(UserInfoDTO user) throws DataAccessException {
		try {
			this.con = this.getConnection();
			final String insertSql = "INSERT INTO USERINFO(USERID,USERName,memo) VALUES(?,?,?)";
			final String selectSql = "SELECT MEMO FROM USERINFO WHERE USERID = ? FOR UPDATE";
			final String updateSql = "UPDATE USERINFO SET MEME = ? WHERE USERID = ?";
			PreparedStatement ps = con.prepareStatement(insertSql);
			ps.setString(1, user.getUserID());
			ps.setString(2, user.getUserName());
			ps.setClob(3, CLOB.empty_lob());
			ps.executeUpdate();
			ps.close();

			ps = this.con.prepareStatement(selectSql);
			ps.setString(1, user.getUserID());
			ResultSet rs = ps.executeQuery();
			rs.next();

			CLOB memo = (CLOB) rs.getClob(1);
			memo.setString(1, user.getMemo());

			ps = this.con.prepareStatement(updateSql);
			ps.setClob(1, memo);
			ps.setString(2, user.getUserID());
			ps.executeUpdate();
			ps.close();
			this.con.commit();
		} catch (Exception ex) {
			throw new DataAccessResourceFailureException(ex.getMessage(), ex);
		} finally {
			try {
				if (this.con != null)
					this.con.close();
			} catch (Exception ex) {
				throw new DataAccessResourceFailureException(ex.getMessage(),
						ex);
			}
		}

	}

2. 使用Spring的org.springframework.jdbc.support.lob.OracleLobHandler类处理
2.1 sql-map-config.xml的配置

<typeHandler jdbcType="CLOB" javaType="java.lang.String" callback="org.springframework.orm.ibatis.support.ClobStringTypeHandler"/>

2.2 sqlMapClient的配置

<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
  <property name="configLocation"><value>/sql-map-config.xml</value></property>
  <property name="dataSource"><ref local="dataSource"/></property>
  <property name="lobHandler"><ref local="oracleLobHandler"/></property>
 </bean>

<bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler"
   lazy-init="true">

特别注意：采用这种方法只对数据源是直接连接Oracle 的JDBC驱动方式有效，如果你采用数据连接池作为数据源，则这种办法无效。但是一般大型的项目都是使用连接池的，所以这个方法有很大的局限性。


3. 通过实现Ibatis的TypeHandlerCallback接口来实现
下面是读取的方法：

public class OracleClobTypeHandlerCallback implements TypeHandlerCallback {

	public void setParameter(ParameterSetter setter, Object obj)
			throws SQLException {
		// TODO Auto-generated method stub
		CLOB clob = CLOB.empty_lob();
		clob.setString(1, (String)obj);
		setter.setClob(clob);

	}

	public Object getResult(ResultGetter getter) throws SQLException {
		
		CLOB clob = (CLOB) getter.getClob();
	
		return (clob == null || clob.length() == 0 )? null :clob.getSubString((long)1, (int)clob.length());
		
	
	}

	public Object valueOf(String param) {
		// TODO Auto-generated method stub
		return null;
	}

}

sqlMap 的配置

  <resultMap id="userResult" class="com.prs.application.ehld.sample.common.dto.UserInfoDTO">
  	<result property="userID" column="USERID" columnIndex="1"/>
  	<result property="userName" column="USERNAME" columnIndex="2"/>
  	<result property="memo"  column="memo" jdbcType="CLOB" javaType = "java.lang.String"  typeHandler =" OracleClobTypeHandlerCallback "/>
</resultMap>

二、 存在的问题
上面三种方法都存在的问题:
1. 使用jdbc来实现，就失去了使用ibatis带来的便利，同时也失去了自动事务管理的能力，还有需要自己去手工管理连接对象。代码量也相当复杂。
2. 使用spring提供的处理器，不支持采用数据连接池的数据源，有很大的局限性。
3. 通过实现ibatis的回调接口来实现，也有一定的局限性，需要新增一个类，配置也不方便，还可能会出错。

难道ibatis读写LOB大类型数据就没辙了吗？通过试验，发现可以配置ParameterMap和ResultMap就可以方便的实现对LOB的读写了，而且不用去实现任何类。只需要配置就可以了

三、 通过配置ParameterMap和ResultMap来实现对LOB类型的读写
3.1 sqlMap的配置

<resultMap id="userResult" class="com.prs.application.ehld.sample.common.dto.UserInfoDTO">
  	<result property="userID" column="USERID" columnIndex="1"/>
  	<result property="userName" column="USERNAME" columnIndex="2"/>
  	[color=darkred]<result property="memo"  column="memo" jdbcType="CLOB" javaType = "java.lang.String" />[/color]</resultMap>

  <parameterMap id="userPara" class = "com.prs.application.ehld.sample.common.dto.UserInfoDTO">
  	<parameter property="userID" jdbcType="VARCHAR" javaType ="java.lang.String"/>
  	<parameter property="userName" jdbcType="VARCHAR" javaType ="java.lang.String"/>
  	[color=darkred]<parameter property="memo" jdbcType="CLOB" javaType ="java.lang.String"/>[/color]  	
  </parameterMap>

3.2 插入和读取语句的配置

  <select id="getUserInfoList" resultMap="userResult" >
      SELECT
          USERINFO.USERID ,
          USERINFO.USERName ,
          USERINFO.MEMO
 		FROM USERINFO
</select>


   <insert id="insertUserInfo"  parameterMap = "userPara"  >
  	INSERT INTO USERINFO(USERID,
  						 USERName,
  						 memo) 
  				VALUES([color=darkred]?,?,?[/color]
  					   )
  </insert>

注意：因为使用了ParameterMap作为输入参数，在插入语句中用?号来代替属性值(如：#userId#)

而不是常见的：

INSERT INTO USERINFO(USERID,
  						 USERName,
  						 memo) 
  				VALUES(#userId#,
							#userName#,
							#memo#
  					   )

但是当paramaterMap的class属性是java.util.Map类时，应该使用#userId#类似的参数，不能用?来代替。
但是这又有一个问题，就是插入的字符串不能超过4000个字符，而CLOB类型的字段可以存4GB大小的字符。只要对userInfo对象的memo成员设置字符串超过了4000个字符，就提示“不能创建更多的套接字”,为什么会报这个错，暂是没有弄清楚。

而需求是要CLOB字段要存6000个汉字，相当于12000个英文字符。可以说是白忙了一场，没有达到目的。

四、 使用Oracle 10g的jdbc驱动程序
因为字符串只要超过了4000个字符就不能插入，所以不得不试着换一下驱动程序看看。一直以来都认为oracel的jdbc 驱动对处理LOB对象有一些问题，想看看10g出来后是不是有所改变。于是上网下载10g的驱动，一阵痛苦的等待后，问题解决，我把字符串设为12万个字符也没有问题了。

另外采用10g 的驱动就算不使用parameterMap也可以成功的插入字符串到CLOB类型字段去，请要注意的是，这样只能插入的字符一定要小于32767个。也就是说我把memo 属性设置多于32766个字符，照样插不进去。这个原因主要是jdbc驱动限制了String的长度的原因。

<insert id="insertUserInfo"  parameterClass = "UserInfoDTO"  >
  	INSERT INTO USERINFO(USERID,
  						 USERName,
  						 memo) 
  				VALUES(#userID#,
						#userName#,
						#memo#
  					   )
</insert>

使用10g的驱动，这样能写入32766个字符

五、 怎么读写BLOB
上面都一直在说CLOB，其实把CLOB实现了，那么BLOB也同样简单，只是注意它的java类型，如果一个字段为BLOB类型，那么在parameterMap中jdbcType 为BLOB,
把javaType设为：[]byte就可以了。
例：

  <parameterMap id="userPara" class = "com.prs.application.ehld.sample.common.dto.UserInfoDTO">
  	<parameter property="userID" jdbcType="VARCHAR" javaType ="java.lang.String"/>
  	<parameter property="userName" jdbcType="VARCHAR" javaType ="java.lang.String"/>
  	[color=red]<parameter property="memo" jdbcType="BLOB" javaType ="[]byte"/>[/color]
  	
  </parameterMap>

假设memo在数据库中为BLOB 类型

那么在javaBean中memo的java类型为[]byte


六、 总结
采用10g的驱动，和通过配置parameterMap和resultMap能够轻松和完美的解决LOB大型数据的读写，无需要编写新的java来实现，也没有局限性。

评论

22 楼 di1984HIT 2014-03-04  

文章不错，学习了

21 楼 rj0801zxh 2011-11-08  

写得非常好，我刚刚解决了一个读clob的问题，谢谢，你的地址我copy了，你可别再改变地址了哦。

20 楼 yiqingfeng 2007-10-21  

用新的驱动就啥问题没有了

19 楼 fight_bird 2007-10-21  

复杂问题要简单化：
无需升级Oracle驱动，只要保证ibatis版本是2.0.x（具体版本记不清了）以上，用ibatis自己的解决方式，9i、10g均无问题，项目正在运行，8应该也无问题，源码看过，为发现不能适用于8的，但未测试。

SqlMapConfig:

    <typeHandler javaType="object" callback="com.ibatis.sqlmap.engine.type.BlobTypeHandlerCallback" jdbcType="BLOB"/>
    <typeHandler javaType="string" callback="com.ibatis.sqlmap.engine.type.ClobTypeHandlerCallback" jdbcType="CLOB"/>

resultMap:

        <result property="attachment" column="attachment" javaType="object" jdbcType="BLOB"/>

JavaBean字段:

    private byte[] attachment;

CLOB的示例不贴了，和对待普通VARCHAR2到String的映射一样，绝对无4000限制，因为后端实现是字符流处理。

若是超大数据量的BLOB存储（9i以上版本应使用BFILE字段类型），需包装默认类，将字段类型直接映射为java.sql.BLOB，基于流直接读取，和jdbc一样处理就ok（已实践）。

18 楼 oliver1212 2007-09-10  

我用你的方法试了一下,不知道为什么我启动服务器时,服务器会报错时说不支持[]byte. 当我不使用parametermap时,插入数据时会报错" 数据大小超出此类型的最大值: 105542"  .       另外我也尝试采用你一开始说的三种传统方法的最后一种时,他又会报"ORA-24813: 不能发送或接收不受支持的 LOB"   . 我真的好郁闷!! 吧 . 我用的是oracle10g,  驱动用的classes12.jar.

17 楼 javabill23123 2007-09-07  

为什么我成功插入了数据库
取出来时，为什么总是空的，取不出来呢

16 楼 wmingluo 2007-07-19  

很不错，谢谢楼主分享

15 楼 klyuan 2007-05-08  

宏基小键盘 写道

今天居然搜索到这个：
http://javasky.bloghome.cn/posts/36215

有待测试

以下为原文
============================================================
由于工作需要, 要用到clob来存储, 并且使用的是ibatis做dao. 原来的配置是thin驱动, 结果试了n次, 始终得到如下的错误:
java.sql.SQLException: Io 异常: Software caused connection abort: socket write error

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)

    at oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java:3678)

    at oracle.jdbc.ttc7.TTC7Protocol.rollback(TTC7Protocol.java:505)

    at oracle.jdbc.driver.OracleConnection.rollback(OracleConnection.java:1444)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:945)

    at $Proxy0.rollback(Unknown Source)



查了很多资料, 也在中加入了, 结果还是不好用, 错误依旧...偶然间在http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+use+a+BLOB+or+CLOB 这里发现 "The example below was done for Oracle but should work for any database with a well written JDBC driver. Make sure that you do not use the thin driver supplied from Oracle. You need to use the latest ojbc14.jar."  不准用thin driver? 灵机一动, 马上换成oci driver, 惊奇的事情发生了, 插入成功了!!

呵呵, 原来是thin driver在作怪啊

那还是驱动在作怪啊！！！呵呵

14 楼 klyuan 2007-05-08  

宏基小键盘 写道

请教楼上几个问题：
一、你的oracle驱动的版本。我用的是最新的ojdbc14-10.2.0.3.jar
二、clob字段可以设置大小？
三、我觉得写parameterMap非常不爽，因为是需要另外的配置，还有要一一对应，不如用inline parameterMap清爽一些。不知道有没有更好的解决方案

oracle驱动的版本，我记不清了，当时没有记录下来。应该10g的都可以吧
clob字段可以设置大小？这个是可以的
为什么不写parameterMap?虽然会麻烦一点，但是使用clob字段的表应该不会太多吧！！！

13 楼 宏基小键盘 2007-04-30  

今天居然搜索到这个：
http://javasky.bloghome.cn/posts/36215

有待测试

以下为原文
============================================================
由于工作需要, 要用到clob来存储, 并且使用的是ibatis做dao. 原来的配置是thin驱动, 结果试了n次, 始终得到如下的错误:
java.sql.SQLException: Io 异常: Software caused connection abort: socket write error

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)

    at oracle.jdbc.ttc7.TTC7Protocol.handleIOException(TTC7Protocol.java:3678)

    at oracle.jdbc.ttc7.TTC7Protocol.rollback(TTC7Protocol.java:505)

    at oracle.jdbc.driver.OracleConnection.rollback(OracleConnection.java:1444)

    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

    at java.lang.reflect.Method.invoke(Unknown Source)

    at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:945)

    at $Proxy0.rollback(Unknown Source)



查了很多资料, 也在中加入了, 结果还是不好用, 错误依旧...偶然间在http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+use+a+BLOB+or+CLOB 这里发现 "The example below was done for Oracle but should work for any database with a well written JDBC driver. Make sure that you do not use the thin driver supplied from Oracle. You need to use the latest ojbc14.jar."  不准用thin driver? 灵机一动, 马上换成oci driver, 惊奇的事情发生了, 插入成功了!!

呵呵, 原来是thin driver在作怪啊

12 楼 ywg_2008 2007-04-30  

非常好的文章。谢谢分享

11 楼 spiritfrog 2007-04-29  

不错，分析的很全面的文章

10 楼 宏基小键盘 2007-04-29  

请教楼上几个问题：
一、你的oracle驱动的版本。我用的是最新的ojdbc14-10.2.0.3.jar
二、clob字段可以设置大小？
三、我觉得写parameterMap非常不爽，因为是需要另外的配置，还有要一一对应，不如用inline parameterMap清爽一些。不知道有没有更好的解决方案

9 楼 klyuan 2007-04-29  

没有条件试啦

8 楼 mmwy 2007-04-28  

有没有在oracle8i上试过？

7 楼 klyuan 2007-04-28  

我看了官方的解决方案，仍然可能需要10g以上的驱动！！
呵

6 楼 leisure 2007-04-27  

不错，正碰到类似的问题.
官方站点的解决
http://opensource.atlassian.com/confluence/oss/display/IBATIS/How+do+I+use+a+BLOB+or+CLOB

5 楼 klyuan 2007-04-26  

原来这样啊，不知道啦，呵呵

4 楼 宏基小键盘 2007-04-26  

文是好文，分析全面透彻。不过如果使用oracle 10g和相应的驱动，iBATIS的版本更新到2.0.9，所有的问题都不是问题了。

As of release 2.0.9 the iBatis framework has the default CLOB and BLOB type handlers included

Make sure that you do not use the thin driver supplied from Oracle. You need to use the latest ojbc14.jar.

spring的那个处理局限性还真不清楚，因为根本不用。

最后感谢作者分享，受益良多。

3 楼 BIGN 2007-04-25  

支持 学习