±êÌ⣺SpringÈÃLOBÊý¾Ý²Ù×÷±äµÃ¼òµ¥Ò×ÐÐ

package com.baobaotao.dao.jdbc; 

import java.sql.Connection; 
import COM.ibm.db2.jdbc.net.DB2Connection; 
import org.springframework.jdbc.core.support.JdbcDaoSupport; 
import org.springframework.jdbc.datasource.DataSourceUtils; 

public class PostJdbcDao extends JdbcDaoSupport implements PostDao { 
  public void getNativeConn(){ 
    try { 
      Connection conn = DataSourceUtils.getConnection(getJdbcTemplate() 
          .getDataSource()); ¢Ù ʹÓà DataSourceUtils ´ÓÄ£°åÀàÖлñÈ¡Á¬½Ó 
      ¢Ú ʹÓÃÄ£°åÀàµÄ±¾µØ JDBC ³éÈ¡Æ÷»ñÈ¡±¾µØµÄ Connection 
      conn = getJdbcTemplate().getNativeJdbcExtractor().getNativeConnection(conn); 
      DB2Connection db2conn = (DB2Connection) conn; ¢Û Õâʱ¿ÉÒÔÇ¿ÖƽøÐÐÀàÐÍת»»ÁË 
      ¡­ 
    } catch (Exception e) { 
      e.printStackTrace(); 
    }     
  } 
}  

¡­ 
£¼bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"  
 destroy-method="close"£¾ 
    £¼property name="driverClassName" 
      value="${jdbc.driverClassName}" /£¾ 
    £¼property name="url" value="${jdbc.url}" /£¾ 
    £¼property name="username" value="${jdbc.username}" /£¾ 
    £¼property name="password" value="${jdbc.password}" /£¾ 
£¼/bean£¾  
¢Ù ¶¨Òå DBCP Êý¾ÝÔ´µÄ JDBC ±¾µØ¶ÔÏó³éÈ¡Æ÷ 
£¼bean id="nativeJdbcExtractor"  
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" 
lazy-init="true" /£¾  
£¼bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"£¾ 
    £¼property name="dataSource" ref="dataSource" /£¾ 
    ¢Ú ÉèÖóéÈ¡Æ÷ 
    £¼property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/£¾  
£¼/bean£¾ 
£¼bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao"£¾ 
    £¼property name="jdbcTemplate" ref="jdbcTemplate" /£¾ 
£¼/bean£¾ 

package com.baobaotao.dao.jdbc; 
¡­ 
import java.sql.PreparedStatement; 
import java.sql.SQLException; 
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback; 
import org.springframework.jdbc.support.lob.LobCreator; 
import org.springframework.jdbc.support.lob.LobHandler; 

public class PostJdbcDao extends JdbcDaoSupport implements PostDao { 
  private LobHandler lobHandler; ¢Ù ¶¨Òå LobHandler ÊôÐÔ 
  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, 1);   
            ps.setInt(2, post.getUserId());   
            ¢Û ÉèÖà CLOB ×ֶΠ
            lobCreator.setClobAsString(ps, 3, post.getPostText()); 
            ¢Ü ÉèÖà BLOB ×ֶΠ
            lobCreator.setBlobAsBytes(ps, 4, post.getPostAttach()); 
          } 
        }); 
  } 
¡­ 
} 

¡­ 
£¼bean id="nativeJdbcExtractor"  
class="org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor" 
lazy-init="true"/£¾ 
£¼bean id="oracleLobHandler"  
class="org.springframework.jdbc.support.lob.OracleLobHandler"  
lazy-init="true"£¾ 
   £¼property name="nativeJdbcExtractor" ref="nativeJdbcExtractor"/£¾ ¢Ù ÉèÖñ¾µØ Jdbc ¶ÔÏó³éÈ¡Æ÷ 
£¼/bean£¾ 
£¼bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao"£¾ 
   £¼property name="lobHandler" ref="oracleLobHandler"/£¾ ¢Ú ÉèÖà LOB ´¦ÀíÆ÷ 
£¼/bean£¾ 

