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、在类中定义大字段:
- public class informAffiche {
- private Blob content;
- public void setcontent(Blob S_content)
- {
- content=S_content;
- }
- public Blob getcontent()
- {
- return content;
- }
- }
2、数据库中读取大字段内容并set进去:
- while(rs.next())
- {
- s.setcontent(rs.getBlob("content"));
- }
3、在页面得到
- if (list.size()>0){
- s=(informAffiche)list.get(0);
- Blob blob= s.getcontent();
- if(blob == null || blob.length()==0){
- content = "";
- }else{
- content = new String(blob.getBytes((long)1, (int)blob.length()));
- System.out.println("content---->"+content);
- }
- }
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; } }); }