Oracle中大文本操作實例
2024-08-29 13:35:00
供稿：網(wǎng)友
一直用Oracle，都為大文本段插入而困惑，
以前都是用文件來保存大文本。^_^
今天終于靜下心來，解決這個問題
沒有經(jīng)過很多的測試，所以想貼出來，給大家測一測
也因為，在網(wǎng)上很難找到這樣的資料(最少我找了很久，但是能用的很少)，
所以，也可以當作一塊引路石。
假如大家還有什么好的方法，來討論一下吧 ^_^
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;
}
}
}
