mysql 存入 blob类型数据

mysql 存入 blob类型数据

最佳 解决方案

如果是字符串 的 blob  , 在 bean 中 把 该字段 设置成 byte[]  即可，遇到汉字 不会乱码

如果是文件 可以采用下面方法。

 方案一

把String类型转为Blob类型很简单，只要将 newSerialBlob(String对象的.getBytes())就可以获得一个Blob对象，

 要是把Blob对象转为String 一种是new String(Blob.getBytes(0,Blob对象.length)) 可是在开发中这样是不行了，总提示一个错误，只有获得Blob对象的输入流才可以

 IntputStream is = Blob对象.getBinaryStream()在用该对的read()方法读取一个字节数组在转换为String

 方案二

1、在类中定义大字段：

Java代码  

1. public class informAffiche {  
2.    private Blob content;  
3.   
4.   
5. public void setcontent(Blob S_content)  
6. {  
7. content=S_content;  
8. }  
9. public Blob getcontent()  
10. {  
11. return content;  
12. }  
13.   
14. }  

2、数据库中读取大字段内容并set进去：

Java代码  

1. while(rs.next())  
2.     {  
3. s.setcontent(rs.getBlob("content"));  
4. }  

3、在页面得到

Java代码  

1. if (list.size()>0){  
2.    s=(informAffiche)list.get(0);  
3.  Blob blob= s.getcontent();  
4.   
5. if(blob == null || blob.length()==0){  
6.     content = "";  
7.   }else{  
8.     content = new String(blob.getBytes((long)1, (int)blob.length()));  
9.     System.out.println("content---->"+content);  
10.   }  
11.   
12.   
13. }  

4、页面输出：

<td><%=content  %></td>

 方案三

package org.util;  
import java.io.BufferedInputStream;  
import java.io.BufferedOutputStream;  
import java.io.File;  
import java.io.FileInputStream;  
import java.io.FileNotFoundException;  
import java.io.FileOutputStream;  
import java.io.IOException;  
import java.io.InputStream;  
import java.io.InputStreamReader;  
import java.io.OutputStream;  
import java.sql.Connection;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
public class BlobTest {  
    /** 
     * @param args 
     * @throws SQLException  
     * @throws IOException  
     */  
    public static void main(String[] args) throws SQLException, IOException {  
        // TODO Auto-generated method stub  
        //create();  
        read(2);  
    }  
      
    //将二进制流存储入数据库blob字段类型。  
    public static void create() throws SQLException, IOException{  
        String sql = "insert into blob_test values(id,?)";  
          
        Connection conn = null;  
        PreparedStatement ps = null;  
          
        try{  
            conn = JdbcUtil.getInstance().getConnection();  
            ps = conn.prepareStatement(sql);  
            File file = new File("input_text_right.gif");  
            BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));  
            ps.setBlob(1, in);  
            int id = ps.executeUpdate();  
            System.out.println("id:"+id);  
            in.close();  
        }finally{  
            JdbcUtil.free(null, ps, conn);  
        }  
    }  
      
    //读取二进制流，并写入新的文件  
    public static void read(int id) throws SQLException, IOException{  
        String sql = "select big_bit from blob_test where id=? limit 1";  
          
        Connection conn = null;  
        PreparedStatement ps = null;  
        ResultSet rs = null;  
        try{  
            conn = JdbcUtil.getInstance().getConnection();  
            ps = conn.prepareStatement(sql);  
            ps.setInt(1, id);  
            rs = ps.executeQuery();  
              
            byte[] b = new byte[1024];  
            while(rs.next()){  
                InputStream in = rs.getBinaryStream(1);//InputStream是字节输入流的所有类的超类  
                File file = new File("left.gif");  
                BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(file));//BufferedOutputStream是缓冲的输出流  
                  
                for(int i=0; (i=in.read(b))>0;){//read方法接收byte数组，并将数据存储在缓冲数组b中  
                    out.write(b);//将指定的字节写入此缓冲的输出流。  
                }  
                out.close();//关闭此输出流并释放与此流有关的所有系统资源。  
            }  
              
              
        }finally{  
            JdbcUtil.free(rs, ps, conn);  
        }  
    }  
} 

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());
      }
     });
}
}

 方案四

<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" />
</bean>
<bean id="dao" abstract="true">
   <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<bean id="postDao" parent="dao"
   class="com.baobaotao.dao.jdbc.PostJdbcDao">
   <property name="lobHandler" ref="oracleLobHandler" />
</bean>
Oracle 10g或其他数据库如下设置：
<bean id="defaultLobHandler"
   class="org.springframework.jdbc.support.lob.DefaultLobHandler"
   lazy-init="true" />
<bean id="dao" abstract="true">
   <property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<bean id="postDao" parent="dao"
   class="com.baobaotao.dao.jdbc.PostJdbcDao">
   <property name="lobHandler" ref="defaultLobHandler" />
</bean>

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;
        }
       });
}