第一步:创建表
DROP TABLE IF EXISTS `blob_test`;
CREATE TABLE `blob_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`big_bit` blob COMMENT '二进制数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
第二步:编写测试类中的代码
package cn.itcast.jdbc;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.Test;
public class BlobTest {
/**
* 测试向表中插入二进制数据
* @throws SQLException
* @throws IOException
*/
@Test
public void testCreate() throws SQLException, IOException {
create();
}
/**
* 从表中查询二进制数据
* @throws SQLException
* @throws IOException
*/
@Test
public void testRead() throws SQLException, IOException {
read();
}
/**
* 从数据库中查询结果
* @throws SQLException
* @throws IOException
*/
static void read() throws SQLException, IOException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
//建立连接,JdbcUtils参考前一篇博客
conn = JdbcUtils.getConnection();
//创建语句
st = conn.createStatement();
//执行语句
rs = st.executeQuery("select big_bit from blob_test");
//处理结果
while(rs.next()) {
//Blob blob = rs.getBlob(1);
//InputStream in = blob.getBinaryStream();
InputStream in = rs.getBinaryStream("big_bit");
File file = new File("IMG_0002_bak.jpg");
OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
byte[] buff = new byte[1024];
int i = 0;
//从输入流读取对象
while((i = in.read(buff)) > 0) {
out.write(buff, 0, i);
}
out.close();
in.close();
}
} finally {
JdbcUtils.free(rs, st, conn);
}
}
/**
* 向数据库中插入二进制数据
* @throws SQLException
* @throws IOException
*/
static void create() throws SQLException, IOException {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
conn = JdbcUtils.getConnection();
//创建语句
String sql = "insert into blob_test(big_bit) values(?)";
ps = conn.prepareStatement(sql);
File file = new File("IMG_0002.jpg");
InputStream in = new BufferedInputStream(new FileInputStream(file));
ps.setBinaryStream(1, in, file.length());
//执行语句
int i = ps.executeUpdate();
in.close();
System.out.println("i=" + i);
}finally{
JdbcUtils.free(rs, ps, conn);
}
}
}
ResultSet是不应该作为返回的,因为一旦Connection关闭,Statement和ResultSet中的数据全部都会变得无效。