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[] dataoutStream.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); // 设置缓冲区大小，这里是4kbServletFileUpload upload = new ServletFileUpload(factory);upload.setSizeMax(4194304); // 设置最大文件尺寸，这里是4MBList<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