Oracle中大文本操作实例

<script type="text/javascript"> google_ad_client = "pub-8800625213955058"; /* 336x280, 创建于 07-11-21 */ google_ad_slot = "0989131976"; google_ad_width = 336; google_ad_height = 280; // </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script> 一直用Oracle，都为大文本段插入而困惑， 以前都是用文件来保存大文本。^_^ 今天终于静下心来，解决这个问题 没有经过很多的测试，所以想贴出来，给大家测一测 也因为，在网上很难找到这样的资料(最少我找了很久，但是能用的很少)， 所以，也可以当作一块引路石。 如果大家还有什么好的方法，来讨论一下吧 ^_^ import java.sql.*; import java.io.*; import java.util.*; import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; /** * LOB Operation Util. * @author Peng Chen[Pizer.Chen -- ICEANT] * @author iceant@21cn.com * @version 1.0 */ public class LOBUtil { public LOBUtil(){} /** * String2LOB store String to LOB * @param data String need to be stored. * @param table_name Table name * @param lob_field_name LOB Field * @param key_field Primary Key Field * @param key_value Primary Key value */ public boolean String2LOB(Connection conn, String data, String table_name, String lob_field_name, String key_field, String key_value)throws SQLException{ try { BLOB lob = getBLOB(conn,table_name,lob_field_name,key_field,key_value); OutputStream os = lob.getBinaryOutputStream(); java.io.BufferedOutputStream bos = new java.io.BufferedOutputStream(os); bos.write(data.getBytes());//Stored String data to BLOB field bos.flush(); bos.close(); os.close(); return true; } catch(SQLException sqle){ throw sqle; }catch (Exception err) { //err.printStackTrace(); return false; } } private static void log(String msg){ System.out.println(msg); } private BLOB getBLOB(Connection conn, String table_name, String lob_field_name, String key_field, String key_value)throws SQLException{ BLOB lob = null; try { StringBuffer sql = new StringBuffer(1024); sql.append("select ").append(lob_field_name).append(" from "); sql.append(table_name).append(" where ").append(key_field); sql.append(" =? for UPDATE "); //sql.append(" = `").append(key_value).append("` for update"); //System.out.println(sql.toString()); // Prepared to get LOB field PreparedStatement pstmt = conn.prepareStatement(sql.toString()); pstmt.setString(1,key_value); //Statement pstmt = conn.createStatement(); //ResultSet rs = pstmt.executeQuery(sql.toString()); // == BUG == //SYSTEM maybe halt here when sqlplus is running and sth is not commited. ResultSet rs = pstmt.executeQuery(); if (rs.next()) { lob = ((OracleResultSet)rs).getBLOB(1); pstmt.close(); return lob; } else { pstmt.close(); throw new SQLException("LOB field can not be found. Please check it again."); } } catch (Exception err) { try{ conn.rollback(); }catch(SQLException sqle){ } throw new SQLException(err.getMessage()); } } /** * LOB2String get String data from LOB * @param conn @see java.sql.Connection Object * @param table_name Table name * @param lob_field_name LOB Field * @param key_field Primary Key Field * @param key_value Primary Key value */ public String LOB2String( Connection conn, String table_name, String lob_field_name, String key_field, String key_value) { try { BLOB p_BLOB = getBLOB(conn,table_name,lob_field_name,key_field,key_value); // Open a stream to read BLOB data InputStream stream = p_BLOB.getBinaryStream(); // Keep BLOB Datas java.io.ByteArrayOutputStream bos = new java.io.ByteArrayOutputStream(); java.io.OutputStream os = new java.io.BufferedOutputStream(bos); // Read from the BLOB stream and write to the stringbuffer int nchars = 0; // Number of chanracters read byte[] l_buffer = new byte[1024]; // Buffer holding characters being transferred while ((nchars = stream.read(l_buffer)) != -1) // Read from BLOB os.write(l_buffer,0,nchars); os.flush(); os.close(); String result = new String(bos.toByteArray()); stream.close(); // Close the BLOB input stream bos.close(); return result; } catch (Exception ex) { // Trap SQL and IO errors try{ conn.rollback(); }catch(SQLException sqle){ } return null; } } }