1 测试表
create table blob_demo(
id VARCHAR2(50),image blob,content blob
)
2 新增一条表数据
2.1 目的
插入一条记录，其中image列存储图片，contet存储正常的文本。
2.2 核心代码
/**
* 新增一条数据
* @param conn
*/
public void addOneData(Connection conn){
try {
String sql = "insert into blob_demo(id,image,content) values(?,?,?)"; //执行的插入sql语句
String pngPath = "/Users/xxxxx/Downloads/test.png"; //图片路径
String content = "this is a test"; //待写入content列的内容
InputStream ins = new FileInputStream(pngPath); //把图片转为io流
PreparedStatement preStat = conn.prepareStatement(sql);
preStat.setString(1,"1");
preStat.setBlob(2,ins);
preStat.setBlob(3,new ByteArrayInputStream(content.getBytes()));
preStat.executeUpdate();
LOG.info("数据新增成功！");
}catch (Exception e){
LOG.error("新增数据失败：{}",e.getMessage());
}
}
3 读取表数据
3.1 目的
查找一条数据，将image字段的图片另存为名称为test_bak.png，将content的内容转为string打印在控制台上。
3.2 核心代码
/**
* 读取数据
* @param conn
*/
public void readData(Connection conn){
Statement stat = null;
ResultSet rs = null;
try {
String sql = "select id,content from blob_demo where id=‘1‘";
long BlobLength = 0;
int i = 1;
byte[] bytes = null;
String content = "";
String filepath = "/Users/weixiurui/Downloads/test_bak.png";
stat = conn.createStatement();
rs = stat.executeQuery(sql);
while (rs.next()){
content = "";
BLOB imageCol = (BLOB) rs.getBlob("image");
BLOB contentCol = (BLOB) rs.getBlob("content");
LOG.info("开始处理image内容...");
//输出到图片/文件
InputStream input = imageCol.getBinaryStream();
FileOutputStream out = new FileOutputStream(filepath);
int len = (int) imageCol.length();
byte buffer[] = new byte[len];
while ((len = input.read(buffer)) != -1) {
out.write(buffer,len);
}
out.close();
input.close();
LOG.info("图片下载完成，请到对应的目录下查看");
LOG.info("开始处理content内容...");
//将blob转为string
BlobLength = contentCol.length(); //获取BLOB长度
while(i<BlobLength) { //循环处理字符串转换，每次1024；Oracle字符串限制最大4k
bytes = contentCol.getBytes(i,1024) ;
i = i + 1024;
content = content + new String(bytes,"utf8") ;
}
LOG.info("content内容为[{}]",content);
}
}catch (Exception e){
LOG.error("操作失败：{}",e.getMessage());
}
}
