使用JAVA读取ORACLE BLOB字段实现上传下载

精选文库使用JAVA读取ORACLE BLOB字段实现上传下载大对象类型BLOB全称为Binary Large Objects，即二进制大对象。可以把BLOB区别为三种形式：声像数据、二进制数据和大文本数据。因此，最常见的应用就是存储图形、声音等对象，此外大二进制对象、OLE对象也可以通过BLOB类型存入数据库，如果文本对象过大，超出了文本类型的规定长度，则必须用BLOB字段进行存储。我们在经常使用的编程环境中并不能直接支持BLOB字段，因此需要调用相应的函数完成BLOB的使用。二 实际Struts项目的处理流程1 插入BLOB字段的流程表示层：上传使用struts的标签，提交给指定处理的Action，在ActionForm中使用struts自带的FormFile来保存文件。核心代码：.省略控制层：在Action中将传入的ActionForm中的文件字段赋给VO值对象，并调用业务代理类的上传方法。核心代码： /新增 if(actionType.equals(insert) /得到文件类型 int iFileType = this.getFileType(drawingInputForm.getFileExtendName(); if(iFileType = 0) /不支持文件类型 this.addError(request, drawing.errors.upload.UnSupportedFileType); else DrawingVO objDrawingVO = new DrawingVO(); /图纸基本属性 objDrawingVO.setDrawingName(drawingInputForm.getDrawingName(); .省略其他set方法 /执行新增（上传） int iRt = objDrawingMan.insertDrawing(objDrawingVO); .省略 Facade门面：通过业务代理类调用DAO中的上传方法，对客户端完全透明。 public int insertDrawing(DrawingVO drawingVO) throws ComtopModuleException try DrawingDAO drawingDAO = new DrawingDAO(); return drawingDAO.insertDrawing(drawingVO); catch(DrawingException ex) throw new ComtopModuleException(drawing.errors.insert, ex); 持久层：DAO中实现和ORACLE数据库的底层交涉，完成真正的文件上传。需要先插入一个空BLOB对象，然后Update这个空对象。 public int insertDrawing(DrawingVO drawingVO) throws DrawingException PreparedStatement pstmt = null; Statement stmt = null; Connection conn = null; int iKey = 0; ResultSet rs = null; /定义SQL语句 String strSQLInsert = null; String strSQLUpdate = null; try conn = dataSource.getConnection(); conn.setAutoCommit(false); /插入空BLOB，empty_blob(),其中表中的Content是BLOC类型字段 strSQLInsert = insert into PROD_DRAWING (DRAWING_ID, DRAWING_NAME, 省略. + CONTENT) + values (?, ?, 省略., empty_blob(); /得到待处理文件 FormFile drawingFile = drawingVO.getDrawingFile(); /插入普通字段 pstmt = conn.prepareStatement(strSQLInsert); /得到主键 iKey = Toolkit.getInstance().getNextKey(DrawingInfo.ID_STORE_KEY_DRAWING); pstmt.setInt(1, iKey); .省略其他set方法 pstmt.executeUpdate(); stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); strSQLUpdate = SELECT CONTENT FROM PROD_DRAWING WHERE DRAWING_ID = + iKey + + FOR UPDATE; /读出记录以增加图片Blob字段 rs = stmt.executeQuery(strSQLUpdate); if(rs.next() logger.debug(开始写入BLOB); /这里不能用oracle.sql.BLOB，会报ClassCast异常 weblogic.jdbc.vendor.oracle.OracleThinBlob blob = (weblogic.jdbc.vendor. oracle. OracleThinBlob)rs.getBlob(1); logger.debug(得到输出流); OutputStream outStream = blob.getBinaryOutputStream(); InputStream fin = drawingFile.getInputStream(); logger.debug(开始分配缓存); byte b = new byteblob.getBufferSize(); int len = 0; while(len = fin.read(b) != -1) logger.debug(正在写入BLOB流); outStream.write(b, 0, len); logger.debug(关闭所有流); fin.close(); outStream.flush(); outStream.close(); rs.close(); mit(); catch(Exception ex) .省略 finally DBUtil.destroyDB(rs, pstmt, conn); return iKey; 2 读取BLOB字段的流程从数据库中读出BLOB数据没有上述由于连接池的不同带来的差异，程序流程同插入BLOB字段，但是读BLOB就不用那么复杂了，只需要J2SE的标准类java.sql.Blob就可以取得输出流。DAO中的核心代码： public DrawingVO readDrawing(int drawingId) throws DrawingException PreparedStatement pstmt = null; Connection conn = null; DrawingVO objDrawingVO = null; ResultSet rs = null; /定义SQL语句 String strSQL = SELECT * FROM PROD_DRAWING WHERE DRAWING_ID=?; try conn = dataSource.getConnection(); pstmt = conn.prepareStatement(strSQL); /设置参数 pstmt.setInt(1, drawingId); /执行查询 rs = pstmt.executeQuery(); while(rs.next() objDrawingVO = new DrawingVO(); objDrawingVO.setDrawingId(rs.getInt(DRAWING_ID); objDrawingVO.setDrawingName(rs.getString(DRAWING_NAME); .省略其他set方法 /set BLOB到VO中 objDrawingVO.setContent(rs.getBlob(CONTENT); catch(Exception ex) .省略 finally DBUtil.destroyDB(rs, pstmt, conn); return objDrawingVO; 这样，传到Action中VO对象就包含这个BLOB对象了，然后需要在Action中对该对象转为输入流，可以选择文件输出流或Servlet输出流，根据具体情况定，这里选择文件输出流。核心代码： private String getBlobToFile(Blob blob, DrawingVO objDrawingVO) throws Exception InputStream ins = blob.getBinaryStream(); /用文件模拟输出流 String strFileName = objDrawingVO.getDrawingName() + . + objDrawingVO.getFileExtendName(); String strRootFilePath = this.getServlet().getServletContext().getRealPath(); String strFilePath = /temp/ + strFileName; String contextFilePath = strRootFilePath + strFilePath; /定义文件对象 File file = new File(this.getServlet().getServletContext().getRealPath() + /temp); if(!file.exists() file.mkdir(); /定义输出流 OutputStream fout = new FileOutputStream(contextFilePath, true); /下面将BLOB数据写入文件 byte b = new byte1024; int len = 0; while(len = ins.read(b) != -1) fout.write(b, 0, len); /依次关闭 fout.close(); ins.close(); return strFilePath; 最后，在Action中调用这个私有方法，完成读取操作。importjava.io.*;importjava.sql.*;publicclassBlobOperationpublicstaticvoidaddLob(longid,StringbinFile)throwsSQLExceptionConnectioncon=null;PreparedStatementps=null;ResultSetrs=null;trycon=ConnectionFactory.getConnection();/换成你自己取连接的方法con.setAutoCommit(false);Stringsql=INSERTINTOBlob_Tbl(id,binfile,bincontent);sql+=VALUES(?,?,?);ps=con.prepareStatement(sql);ps.setLong(1,id);ps.setString(2,binFile);ps.setBlob(3,oracle.sql.BLOB.empty_lob();ps.executeUpdate();/DatabaseUtils.closeObject(ps);ps=con.prepareStatement(SELECTbincontentFROMBlob_TblWHEREid=+id+forupdate);rs=ps.executeQuery();if(rs.next()oracle.sql.BLOBbinContent=(oracle.sql.BLOB)rs.getBlob(1);/*writeblobcontent*/OutputStreambinOut=binContent.getBinaryOutputStream();BufferedOutputStreamout=newBufferedOutputStream(binOut);BufferedInputStreamin=newBufferedInputStream(newFileInputStream(binFile);intc;while(c=in.read()!=-1)out.write(c);in.close();out.close();mit();catch(Exceptione)e.printStackTrace();trycon.rollback();catch(SQLExceptionse)thrownewSQLException(e.getMessage();finallyDatabaseUtils.closeObject(rs,ps,con);publicstaticvoidfetchLob(longid,Stringfilename)throwsSQLExceptionConnectioncon=null;Statementst=null;ResultSetrs=null;trycon=ConnectionFactory.getConnection();Stringsql=SELECT*FromBlob_TblWhereid=+id;st=con.createStatement();rs=st.executeQuery(sql);while(rs.next()StringbinFile=rs.getString(binfile);oracle.sql.BLOBbinContent=(oracle.sql.BLOB)rs.getBlob(bincontent);/*readblobcontent*/BufferedOutputStreamout=newBufferedOutputStream(newFileOutputStream(filename);BufferedInputStreamin=newBufferedInputStream(binContent.getBinaryStream();intc;while(c=in.read()!=-1)out.write(c);in.close();out.close();catch(Exceptione)thrownewSQLException(e.getMessage();finallyDatabaseUtils.closeObject(rs,st,con);publicstaticvoidmain(Stringargs)throwsExceptionif(args.length=0)addLob(1,a.jpg);elsefetchLob(1,args0);package com.semovy.test;import java.io.BufferedInputStream;import java.io.BufferedReader;import java.io.BufferedWriter;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.InputStreamReader;import java.io.OutputStream;import java.io.PrintWriter;import java.io.Reader;import java.sql.Blob;import java.sql.Clob;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;/* * author semovy 测试向oracle 读,写文件Blob 读,写大文本Clob*/public class OracleBlobTest private String driver = oracle.jdbc.driver.OracleDriver;private String url = jdbc:oracle:thin:localhost:1521:teckotooling;private String user = scott;private String pwd = tiger;public OracleBlobTest() public static void main(String args) OracleBlobTest obt = new OracleBlobTest();obt.writeBlob();obt.readBlob();obt.writeClob();obt.readClob();/* * 读二进制文件 * */private void readBlob() Connection conn = null;try conn = getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(select * from test where id=1);byte buffer = new byte1024;OutputStream out = new FileOutputStream(d:/360安全卫士定1.exe);int tempLen = 0;int amount = 0;if (rs.next() Blob blob = rs.getBlob(BINARYCONTENT);InputStream in = blob.getBinaryStream();while (tempLen = in.read(buffer) != -1) out.write(buffer, 0, tempLen);amount += tempLen;System.out.println(已经读出并写: + amount + 字节);System.out.println(已经读出并写:完成);out.flush();out.close();in.close();rs.close();stmt.close(); catch (ClassNotFoundException e) System.out.println(e.getLocalizedMessage(); catch (SQLException e) System.out.println(e.getLocalizedMessage(); catch (IOException e) System.out.println(e.getLocalizedMessage(); finally try if (conn != null)conn.close(); catch (SQLException e) System.out.println(e.getLocalizedMessage();/* * 写二进制文件 * */private void writeBlob() Connection conn = null;try conn = getConnection();conn.setAutoCommit(false);String sql = null;Statement stmt = conn.createStatement();sql = delete from test where id=1;stmt.executeUpdate(sql);sql = insert into test(1,BINARYCONTENT,CLOBCONTENT) values(1,empty_blob(),empty_clob();stmt.executeUpdate(sql);ResultSet rs = stmt.executeQuery(select * from test where id=1);if (rs.next() Blob blob = rs.getBlob(BINARYCONTENT);OutputStream out = (oracle.sql.BLOB) blob).setBinaryStream(0);/ 从0开始,否则写出的文件有差错int bufferSize = (oracle.sql.BLOB) blob).getBufferSize();System.out.println(bufferSize : + bufferSize);BufferedInputStream in = new BufferedInputStream(new FileInputStream(d:/360安全卫士定.exe), bufferSize);byte b = new bytebufferSize;int count = in.read(b, 0, bufferSize);int amount = 0;while (count != -1) out.write(b, 0, count);amount += count;System.out.println(处理了 + amount + 字节);count = in.read(b, 0, bufferSize);System.out.println(处理了 + amount + 字节,成功);out.close();out = null;in.close();mit(); catch (ClassNotFoundException e) System.out.println(e.getLocalizedMessage(); catch (SQLException e) try conn.rollback(); catch (SQLException e1) System.out.println(e1.getLocalizedMessage();System.out.println(e.getLocalizedMessage(); catch (IOException e) System.out.println(e.getLocalizedMessage(); finally try if (conn != null)conn.close(); catch (SQLException e) System.out.println(e.getLocalizedMessage();/* * 读大文本 * */private void readClob() Connection conn = null;try conn = getConnection();Statement stmt = conn.createStatement();ResultSet rs = stmt.executeQuery(select * from test where id=2);String tempStr = null;if (rs.next() Clob clob = rs.getClob(CLOBCONTENT);if (clob != null) Reader in = clob.getCharacterStream();BufferedReader br = new BufferedReader(in);System.out.println(开始读.);while (tempStr = br.readLine() != null) System.out.println(tempStr);System.out.println(读完成.);in.close();rs.close();stmt.close(); catch (ClassNotFoundException e) System.out.println(e.getLocalizedMessage(); catch (SQLException e) System.out.println(e.getLocalizedMessage(); catch (IOException e) System.out.println(e.getLocalizedMessage(); finally try if (conn != null)conn.close(); catch (SQLException e) System.out.println(e.getLocalizedMessage();/* * 写大文本 * */private void writeClob() Connection conn = null;try conn = getConnection();conn.setAutoCommit(false);String sql = null;Statement stmt = conn.createStatement();sql = delete from test where id=2;stmt.executeUpdate(sql);sql = insert into test values(2,empty_blob(),empty_clob();stmt.executeUpdate(sql);ResultSet rs = stmt.executeQuery(select * from test where id=2);if (rs.next() Clob clob = rs.getClob(CLOBCONTENT);PrintWriter out = new PrintWriter(new BufferedWriter(oracle.sql.CLOB) clob).setCharacterStream(0);BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(d:/在北大校园BBS引起轰动的一篇文章请热爱祖国的人转发！.mht);String str = null;System.out.println(开始写.);while (str = in.readLine() != null) out.println(str);System.out.println(str);in.close();out.close();rs.close();mit(); catch (ClassNotFoundException e) System.out.println(e.getLocalizedMessage(); catch (SQLException e) try conn.rollback(); catch (SQLException e1) System.out.println(e1.getLocalizedMessage();System.out.println(e.getLocalizedMessage(); catch (IOException e) System.out.println(e.getLocalizedMessage(); finally try if (conn != null)conn.close(); catch (SQLException e) System.out.println(e.getLocalizedMessage();private Connection getConnection() throws ClassNotFoundException,SQLException Class.forName(driver);return DriverManager.getConnection(url, user, pwd);/* * * param rs * throws SQLException */private void displayResultSet(ResultSet rs) throws SQLException ResultSetMetaData rsmd = rs.getMetaData();int colnum = rsmd.getColumnCount();while (rs.next() for (int i = 0; i colnum; i+) if (i = colnum - 1)System.out.print(rsmd.getColumnLabel(i + 1) + : + rs.getObject(i + 1);elseSystem.out.print(rsmd.getColumnLabel(i + 1) + : + rs.getObject(i + 1) + , );System.out.println();一般来说，大对象存储是把文件存到数据库中，当然也可以内存中的超大字符串。对于象图片这样的文件当然是用二进制存储，这里有很多误区，网络上的教程99%都是行不通的，连SUN自己的文档都一直错误，虽然错误很小。按说二进制文件应该存为BLOB类型，但JBDC2并不能直接对BLOB存入二进制文件，如果你这样做，会得到一个IO而不是SQL异常，为此花了我近两个小时才弄清楚。 如果要把一个二制文件存入ORACLE，用标准的JDBC你就要用LONG ROW类型： create table tb_file(name varchar(20),detail long row); 然后 File file = new File(aaa.gif); int fileLength =(int) file.length(); InputStream fin = new FileInputStream(file); PreparedStatement pstmt = con.prepareStatement(insert into tb_file values(aaa.gif,?); pstmt.setBinaryStream (1, fin, fileLength); pstmt.e