java多线程保存oracle的blob字段在本地生成图像

前些日子折腾的在线拍照，由于只是更新blob字段，没有在本地保存照片，导致客户拍脑门想要的时候不能马上拿出来。没说的谁让顾客是上帝呢，做吧。使用线程池创建三个线程让自定一个线程目标共享（也就是把表里的记录分成n组同时执行导出）。程序很简单，一共四个类和一个ojdbc14.jar包。由于sql语句写错了造成多个线程对一个文件进行写操作，害的我查了半天。

首先定义分页对象也就是每个线程要处理的数据量（通过传入的总页数和总行数得到每页数量）

package com.main;

import java.util.ArrayList;
import java.util.List;

public class PageVO {
 private String startRowNum;//返回查询的起始行
 private String endRowNum;//返回查询的终止行
 private int totalRows; //  数据查询总记录数
 private int totalPages; // 分成页数
 private int pageRows;//每页行数
 public PageVO(){
 }
 public PageVO(int totalRows,int totalPages){
  this.totalPages = totalPages;
  this.totalRows = totalRows;
 }
 public PageVO(String startNum,String endNum){
  this.startRowNum = startNum;
  this.endRowNum = endNum;
 }
 public String getStartRowNum() {
  return startRowNum;
 }
 public void setStartRowNum(String startRowNum) {
  this.startRowNum = startRowNum;
 }
 public String getEndRowNum() {
  return endRowNum;
 }
 public void setEndRowNum(String endRowNum) {
  this.endRowNum = endRowNum;
 }
 public List<PageVO> getPageVOList(){
  List<PageVO> list = new ArrayList<PageVO>();
  pageRows = totalRows/totalPages;
  int currentRow = 1;//定义起始值
  for (int i = 0; i<totalPages; i ++){
   list.add(new PageVO(new Integer(currentRow).toString(),new Integer(pageRows + currentRow).toString()));
   currentRow = currentRow + pageRows + 1;
  }
  return list;
 } 
}

其次获得connection类

package com.main;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class ConnectionFactory {
 private static final ThreadLocal<Connection> tl = new ThreadLocal<Connection>();
 public static Connection getConnection() throws Exception{
  Connection conn = (Connection)tl.get();
  if(conn == null || conn.isClosed()){
   conn = createConnection();
   tl.set(conn);   
  }
  return conn;
 }
 public static void closeConnection()throws Exception{
  Connection conn = (Connection)tl.get();
  tl.set(null);
  if(conn!=null){
   conn.close();
  }  
 }
 public static Connection  createConnection() throws Exception{
  DataSource ds = ConnectionFactory.getDataSource();
     if (ds != null){
      return ds.getConnection();
     }   
        try {
         Properties properties = new Properties();
         InputStream is = ClassLoader.getSystemResourceAsStream("QueryBean.properties");
         properties.load(is);
         String driver = properties.getProperty("driver");
         String url = properties.getProperty("url");
         String user = properties.getProperty("user");
         String password = properties.getProperty("password");         
         Class.forName(driver).newInstance();         
         Connection conn =  DriverManager.getConnection(url,user,password);
          if(conn==null){
           throw new Exception("没有获得连接");
          }else{
           return conn;
          }  
        }
        catch (Exception e) {
          System.out.println(
              "Exception occured in ConnectionFactory.getConnection(): " + e.getMessage());
          return null;
        }
 }
 protected static DataSource getDataSource()
    {
     InitialContext ic;
     DataSource nativeDS = null;
     try {
      ic = new InitialContext();
      nativeDS = (DataSource) ic.lookup("java:comp/env/MyDS");
     }
     catch (NamingException e)
     {
      // 忽略异常，如果没有jndi则用老的连接方式通过配置文件读取
     }
     return nativeDS;
    }
}
再次根据业务定义线程目标类

package com.main;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.imageio.ImageIO;

public class CustomBlobToImage implements Runnable {
 private PageVO pg;
 public CustomBlobToImage(PageVO pg){
  this.pg = pg;
 }
 private void blobToImage(){
  PreparedStatement ps = null;
  Connection conn = null;
        ResultSet rs = null;  
        Blob blob = null;
        int ierror = 0;//问题图片记录数
        int icount = 0;//记录导出图片
        List<String> list =new  ArrayList<String>();
        BufferedImage bufferedImage;
        try {
         conn = ConnectionFactory.getConnection();
         //blob字段值读出
            String ksh = null;
            String path = null;
           
            String sql = "select ksh,zp,num from (select a.*,rownum num from (select * from student order by ksh) a where rownum <= " + pg.getEndRowNum() + " ) where num >= " + pg.getStartRowNum();  
            ps = conn.prepareStatement(sql);    
            rs = ps.executeQuery();
            System.out.println(Thread.currentThread().getName() + "开始处理数据！");
            ierror = 0;//问题图片记录数
            icount = 0;
            while(rs.next()) {
             StringBuffer sb = new StringBuffer();
             ksh = rs.getString("ksh");
             blob = rs.getBlob("zp");
             sb.append("c:/photoimage/");
             sb.append(ksh.substring(0,2));
             sb.append('/');
             sb.append(ksh.substring(2,4));
             sb.append('/');
             sb.append(ksh.substring(4,8));
             sb.append('/');
             path = sb.toString() +  ksh + ".jpg";             
             if (blob == null){
              ierror ++;
              list.add(ksh + " no image");
              continue;
             }
                InputStream in = blob.getBinaryStream();

                File file = new File(path);
                if(!file.exists()){
                 file.mkdirs();
                }
                bufferedImage = ImageIO.read(in);
                if (bufferedImage != null) { 
                 ImageIO.write(bufferedImage, "jpeg", file);                
                }    
                in.close();
                icount++;
                if (icount%100 == 0){
                 System.out.println("线程" + Thread.currentThread().getName() + "完成个数:"  + new Integer(icount).toString() + " 错误累计:" + new Integer(ierror).toString());
                }
            }
            System.out.println("线程" + Thread.currentThread().getName() + "处理完成");
            //记录日志文件
        }catch(Exception e){
         System.out.println( "线程" +  Thread.currentThread().getName());
        }finally{
         try{
          if(rs != null){
           rs.close();
          }
          if(ps != null){
           ps.close();
          }
          if(conn != null){
           ConnectionFactory.closeConnection();
          }
         }catch(Exception e){
          e.printStackTrace();
         }
        }
 }
 public void run() {
  // TODO Auto-generated method stub
  blobToImage();
 }

}
最后main方法调用

package com.main;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.util.Iterator;
import java.util.List;

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class Main {

 /**
  * @param args
  */
 public static void main(String[] args) throws Exception {
  //创建三个可复用的线程
  Connection conn = ConnectionFactory.getConnection();
  PreparedStatement ps = null;
  ResultSet rs = null;
  try{
   ps = conn.prepareStatement("select count(*) from student");
   rs = ps.executeQuery();
   rs.next();
   int totalRows = rs.getInt(1);
   ExecutorService pool = Executors.newFixedThreadPool(3);
   PageVO pg = new PageVO(totalRows,4);
   List list = pg.getPageVOList();
   Iterator<PageVO> it = list.iterator();
   while(it.hasNext()){
    PageVO pgvo = (PageVO)it.next();
    pool.execute(new CustomBlobToImage(pgvo));
   }
   pool.shutdown();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
         try{
          if(rs != null){
           rs.close();
          }
          if(ps != null){
           ps.close();
          }
          if(conn != null){
           ConnectionFactory.closeConnection();
          }
         }catch(Exception e){
          e.printStackTrace();
         }
  }
  

    }  
}