£¼bean id="defaultLobHandler"  
class="org.springframework.jdbc.support.lob.DefaultLobHandler"  
lazy-init="true"/£¾ 
£¼bean id="postDao" class="com.baobaotao.dao.jdbc.PostJdbcDao"£¾ 
   £¼property name="lobHandler" ref=" defaultLobHandler"/£¾  
£¼property name="jdbcTemplate" ref="jdbcTemplate" /£¾ 
£¼/bean£¾ 
 

package com.baobaotao.dao.jdbc; 

import org.springframework.core.io.ClassPathResource; 
import org.springframework.test.AbstractDependencyInjectionSpringContextTests; 
import org.springframework.util.FileCopyUtils; 
import com.baobaotao.dao.PostDao; 
import com.baobaotao.domain.Post; 
public class TestPostJdbcDaoextends AbstractDependencyInjectionSpringContextTests { 
  private PostDao postDao; 
  public void setPostDao(PostDao postDao) { 
    this.postDao = postDao; 
  } 
  protected String[] getConfigLocations() { 
    return new String[]{"classpath:applicationContext.xml"}; 
  } 
  public void testAddPost() throws Throwable{ 
    Post post = new Post(); 
    post.setPostId(1); 
    post.setUserId(2); 
    ClassPathResource res = new ClassPathResource("temp.jpg"); ¢Ù »ñȡͼƬ×ÊÔ´ 
    byte[] mockImg = FileCopyUtils.copyToByteArray(res.getFile());  ¢Ú ¶ÁȡͼƬÎļþµÄÊý¾Ý 
    post.setPostAttach(mockImg); 
    post.setPostText("²âÊÔÌû×ÓµÄÄÚÈÝ"); 
    postDao.addPost(post); 
  } 
} 

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 { 
            int postId = rs.getInt(1); 
            ¢Ù ÒÔ¶þ½øÖÆÊý×鷽ʽ»ñÈ¡ BLOB Êý¾Ý¡£ 
            byte[] attach = lobHandler.getBlobAsBytes(rs, 2);  
            Post post = new Post(); 
            post.setPostId(postId); 
            post.setPostAttach(attach); 
            return post; 
          } 
        }); 
} 

¡­ 
public void getAttach(final int postId,final OutputStream os){ ¢Ù ÓÃÓÚ½ÓÊÕ LOB Êý¾ÝµÄÊä³öÁ÷ 
  String sql = "SELECT post_attach FROM t_post WHERE post_id=? "; 
  getJdbcTemplate().query( 
      sql, new Object[] {postId}, 
      new AbstractLobStreamingResultSetExtractor() { ¢Ú ÄäÃûÄÚ²¿Àà 
¢Û ´¦ÀíδÕÒµ½Êý¾ÝÐеÄÇé¿ö 
protected void handleNoRowFound() throws LobRetrievalFailureException { 
          System.out.println("Not Found result!"); 
        } 
        ¢Ü ÒÔÁ÷µÄ·½Ê½´¦Àí LOB ×ֶΠ
        public void streamData(ResultSet rs) throws SQLException, IOException { 
          InputStream is = lobHandler.getBlobAsBinaryStream(rs, 1); 
          if (is != null) { 
            FileCopyUtils.copy(is, os); 
          } 
        } 
      } 
  ); 
} 

package com.baobaotao.domain; 
¡­ 
import javax.persistence.Basic; 
import javax.persistence.Lob; 
import javax.persistence. Column; 
@Entity(name = "T_POST") 
public class Post implements Serializable { 
  ¡­ 
  @Lob ¢Ù-1 ±íʾ¸ÃÊôÐÔÊÇ LOB ÀàÐ͵Ä×ֶΠ
  @Basic(fetch = FetchType.EAGER) ¢Ù-2 ²»²ÉÓÃÑÓ³Ù¼ÓÔØ»úÖÆ 
  @Column(name = "POST_TEXT", columnDefinition = "LONGTEXT NOT NULL") ¢Ù-3 ¶ÔÓ¦×Ö¶ÎÀàÐÍ 
  private String postText; 

