用jdbcTempate调用存储过程

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

关键字: spring

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

Java代码 

1. public intgetUserTopicNum(final intuserId) {  
2.     String sql = "{call P_GET_TOPIC_NUM(?,?)}";  
3.     //使用   Object execute(String callString, CallableStatementCallbackaction)接口  
4.     Object obj = getJdbcTemplate().execute(sql,newCallableStatementCallback(){  
5.         publicObject doInCallableStatement(CallableStatement cs)throws SQLException,DataAccessException {  
6.             cs.setInt(1,userId);  
7.             cs.registerOutParameter(2, Types.INTEGER);  
8.             cs.execute();   
9.             returnnewInteger(cs.getInt(2));  
10.         }     
11.     });   
12.     return((Integer)obj).intValue();  
13. }  

2、spring定义了一个以统一的方式操作各种数据库的Lob类型数据的LobCreator(保存的时候用),同时提供了一个LobHandler为操作二进制字段和大文本字段提供统一接口访问。
举例，例子里面的t_post表中post_text字段是CLOB类型,而post_attach是BLOG类型：

Java代码 

1. public class PostJdbcDaoextendsJdbcDaoSupport implements PostDao {  
2.     privateLobHandler lobHandler;  
3.     privateDataFieldMaxValueIncrementer incre;  
4.     publicLobHandler getLobHandler() {  
5.         returnlobHandler;   
6.     }   
7.     publicvoidsetLobHandler(LobHandler lobHandler) {  
8.         this.lobHandler= lobHandler;   
9.     }   
10.     publicvoidaddPost(final Post post){        
11.         String sql = "INSERT INTOt_post(post_id,user_id,post_text,post_attach)"  
12.                 + "VALUES(?,?,?,?)";  
13.         getJdbcTemplate().execute(  
14.                 sql,   
15.                 newAbstractLobCreatingPreparedStatementCallback(  
16.                         this.lobHandler){   
17.                     protectedvoidsetValues(PreparedStatement ps,  
18.                             LobCreator lobCreator) throws SQLException {  
19.                         ps.setInt(1, incre.nextIntValue());  
20.                         ps.setInt(2, post.getUserId());  
21.                         lobCreator.setClobAsString(ps, 3,post.getPostText());  
22.                         lobCreator.setBlobAsBytes(ps, 4,post.getPostAttach());  
23.                     }   
24.                 });   
25.     }   
26. }  

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

Java代码 

1. <bean id="nativeJdbcExtractor"  
2.     class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor"  
3.     lazy-init="true" />  
4. <bean id="oracleLobHandler"  
5.     class="org.springframework.jdbc.support.lob.OracleLobHandler"  
6.     lazy-init="true">  
7.     <property name="nativeJdbcExtractor"ref="nativeJdbcExtractor"/>  
8. </bean>  
9. <bean id="dao" abstract="true">  
10.     <property name="jdbcTemplate"ref="jdbcTemplate" />  
11. </bean>  
12. <bean id="postDao"parent="dao"  
13.     class="com.baobaotao.dao.jdbc.PostJdbcDao">  
14.     <property name="lobHandler"ref="oracleLobHandler" />  
15. </bean>  

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

Java代码 

1. <bean id="defaultLobHandler"  
2.     class="org.springframework.jdbc.support.lob.DefaultLobHandler"  
3.     lazy-init="true" />  
4. <bean id="dao" abstract="true">  
5.     <property name="jdbcTemplate"ref="jdbcTemplate" />  
6. </bean>  
7. <bean id="postDao"parent="dao"  
8.     class="com.baobaotao.dao.jdbc.PostJdbcDao">  
9.     <property name="lobHandler"ref="defaultLobHandler" />  
10. </bean>  

读取BLOB/CLOB块,举例：

Java代码 

1. public ListgetAttachs(final intuserId){  
2.     String sql = "SELECT post_id,post_attach FROM t_post where user_id =?and post_attach is not null";  
3.     returngetJdbcTemplate().query(  
4.             sql,new Object[] {userId},  
5.             newRowMapper() {  
6.                 publicObject mapRow(ResultSet rs, introwNum) throws SQLException {  
7.                     Post post = new Post();  
8.                     intpostId = rs.getInt(1);  
9.                     byte[]attach = lobHandler.getBlobAsBytes(rs, 2);  
10.                     post.setPostId(postId);  
11.                     post.setPostAttach(attach);  
12.                     returnpost;   
13.                 }   
14.             });   
15. }  

注：代码均来自<<精通spring2.x企业应用开发详解>>