- 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