更新用blob字段存储照片的Java代码

最新推荐文章于 2022-01-13 14:29:22 发布

执2017

最新推荐文章于 2022-01-13 14:29:22 发布

阅读量540

点赞数

分类专栏： Java编程数据处理

Java编程同时被 2 个专栏收录

30 篇文章 0 订阅

订阅专栏

数据处理

14 篇文章 0 订阅

订阅专栏

package com.service;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;
import oracle.sql.BLOB;
import com.litsoft.cctv.util.propertiesUtil;
/**
* 由于需要将两个不同库的的照片进行更新，表里面存照片的字段是blob类型的，
* 琢磨来一下，直接将结果select查询出来再update行不通，所以先将图片从库里
* 导到本地，然后在从本地拿数据进行更新操作
*
*/
public class UpdateImage{
/**
* 我把数据库的连接信息写到properties文件里
*/
private String toUrl = "repast.url";
private String toUserName= "repast.username";
private String toPassword = "repast.password";
private String fromUrl = "manage.url";
private String fromUserName = "manage.username";
private String fromPassword = "manage.password";
/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
CopyOfImportImg i = new CopyOfImportImg();
i.exportImage();
List<String> accounts = readfile("D:\\image");
i.updateImage(accounts);
}
/**
* 导出照片到本地
*/
public void exportImage(){
long start = System.currentTimeMillis();
Connection conn = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
//加载驱动
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//获得连接(源),其中propertiesUtil是获取properties文件的一个工具类，在这里略过
conn=DriverManager.getConnection(
propertiesUtil.getProperties(fromUrl),propertiesUtil.getProperties(fromUserName),propertiesUtil.getProperties(fromPassword));
//取出所有的照片记录
preparedStatement = conn.prepareStatement("select t.employee_number,t.photofile from oadb.SAP_PHOTO t where t.end_date = '99991231' ");
preparedStatement.execute();
resultSet = preparedStatement.getResultSet();
while(resultSet.next()){
//工号
String account = resultSet.getString("employee_number");
//照片
Blob photo = resultSet.getBlob("photofile");
InputStream inputStream = photo.getBinaryStream();
//将照片放在D盘下，文件名如：002125457.jpg
File fileOutput =
new File("D:\\image\\"+account+".jpg");
if(!fileOutput.exists()){
fileOutput.createNewFile();
}
FileOutputStream fo =
new FileOutputStream(fileOutput);
int c;
while ((c = inputStream.read()) != -1) {
fo.write(c);
}
fo.close();
}
} catch (Exception e) {
e.printStackTrace();
}finally{
try {
resultSet.close();
preparedStatement.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 跟新数据库里的照片
*/
public void updateImage(List<String> list){
long start = System.currentTimeMillis();
//加载驱动
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//获得连接(目标库)
Connection conn = DriverManager.getConnection(
propertiesUtil.getProperties(toUrl),propertiesUtil.getProperties(toUserName),propertiesUtil.getProperties(toPassword));
PreparedStatement preparedStatement = conn.prepareStatement("update employee_info set photo=? where account=?");
PreparedStatement preparedForEmpty = conn.prepareStatement("update EMPLOYEE_INFO t set photo=empty_blob() where t.account=? ");
PreparedStatement preparedForUpdate = conn.prepareStatement("select photo from employee_info where account=? for update");
conn.setAutoCommit(false);
for(int i=0,size=list.size(); i<size; i++){
File imgFile = new File("D:\\image\\"+list.get(i)+".jpg");
//判断图片是否存在
if(imgFile.exists()){
System.out.println("i:"+i+"account:"+list.get(i));
InputStream inputStream = new FileInputStream(imgFile);
//首先将照片设置为空
preparedForEmpty.setString(1, list.get(i));
preparedForEmpty.executeUpdate();
//查询Blob, 获得Blob的Cursor，一定注意sql后面有for update,
preparedForUpdate.setString(1, list.get(i));
ResultSet rs= preparedForUpdate.executeQuery();
BLOB blob = null;
while(rs.next()){
blob = (BLOB)rs.getBlob(1);
//使用字节流将待入库的文件写入到blob中
byte[] temp = new byte[inputStream.available()];
inputStream.read(temp);
OutputStream out = blob.getBinaryOutputStream();
out.write(temp);
out.close();
inputStream.close();
//向数据库中写入数据
preparedStatement.setBlob(1,blob);
preparedStatement.setString(2, (String) list.get(i));
preparedStatement.executeUpdate();
conn.commit();
}
}else{
System.out.println("找不到文件"+list.get(i));
continue;
}
}
long end = System.currentTimeMillis();
preparedStatement.close();
System.out.println("导入照片结束，耗时："+((end-start)/1000/60)+"分钟");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取某个文件夹下的所有文件
*/
public static List<String> readfile(String filepath) throws Exception {
List name = new ArrayList<String>();
try {
File file = new File(filepath);
if (!file.isDirectory()) {
System.out.println("文件");
System.out.println("path=" + file.getPath());
System.out.println("absolutepath=" + file.getAbsolutePath());
System.out.println("name=" + file.getName());
} else if (file.isDirectory()) {
System.out.println("文件夹");
String[] filelist = file.list();
for (int i = 0; i < filelist.length; i++) {
File readfile = new File(filepath + "\\" + filelist[i]);
if (!readfile.isDirectory()) {
System.out.println("path=" + readfile.getPath());
System.out.println("absolutepath="
+ readfile.getAbsolutePath());
System.out.println("name=" + readfile.getName());
//将文件名如：002125457.jpg的工号取出
name.add(readfile.getName().substring(0, readfile.getName().lastIndexOf(".")));
} else if (readfile.isDirectory()) {
readfile(filepath + "\\" + filelist[i]);
}
}
}
} catch (FileNotFoundException e) {
System.out.println("readfile() Exception:" + e.getMessage());
}
return name;
}
}

转自：http://blog.csdn.net/u013341688/article/details/51832575

执2017

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
更新用blob字段存储照片的Java代码

一直以来，上传文件是我很头疼的一个问题，终于搞懂。思路如下：先声明Blob，利用Blob的getBinaryOutputStream()方法获得OutputStream来做输出保存到数据库，期间利用了outputstream的写inputstream的过程。读取时，利用getBinaryStream()，将数据库的blob字段化为二进制流利用inputstream读写。总体来说就是利用将blob类型转换为二进制流，通过Java本身的API帮助形成文件的读写。
复制链接

扫一扫