  @Lob ¢Ú-1 ±íʾ¸ÃÊôÐÔÊÇ LOB ÀàÐ͵Ä×ֶΠ
  @Basic(fetch = FetchType. LAZY) ¢Ú-2 ²ÉÓÃÑÓ³Ù¼ÓÔØ»úÖÆ 
  @Column(name = "POST_ATTACH", columnDefinition = "BLOB") ¢Ú-3 ¶ÔÓ¦×Ö¶ÎÀàÐÍ 
  private byte[] postAttach; 
¡­ 
£ý 

£¼?xml version="1.0" encoding="UTF-8"?£¾ 
£¼!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"£¾ 
£¼hibernate-mapping auto-import="true" default-lazy="false"£¾ 
  £¼class name="com.baobaotao.domain.Post" table="t_post"£¾ 
    £¼id name="postId" column="post_id"£¾ 
      £¼generator class="identity" /£¾ 
    £¼/id£¾ 
    £¼property name="userId" column="user_id"/£¾ 
    £¼property name="postText" column="post_text"   
    type="org.springframework.orm.hibernate3.support.ClobStringType"/£¾¢Ù¶ÔÓ¦ CLOB ×ֶΠ
    £¼property name="postAttach" column="post_attach"  
     type="org.springframework.orm.hibernate3.support.BlobByteArrayType"/£¾¢Ú BLOB ×ֶΠ
    £¼property name="postTime" column="post_time" type="date" /£¾ 
    £¼many-to-one name="topic" column="topic_id" class="com.baobaotao.domain.Topic" /£¾ 
  £¼/class£¾ 
£¼/hibernate-mapping£¾ 

¡­ 
£¼bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler" 
 lazy-init="true" /£¾ 
£¼bean id="sessionFactory"   
class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"£¾ 
  £¼property name="dataSource" ref="dataSource" /£¾ 
  £¼property name="lobHandler" ref="lobHandler" /£¾ ¢Ù ÉèÖà LOB ´¦ÀíÆ÷ 
  ¡­ 
£¼/bean£¾ 

£¼?xml version="1.0" encoding="UTF-8" ?£¾ 
£¼!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 
    "http://ibatis.apache.org/dtd/sql-map-2.dtd"£¾ 
£¼sqlMap namespace="Post"£¾ 
  £¼typeAlias alias="post" type="com.baobaotao.domain.Post"/£¾ 
   £¼resultMap id="result" class="post"£¾    
        £¼result property="postId" column="post_id"/£¾    
        £¼result property="userId" column="user_id"/£¾    
        £¼result property="postText" column="post_text" ¢Ù ¶ÁÈ¡ CLOB ÀàÐÍÊý¾Ý 
        typeHandler="org.springframework.orm.ibatis.support.ClobStringTypeHandler"/£¾ 
        £¼result property="postAttach" column="post_attach" ¢Ú ¶ÁÈ¡ BLOB ÀàÐÍÊý¾Ý 
        typeHandler="org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler"/£¾ 
    £¼/resultMap£¾  
  £¼select id="getPost" resultMap="result"£¾ 
    SELECT post_id,user_id,post_text,post_attach,post_time  
    FROM t_post  WHERE post_id =#postId# 
  £¼/select£¾ 
  £¼insert id="addPost"£¾ 
    INSERT INTO t_post(user_id,post_text,post_attach,post_time)  
    VALUES(#userId#, 
  #postText,handler=org.springframework.orm.ibatis.support.ClobStringTypeHandler#, ¢Û 
  #postAttach,handler=org.springframework.orm.ibatis.support.BlobByteArrayTypeHandler#, ¢Ü 
  #postTime#) 
  £¼/insert£¾ 
£¼/sqlMap£¾  

£¼bean id="lobHandler" class="org.springframework.jdbc.support.lob.DefaultLobHandler"     
 lazy-init="true" /£¾ 
£¼bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"£¾ 
 £¼property name="dataSource" ref="dataSource" /£¾ 
 £¼property name="lobHandler" ref="lobHandler" /£¾ ¢ÙÉèÖÃLobHandler 
 £¼property name="configLocation" 
  value="classpath:com/baobaotao/dao/ibatis/sql-map-config.xml" /£¾ 
£¼/bean£¾