Question

当Spring遇到了Oracle，该怎么办

Answer 1

1、读写blob

需要一个lobhander，虽然defaultlobhander可以用于大多数数据库和大多数oracle的版本，却不能用过oracle 9i。我需要一个oraclelobhander。

以下是spring中的配置。

<bean id="oracleLobHandler"
class="org.springframework.jdbc.support.lob.OracleLobHandler"
singleton="false">
<property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/>
</bean>
<bean id="nativeJdbcExtractor"
class="org.springframework.jdbc.support.nativejdbc.C3P0NativeJdbcExtractor"
lazy-init="true"/>

以下是dao中的片段

/**
* 更新指定的blob
* @param key
* @param contentStream
* @param contentLength
*/
public void updateBlog(final String key, final InputStream contentStream,
final int contentLength) {
logger.debug("update content");
try {
getJdbcTemplate().execute(
"update table set content = ? where id = ?",
new AbstractLobCreatingPreparedStatementCallback(
this.lobHandler) {
protected void setValues(PreparedStatement ps,
LobCreator lobCreator) throws SQLException {
lobCreator.setBlobAsBinaryStream(ps, 1,
contentStream, contentLength);
ps.setString(2, key);
}
});
} catch (RuntimeException re) {
logger.warn("update content fail");
throw re;
}
}

/**
* 取得指定的blob
* @param name
* @param contentStream
* @throws DataAccessException
*/
public void getBlobContent(final String id, final OutputStream
contentStream) throws DataAccessException {
getJdbcTemplate().query(
"SELECT content FROM table WHERE id=?", new String[] {id},
new AbstractLobStreamingResultSetExtractor() {
protected void handleNoRowFound() throws LobRetrievalFailureException {
throw new IncorrectResultSizeDataAccessException(
"Image with id '" + id + "' not found in database", 1, 0);
}
public void streamData(ResultSet rs) throws SQLException, IOException {
InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1);
if (is != null) {
FileCopyUtils.copy(is, contentStream);
}
}
}
);
}

前一个方法是写blob，后一个读blob。后面一个在业务方法中，这样用。

public void getBlobContent(HttpServletRequest request,
HttpServletResponse response) {
try {
serviceDisplayDAO.getBlobContent(request.getParameter("id"),response.getOutputStream());
} catch (DataAccessException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

在页面中只需要做个链接:href="/blob.do?action=getBlogContent&id='"+id+"'"

2、读写clob

这个要简单一些，不过要需要oracle的驱动不能用class12.zip那个，要从oracle下一个新的版本，具体多少不记得了。

/**
* 基于主键的查询方法 根据给出的主键查询一个业务并返回
*
* @param key
* @return
*/
public List findByPrimaryKey(String key) {
logger.debug("finding service by primary key");
try {
return getJdbcTemplate().query(
"SELECT serviceid,contenttype,templatetext FROM table where serviceid='"+key+"'",
new RowMapper() {
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
String serviceid = rs.getString(1);
String contenttype = rs.getString(2);
String templatetext = lobHandler.getClobAsString(rs, 3);
return new ServConTempBean(serviceid, contenttype, templatetext);
}
});
} catch (RuntimeException re) {
logger.warn("finding service by primary key failed", re);
throw re;
}
}

/**
* 根据业务主键更新信息
* @param key
* @param params
* @param types
* @return
*/
public void updateContentByPrimaryKey(final String key,final String templatetext) {
logger.debug("update service content by content template primary key");
try {
getJdbcTemplate().execute(
"update table set templatetext=? where serviceid=?",
new AbstractLobCreatingPreparedStatementCallback(this.lobHandler)
{protected void setValues(PreparedStatement ps,
LobCreator lobCreator) throws SQLException {
lobCreator.setClobAsString(ps, 1, templatetext);
ps.setString(2, key);
}
}
);
} catch (RuntimeException re) {
logger.warn("update service by service primary key failed", re);
throw re;
}
}

Answer 2

spring就是一个框架，本身是可以集合oracle数据库的，直接配置数据源即可。