mysql 存blob_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代码  

public class informAffiche {

private Blob content;

public void setcontent(Blob S_content)

{

content=S_content;

}

public Blob getcontent()

{

return content;

}

}

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

Java代码  

while(rs.next())

{

s.setcontent(rs.getBlob("content"));

}

3、在页面得到

Java代码  

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、页面输出：

方案三

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

}

});

}

}

方案四

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或其他数据库如下设置：

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

lazy-init="true" />

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

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;

}

});

}