循环读取图片入库

 

工作总结备份,见代码如下:

 

package com.yusj;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class TestImage {

	private static Connection conn = null;
	private static Statement stmt = null;
	private ResultSet rs = null;

	static {
		try {
			// 加载Oracle驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			// 获得连接
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@10.117.10.5:1521:dqjz1", "jwzh",
					"jwzh");
			stmt = conn.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 关闭所有与数据库相关的连接
	 * 
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	public void closeAll(ResultSet rs, Statement stmt, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 读取图片并入库
	 */
	public void insertDB() {
		// 历史表与路径表关联查询
		String sql = " select rownum, t2.oldfwbh, t2.olddybh, t2.oldbzdzbm, t1.tplj,t2.newfwbh from test_jgt t1, test_jgt_lsb_20130401 t2 where t1.fwbh = t2.oldfwbh and t1.sfdrcg='0' ";  
		String dir = "";
		String[][] res;
		try {
			res = querySql(conn, sql);
			if (res != null) {
				// 循环取出照片
				for (int i = 0; i < res.length; i++) {
					dir = "C:\\test\\images\\images";
					dir += "\\" + res[i][3] + "\\" + res[i][2] + "\\"
							+ res[i][1] + "\\" + res[i][4];
					// 获得fjbh(主键)
					String seqId = getSequenceValue("SEQ_COMMON_SERIVAL_NUMBER",conn) ;
					//String seqId = "1" ;
					System.out.println(seqId+"----"+dir);
					// 逐个插入
					String[] args = {seqId,res[i][5],res[i][4],"image/pjpeg",res[i][1]} ;
					inputImage(args,dir) ;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			// 关闭相应数据库连接
			closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 向数据库中插入图片
	 * @param args
	 * @param impImageDir
	 */
	public void inputImage(String[] args, String impImageDir) {
		try {
			
			conn.setAutoCommit(false);// 取消自动提交功能
			OutputStream os = null;
			// 插入一个空对象empty_blob()
			//stmt.executeUpdate("insert into image_lob (t_id, t_image) values ('"+args[0]+"', empty_blob())");
			stmt.executeUpdate("insert into test_attachment (fjbh, dah, filename, data, cjsj, fjlx) values ('"+args[0]+"','"+args[1]+"','"+args[2]+"', empty_blob(), sysdate, '"+args[3]+"')");
			
			// 锁定数据行进行更新，注意"for update"语句 
			//rs = stmt.executeQuery("select t_image from image_lob where t_id='" + args[0] + "' for update");
			rs = stmt.executeQuery("select data from test_attachment where fjbh='" + args[0] + "' for update");
			
			if (rs.next()) {
				// 得到java.sql.Blob对象后强制转换为oracle.sql.BLOB
				oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("data");
				// 通过getBinaryOutputStream()方法获得向数据库中插入图片的"管道"
				os = blob.getBinaryOutputStream();
				// 读取想要存储的图片文件
				InputStream is = new FileInputStream(impImageDir);
				// 依次读取流字节,并输出到已定义好的数据库字段中.
				int i = 0;
				while ((i = is.read()) != -1) {
					os.write(i);
				}
			}
			os.flush();
			os.close();
			// 图片入库成功
			stmt.executeUpdate("update test_jgt set sfdrcg='1' where fwbh = '"+args[4]+"'") ;
			conn.commit();
			conn.setAutoCommit(true);// 恢复现场
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		} catch (IOException e) {
			//e.printStackTrace();
			try {
				conn.rollback();
				// 本地图片不存在
				stmt.executeUpdate("update test_jgt set sfdrcg='2' where fwbh = '"+args[4]+"'") ;
				conn.commit();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
	}

	/**
	 * 从数据库里检索出图片
	 */
	public void outputImage() {
		try {
			String sql = "select image from t_image where id=1";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				oracle.sql.BLOB b = (oracle.sql.BLOB) rs.getBlob(1);
				InputStream is = b.getBinaryStream();
				FileOutputStream fos = new FileOutputStream(
						"E:\\outputImage.jpg");
				int i = 0;
				while ((i = is.read()) != -1) {
					fos.write(i);
				}
				fos.flush();
				fos.close();
				is.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			closeAll(rs, stmt, conn);
		}
	}

	/**
	 * 执行查询语句,获得返回结果
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 * @throws Exception
	 */
	public String[][] querySql(Connection conn, String sql)
			throws SQLException, Exception {
		if (sql == null)
			throw new Exception("无效的SQL语句！");
		if (conn == null)
			throw new Exception("获取数据库连接失败！");
		conn.setAutoCommit(false);
		ResultSet rs = null;
		Statement stmt = null;
		ResultSetMetaData md = null;

		ArrayList aList = new ArrayList();
		int rows = 0, cols;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			md = rs.getMetaData();
			cols = md.getColumnCount();
			while (rs.next()) {
				String[] row = new String[md.getColumnCount() + 1];
				for (int i = 0; i < md.getColumnCount(); i++) {
					row[i] = rs.getString(i + 1);
				}
				aList.add(row);
			}
			rs.close();
			rs = null;
			stmt.close();
			stmt = null;
		} catch (SQLException e) {
			e.printStackTrace(System.out);
			throw new SQLException("#71:" + e.toString());
		} catch (Exception e) {
			e.printStackTrace(System.out);
			throw e;
		} finally {
			if (stmt != null)
				stmt.close();
			stmt = null;
		}

		rows = aList.size();
		if (rows == 0 || cols == 0) {
			aList.clear();
			aList = null;
			return null;
		}
		String[][] res = new String[rows][cols];
		for (int i = 0; i < rows; i++) {
			Object[] row = (Object[]) aList.toArray()[i];
			for (int j = 0; j < cols; j++) {
				if (row[j] == null)
					res[i][j] = new String("");
				else
					res[i][j] = new String(row[j].toString());
			}
		}
		aList.clear();
		aList = null;
		return res;
	}

	/**
	 * 获得序列
	 * @param seqname
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static String getSequenceValue(String seqname, Connection conn)
			throws SQLException {
		ResultSet rs = null;
		Statement stmt = null;
		String res = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select " + seqname + ".nextval from dual");
			if (rs.next()) {
				res = rs.getString(1);
			}
			rs.close();
			rs = null;
			stmt.close();
			stmt = null;
		} catch (SQLException e) {
			e.printStackTrace(System.out);
			throw e;
		}
		return res;
	}

	public static void main(String[] args) {
		// 从硬盘提取图片插入到数据库中
		// new TestImage().inputImage();
		// 从数据库中检索图片到硬盘
		// new TestImage().outputImage();
		new TestImage().insertDB();
	}
}