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

来源：互联网发布：鲁班节点软件编辑：程序博客网时间：2024/05/15 23:51
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.jpgFile 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;    }}
1 0