oracle blob数据存取

    Oracle中的lob (Large Object)可以存储非常大的数据（可能是4GB），这样就可以通过将文件或其它任何对象序列化成字节输出流(OutputStream)后写入数据库，之后使用字节输入流(InputStream)将数据读出然后反序列化为原始文件或对象。操作时需要使用oracle的JDBC包，它扩展了sun的JDBC包中的Blob对象。
    以下是一个保存图片进数据库的例子:
1.servlet:用于保存图片并将图片输出

package com.logcd.servlet;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Iterator;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import oracle.sql.BLOB;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class ImageServlet extends HttpServlet {

	private static final long serialVersionUID = 1L;

	/**
	 * 处理请求
	 * @throws FileUploadException
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		Iterator<FileItem> i = getFileItem(request);
		String title = "";
		byte[] data = null;

		while (i.hasNext()) {
			FileItem fi = (FileItem) i.next();
			if (fi.isFormField()) {// 取得表单域
				if(fi.getFieldName().equalsIgnoreCase("title")){
                              title = new String(fi.getString().getBytes("iso8859-1"),"gbk");
					}
			} else {// 取文件域
				data = fi.get();//文件二进制数据
			}
		}

		Integer id = saveImageUseProc(data,title);//saveImage(data, title);//存入 
		//outputImage(response, id);//读出
		outputImageUseProc(response,id);
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);
	}

	/**
	 * 通过SQL保存图片
	 * @param data
	 * @param title
	 */
	@SuppressWarnings("deprecation")
	public static Integer saveImage(byte[] data, String title) {
		Connection conn = getConnection();
		Integer id = (int) (Math.random() * 100000);
		String sql = "insert into t_image(id,title,image) values(" + id + ",'"
				+ title + "',empty_blob())";
		Statement stmt;
		OutputStream outStream = null;
		try {
			conn.setAutoCommit(false);// 如果不关闭会报-->"错误:读取违反顺序"

			stmt = conn.createStatement();
			stmt.execute(sql);

			String sqll = "select image from t_image where id=" + id
					+ " for update";

			ResultSet rs = stmt.executeQuery(sqll);
			if (rs.next()) {
				BLOB blob = (BLOB) rs.getBlob("image");
				outStream = blob.getBinaryOutputStream();
				// data是传入的byte数组，定义：byte[] data
				outStream.write(data, 0, data.length);

				outStream.flush();
				outStream.close();
				conn.commit();
			}
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return id;

	}

	/**
	 * 调用存储过程保存图片
	 * @param data
	 * @param title
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static Integer saveImageUseProc(byte[] data, String title){
		Integer id = null;
		BLOB blob = null;
		OutputStream outStream;
		Connection conn = getConnection();
		try{
			conn.setAutoCommit(false);
			String call="{call OPERATE_BLOB.SAVE_BLOB_IMAGE(?,?,?)}";//调用语句
		    CallableStatement proc=conn.prepareCall(call);//调用存储过程
            proc.setString(1, title);
            proc.registerOutParameter(2, Types.BLOB);
		    proc.registerOutParameter(3, Types.INTEGER); 
            
            proc.execute();
            
            blob = (BLOB)proc.getBlob(2);
            id = proc.getInt(3);//返回结果

			outStream = blob.getBinaryOutputStream();
			outStream.write(data, 0, data.length);
			outStream.flush();
			outStream.close();
            
			proc.close();
            conn.commit();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return id;
	}

	/**
	 * 输出保存的图片
	 * @param response
	 * @param id
	 */
	public static void outputImage(HttpServletResponse response, Integer id) {
		Connection con = getConnection();
		byte[] data = null;
		try{
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery("select image from t_image where id="
					+ id);
			if (rs.next()) {
				BLOB blob = (BLOB)rs.getBlob("image");
				InputStream inStream = blob.getBinaryStream();
				int bufferSize = blob.getBufferSize();
				data = new byte[bufferSize];
				int count = inStream.read(data, 0, bufferSize);
				while(count != -1){//读出字节数据
					response.getOutputStream().write(data,0,count);
					count = inStream.read(data, 0, bufferSize);
				}
				inStream.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}

		}
	}

	/**
	 * 调用存储过程输出图片
	 * @param response
	 * @param id
	 */
	public static void outputImageUseProc(HttpServletResponse response, Integer id){
		Connection conn = getConnection();
        try{
        	String call = "{call OPERATE_BLOB.QUERY_BLOB_IMAGE(?,?)}";
		    CallableStatement proc=conn.prepareCall(call);//调用存储过程
            
		    proc.setInt(1, id);
		    proc.registerOutParameter(2, Types.BLOB);
		    
		    proc.execute();
		    
		    BLOB blob = (BLOB)proc.getBlob(2);
			
		    InputStream inStream = blob.getBinaryStream();
			int bufferSize = blob.getBufferSize();
			byte[] data = new byte[bufferSize];
			int count = inStream.read(data, 0, bufferSize);
			while(count != -1){//读出字节数据
				response.getOutputStream().write(data,0,count);
				count = inStream.read(data, 0, bufferSize);
			}
			inStream.close();
		    
        }catch(Exception e){
        	e.printStackTrace();
        }finally{
        	try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
        }
	}
	
	/**
	 * 取得所有表单数据
	 * @param request
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static Iterator<FileItem> getFileItem(HttpServletRequest request) {
		DiskFileItemFactory factory = new DiskFileItemFactory();
		factory.setSizeThreshold(4096); // 设置缓冲区大小，这里是4kb
		ServletFileUpload upload = new ServletFileUpload(factory);
		upload.setSizeMax(4194304); // 设置最大文件尺寸，这里是4MB

		List<FileItem> items = null;
		Iterator<FileItem> i = null;
		try {
			items = upload.parseRequest(request);
			i = items.iterator();
		} catch (FileUploadException e) {
			e.printStackTrace();
		}

		return i;
	}

	/**
	 * 取得数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@195.2.199.6:1521:orcl";
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, "testdb", "logcd");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException ex) {
			ex.printStackTrace();
		}
		return conn;
	}

}

2.所用到的存储过程

CREATE OR REPLACE PACKAGE BODY OPERATE_BLOB AS

  PROCEDURE  SAVE_BLOB_IMAGE(
      PC_TITLE  IN   VARCHAR2,
      PB_IMAGE  OUT   BLOB,
      PN_ID     OUT  INTEGER
    )AS
      v_id INTEGER;
  BEGIN   
       SELECT nvl(MAX(id),1000) + 1 INTO v_id FROM t_image; 
       PN_ID := v_id;
       INSERT INTO t_image(id,title,image) values(v_id,PC_TITLE,empty_blob())
       RETURNING image INTO PB_IMAGE;

  END;

  PROCEDURE QUERY_BLOB_IMAGE(
     PN_ID      IN   INTEGER,
     PB_IMAGE   OUT  BLOB
  )AS
  BEGIN
     SELECT image INTO PB_IMAGE FROM t_image WHERE id = PN_ID;  
  END;

END;

3.web.xml配置servlet

    <servlet>
        <servlet-name>ImageServlet</servlet-name>
        <servlet-class>com.logcd.servlet.ImageServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>ImageServlet</servlet-name>
        <url-pattern>/imageServlet</url-pattern>
    </servlet-mapping>

4.在image.html页面中调用下

<HTML>
	<HEAD>
		<TITLE>Image File</TITLE>
		<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
	</HEAD>
	<FORM method="POST" encType="multipart/form-data" action="imageServlet">
		<INPUT type="text" name="title">
		<BR>
		<INPUT type="file" name="image">
		<BR>
		<INPUT type="submit" value="提交">
	</FORM>
	<BODY>
	</BODY>
</HTML>

评论

1 楼 Larkin_chen 2012-05-29  

xiexie