Spring 让 LOB 数据操作变得简单易行

package com.baobaotao.dao.jdbc;import java.sql.Connection;
import COM.ibm.db2.jdbc.net.DB2Connection;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.datasource.DataSourceUtils;public class PostJdbcDao extends JdbcDaoSupport implements PostDao {
public void getNativeConn(){
try {
Connection conn = DataSourceUtils.getConnection(getJdbcTemplate()
.getDataSource()); ① 使用 DataSourceUtils 从模板类中获取连接
② 使用模板类的本地 JDBC 抽取器获取本地的 Connection
conn = getJdbcTemplate().getNativeJdbcExtractor().getNativeConnection(conn);
DB2Connection db2conn = (DB2Connection) conn; ③ 这时可以强制进行类型转换了
…
} catch (Exception e) {
e.printStackTrace();
}
}
}

…
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName"
value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
① 定义 DBCP 数据源的 JDBC 本地对象抽取器
<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"
lazy-init="true" />
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource" />
② 设置抽取器
<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
</bean>
<bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

package com.baobaotao.dao.jdbc;
…
import java.sql.PreparedStatement;
import java.sql.SQLException;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;public class PostJdbcDao extends JdbcDaoSupport implements PostDao {
private LobHandler lobHandler; ① 定义 LobHandler 属性
public LobHandler getLobHandler() {
return lobHandler;
}
public void setLobHandler(LobHandler lobHandler) {
this.lobHandler = lobHandler;
}
public void addPost(final Post post) {
String sql = " INSERT INTO t_post(post_id,user_id,post_text,post_attach)"
+ " VALUES(?,?,?,?)";
getJdbcTemplate().execute(sql,
new AbstractLobCreatingPreparedStatementCallback(this.lobHandler) { ②
protected void setValues(PreparedStatement ps,LobCreator lobCreator)
throws SQLException {
ps.setInt(1, 1);
ps.setInt(2, post.getUserId());
③ 设置 CLOB 字段
lobCreator.setClobAsString(ps, 3, post.getPostText());
④ 设置 BLOB 字段
lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());
}
});
}
…
}

…
<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"/> ① 设置本地 Jdbc 对象抽取器
</bean>
<bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao">
<property name="lobHandler" ref="oracleLobHandler"/> ② 设置 LOB 处理器
</bean>

<bean id="defaultLobHandler"
class="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init="true"/>
<bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao">
<property name="lobHandler" ref=" defaultLobHandler"/>
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>

package com.baobaotao.dao.jdbc;import org.springframework.core.io.ClassPathResource;
import org.springframework.test.AbstractDependencyInjectionSpringContextTests;
import org.springframework.util.FileCopyUtils;
import com.baobaotao.dao.PostDao;
import com.baobaotao.domain.Post;
public class TestPostJdbcDaoextends AbstractDependencyInjectionSpringContextTests {
private PostDao postDao;
public void setPostDao(PostDao postDao) {
this.postDao = postDao;
}
protected String[] getConfigLocations() {
return new String[]{"classpath:applicationContext.xml"};
}
public void testAddPost() throws Throwable{
Post post = new Post();
post.setPostId(1);
post.setUserId(2);
ClassPathResource res = new ClassPathResource("temp.jpg"); ① 获取图片资源
byte[] mockImg = FileCopyUtils.copyToByteArray(res.getFile()); ② 读取图片文件的数据
post.setPostAttach(mockImg);
post.setPostText("测试帖子的内容");
postDao.addPost(post);
}
}

public List getAttachs(final int userId){
String sql = "SELECT post_id,post_attach FROM t_post “+
“where user_id =? and post_attach is not null ";
return getJdbcTemplate().query(
sql,new Object[] {userId},
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
int postId = rs.getInt(1);
① 以二进制数组方式获取 BLOB 数据。
byte[] attach = lobHandler.getBlobAsBytes(rs, 2);
Post post = new Post();
post.setPostId(postId);
post.setPostAttach(attach);
return post;
}
});
}

