java clob存储过程_用jdbcTempate调用存储过程,处理BLOBCLOB小记

用jdbcTempate调用存储过程,处理BLOB/CLOB小记

1、利用spring的jdbcTemplate调用存储过程

假如我们有P_GET_TOPIC_NUM这一个存储过程,有两个参数,第一个参数userId是传进去的,第二个参数是传出来的,举例如下:

Java代码

public int getUserTopicNum(final int userId) {

String sql = "{call P_GET_TOPIC_NUM(?,?)}";

//使用?   Object execute(String callString, CallableStatementCallback action)接口

Object obj = getJdbcTemplate().execute(sql,new CallableStatementCallback(){

public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {

cs.setInt(1,userId);

cs.registerOutParameter(2, Types.INTEGER);

cs.execute();

return new Integer(cs.getInt(2));

}

});

return ((Integer)obj).intValue();

}

2、spring定义了一个以统一的方式操作各种数据库的Lob类型数据的LobCreator(保存的时候用),同时提供了一个LobHandler为操作二进制字段和大文本字段提供统一接口访问。

举例，例子里面的t_post表中post_text字段是CLOB类型,而post_attach是BLOG类型：

Java代码

public class PostJdbcDao extends JdbcDaoSupport implements PostDao {

private LobHandler lobHandler;

private DataFieldMaxValueIncrementer incre;

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, incre.nextIntValue());

ps.setInt(2, post.getUserId());

lobCreator.setClobAsString(ps, 3, post.getPostText());

lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach());

}

});

}

}

设置相对应的配置文件(Oracle 9i版本),Oracle的数据库最喜欢搞搞特别的东西啦：

Java代码

class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"

lazy-init="true" />

class="org.springframework.jdbc.support.lob.OracleLobHandler"

lazy-init="true">

class="com.baobaotao.dao.jdbc.PostJdbcDao">

Oracle 10g或其他数据库如下设置：

Java代码

class="org.springframework.jdbc.support.lob.DefaultLobHandler"

lazy-init="true" />

class="com.baobaotao.dao.jdbc.PostJdbcDao">

读取BLOB/CLOB块,举例：

Java代码

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 {

Post post = new Post();

int postId = rs.getInt(1);

byte[] attach = lobHandler.getBlobAsBytes(rs, 2);

post.setPostId(postId);

post.setPostAttach(attach);

return post;

}

});

}

注：代码均来自<>