简单易行: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，查看一下吧！
页:
[1]