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>

转载自：http://log-cd.javaeye.com/blog/415844