…
public void getAttach(final int postId,final OutputStream os){ ① 用于接收 LOB 数据的输出流
String sql = "SELECT post_attach FROM t_post WHERE post_id=? ";
getJdbcTemplate().query(
sql, new Object[] {postId},
new AbstractLobStreamingResultSetExtractor() { ② 匿名内部类
③ 处理未找到数据行的情况
protected void handleNoRowFound() throws LobRetrievalFailureException {
System.out.println("Not Found result!");
}
④ 以流的方式处理 LOB 字段
public void streamData(ResultSet rs) throws SQLException, IOException {
InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
if (is != null) {
FileCopyUtils.copy(is, os);
}
}
}
);
}

package com.baobaotao.domain;
…
import javax.persistence.Basic;
import javax.persistence.Lob;
import javax.persistence. Column;
@Entity(name = "T_POST")
public class Post implements Serializable {
…
@Lob ①-1 表示该属性是 LOB 类型的字段
@Basic(fetch = FetchType.EAGER) ①-2 不采用延迟加载机制
@Column(name = "POST_TEXT", columnDefinition = "LONGTEXT NOT NULL") ①-3 对应字段类型
private String postText; @Lob ②-1 表示该属性是 LOB 类型的字段
@Basic(fetch = FetchType. LAZY) ②-2 采用延迟加载机制
@Column(name = "POST_ATTACH", columnDefinition = "BLOB") ②-3 对应字段类型
private byte[] postAttach;
…
｝

提示

使用 Spring JDBC 时，我们除了可以按 byte[]、String 类型处理 LOB 数据外，还可以使用流的方式操作 LOB 数据，当 LOB 数据体积较大时，流操作是唯一可行的方式。可惜，Spring 并未提供以流方式操作 LOB 数据的 UserType（记得 Spring 开发组成员认为在实现上存在难度）。不过，www.atlassian.com 替 Spring 完成了这件难事，读者可以通过 这里 了解到这个满足要求的BlobInputStream 类型。

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping auto-import="true" default-lazy="false">
<class name="com.baobaotao.domain.Post" table="t_post">
<id name="postId" column="post_id">
<generator class="identity" />
</id>
<property name="userId" column="user_id"/>
<property name="postText" column="post_text"
type="org.springframework.orm.hibernate3.support.ClobStringType"/>①对应 CLOB 字段
<property name="postAttach" column="post_attach"
type="org.springframework.orm.hibernate3.support.BlobByteArrayType"/>② BLOB 字段
<property name="postTime" column="post_time" type="date" />
<many-to-one name="topic" column="topic_id" class="com.baobaotao.domain.Topic" />
</class>
</hibernate-mapping>

…
<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init="true" />
<bean id="sessionFactory"
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="lobHandler" ref="lobHandler" /> ① 设置 LOB 处理器
…
</bean>

<?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="Post">
<typeAlias alias="post" type="com.baobaotao.domain.Post"/>
<resultMap id="result" class="post">
<result property="postId" column="post_id"/>
<result property="userId" column="user_id"/>
<result property="postText" column="post_text" ① 读取 CLOB 类型数据
typeHandler="org.springframework.orm.ibatis.support.ClobStringTypeHandler"/>
<result property="postAttach" column="post_attach" ② 读取 BLOB 类型数据
typeHandler="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler"/>
</resultMap>
<select id="getPost" resultMap="result">
SELECT post_id,user_id,post_text,post_attach,post_time
FROM t_post WHERE post_id =#postId#
</select>
<insert id="addPost">
INSERT INTO t_post(user_id,post_text,post_attach,post_time)
VALUES(#userId#,
#postText,handler=org.springframework.orm.ibatis.support.ClobStringTypeHandler#, ③
#postAttach,handler=org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler#, ④
#postTime#)
</insert>
</sqlMap>

提示

为每一个 LOB 类型字段分别指定处理器并不是一个好主意，iBatis 允许在 sql-map-config.xml 配置文件中通过 <typeHandler> 标签统一定义特殊类型数据的处理器，如：

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

<bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"
lazy-init="true" />
<bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="lobHandler" ref="lobHandler" /> ①设置LobHandler
<property name="configLocation"
value="classpath:com/baobaotao/dao/ibatis/sql-map-config.xml" />
</bean>