简单易行:Spring中操作LOB字段案例

Spring提供了org.springframework.jdbc.core.JdbcTemplate模板类，封装了一些sql常用方法使代码人员更加方便操作，更是引入了回调函数凸显了对面向对象的支持。这里主要介绍一下对Blob、Clob的解决。

1、就像普通的类声明一样，在applicationContext.xml中定义dataSource、jdbcTemplate类:

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate" abstract="false" lazy-init="false" autowire="default" dependency-check="default"> <property name="dataSource"> <ref bean="dataSource" /> </property> </bean>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="org.logicalcobwebs.proxool.ProxoolDriver" /> <property name="url" value="proxool.proxpoolssh2" /> </bean>

2、针对Blob、Clob定义一个lobHander（此处名为oracleLobHandler）

<!-- 使用spring+hibernate处理大字段 --> <!-- 处理oracle BLOB/CLOB--> <bean id="nativeJdbcExtractor" class="org.springframework.jdbc.support.nativejdbc.SimpleNativeJdbcExtractor" /> <bean id="oracleLobHandler" class="org.springframework.jdbc.support.lob.OracleLobHandler" lazy-init="true"> <property name="nativeJdbcExtractor"> <ref bean="nativeJdbcExtractor" /> </property> </bean> <!-- 如果底层数据库是 DB2、MySQL 等非 Oracle 的其它数据库，则只要简单配置一个 DefaultLobHandler 就可以了. <bean id="defaultLobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" lazy-init="true" /> --> 对于sqlserver等不必配置。

3、撰写业务代码：

public class PostJdbcDao2 {

private LobHandler lobHandler; private JdbcTemplate jdbcTemplate;

//getter、setter

...

public void addPost() { String sql = " INSERT INTO t_post(oid,post_text,post_attach)" + " VALUES(?,?,?)"; this.getJdbcTemplate().execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) { protected void setValues(PreparedStatement ps, LobCreator lobCreator) throws SQLException { ps.setString(1, "123123"); try { FileInputStream fin = null;

//非lob方式，如sql server //lobCreator.setClobAsString(ps, 2, "setClobAsString");

//lob方式，如oracle db2 mysql fin = new FileInputStream(new File("c:\WFM_Ecode.log")); lobCreator.setClobAsAsciiStream(ps, 2, fin, fin.available()); fin.close();

//非lob方式，如sql server //lobCreator.setBlobAsBytes(ps, 3, "setBlobAsBytes sdfafasf".getBytes());

//lob方式，如oracle db2 mysql fin = new FileInputStream(new File("C:\tomcat5.0.28_免安装.rar")); lobCreator.setBlobAsBinaryStream(ps, 3, fin, fin.available()); fin.close();

} catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } }); }

public List getAttachs(String userId) { String sql = "SELECT oid,post_text,post_attach FROM t_post where oid =?"; List list = null; list = getJdbcTemplate().query(sql, new Object[] { userId }, new RowMapper() { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Student stu = new Student(); String postId = rs.getString(1); stu.setId(postId); stu.setDescript(lobHandler.getClobAsString(rs, "post_text")); stu.setImage(lobHandler.getBlobAsBytes(rs, "post_attach")); return stu; } }); showList(list); return list; }

}

4、在jsp调用：

JdbcTemplate jdbcTemplate = (JdbcTemplate) WebApplicationContextUtils .getWebApplicationContext(session.getServletContext()) .getBean("jdbcTemplate"); LobHandler lobHandler = (LobHandler) WebApplicationContextUtils .getWebApplicationContext(session.getServletContext()) .getBean("oracleLobHandler"); PostJdbcDao2 d2 = new PostJdbcDao2(); d2.setLobHandler(lobHandler); d2.setJdbcTemplate(jdbcTemplate); d2.addPost();

List list = d2.getAttachs("123123");

ok，查看一下吧！