oracle之CLOB处理完整版
/**
*
*操作oracle数据库的CLOB字段，包括读和写
*作者：令少爷
* */
package com.nes.common.sql.lob;
import java.sql.*;
import java.io.*;
import oracle.jdbc.OracleResultSet;
import oracle.sql.*;
public class JClob {
String tableName = null; //表名
String primaryKey = null; //表的主键名
String primaryValue = null; //表的主键值
String fieldName = null; //表的CLOB字段名
String clobValue = null; //表的CLOB字段值
Connection conn = null; //与oracle的连接
/**
*
*用于测试用
*
* */
public static void main(String[] args) {
try {
JClob jc = new JClob(getConnection(),"aa","a","aaaa","c","ccc");
jc.write();
jc.read();
}
catch (Exception e) {
System.out.println(e);
e.printStackTrace();
}
}
/**
*
*构造方法
*
* */
public JClob(Connection connection,String tableName,String primaryKey,String primaryValue,String fieldName,String clobValue) {
this.conn = connection;
this.tableName = tableName;
this.primaryKey = primaryKey;
this.primaryValue = primaryValue;
this.fieldName = fieldName;
this.clobValue = clobValue;
}
/**
*
*构造方法，但不必传clobValue值
*一般构造出的实例用来读Clob字段
*
* */
public JClob(Connection connection,String tableName,String primaryKey,String primaryValue,String fieldName) {
this.conn = connection;
this.tableName = tableName;
this.primaryKey = primaryKey;
this.primaryValue = primaryValue;
this.fieldName = fieldName;
}
/**
*
*用于测试
*
* */
public static Connection getConnection() throws SQLException,ClassNotFoundException {
Class.forName("oracle.jdbc.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.18:1521:portal","portal","portal");
return conn;
}
/**
*
*读数据库中clob字段的内容
*@return clob字段值
*
* */
public String read() throws SQLException,IOException {
String rtn = null;
try {
String sql = "select " + fieldName + " from " + tableName + " where " + primaryKey + "=" + primaryValue;
//Connection conn = getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
//int v = Integer.parseInt(primaryValue);
//pstmt.setInt(1,v);
ResultSet rs = pstmt.executeQuery();
java.sql.Clob clob = null;
if (rs.next()) {
clob = rs.getCLOB(fieldName);
//clob = ((OracleResultSet)rs).getCLOB(fieldName);
//clob = ((org.apache.commons.dbcp.DelegatingResultSet)rs).getClob(fieldName);
//Reader in = clob.getCharacterStream();
InputStream input = clob.getAsciiStream();
int len = (int)clob.length();
byte[] by = new byte[len];
int i ;//= input.read(by,0,len);
while(-1 != (i = input.read(by, 0, by.length))) {
input.read(by, 0, i);
}
rtn = new String(by);
}
}
catch (SQLException e){
throw e;
}
catch (Exception ee) {
ee.printStackTrace();
}
return rtn;
}
/**
*
*葱数据库中clob字段的内容
*
* */
public void write() throws SQLException,IOException {
String sql = "update " + tableName + " set " + fieldName + "=empty_clob() where " + primaryKey + "=" + primaryValue;
//Connection conn = getConnection();
conn.setAutoCommit(false);
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.executeUpdate();
sql = "select " + fieldName + " from " + tableName + " where " + primaryKey + "=" + primaryValue;
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql);
java.sql.Clob clob ;
if (rs.next()) {
clob = ((oracle.jdbc.OracleResultSet)rs).getClob(fieldName);
//clob = ((org.apache.commons.dbcp.DelegatingResultSet)rs).getClob(fieldName);
oracle.sql.CLOB my_clob = (oracle.sql.CLOB)clob;
OutputStream writer = my_clob.getAsciiOutputStream();
byte[] contentStr = this.getContent().getBytes();
writer.write(contentStr);
writer.flush();
writer.close();
}
conn.commit();
rs.close();
st.close();
pstmt.close();
conn.setAutoCommit(true);
}
/**
*
*
* */
private String getContent() {
return this.clobValue;
}
/**
*
*
* */
public void setClobValue(String clobValue) {
this.clobValue = clobValue;
}
}
*******************************
JAVA完全控制Oracle中BLOB|CLOB说明
环境:
Database: Oracle 9i
App Server: BEA Weblogic 8.14
表结构:
CREATE TABLE TESTBLOB (ID Int, NAME Varchar2(20), BLOBATTR Blob)
CREATE TABLE TESTBLOB (ID Int, NAME Varchar2(20), CLOBATTR Clob)
JAVA可以通过JDBC,也可以通过JNDI访问并操作数据库,这两种方式的具体操作存在着一些差异,由于通过App Server的数据库连接池JNDI获得的数据库连接提供的java.sql.Blob和java.sql.Clob实现类与JDBC方式提供的不同,因此在入库操作的时候需要分别对待；出库操作没有这种差异,因此不用单独对待.
一、BLOB操作
1、入库
(1)JDBC方式
//通过JDBC获得数据库连接
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");
con.setAutoCommit(false);
Statement st = con.createStatement();
//插入一个空对象empty_blob()
st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, "thename", empty_blob())");
//锁定数据行进行更新,注意"for update"语句
ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");
if (rs.next())
{
//得到java.sql.Blob对象后强制转换为oracle.sql.BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
OutputStream outStream = blob.getBinaryOutputStream();
//data是传入的byte数组,定义:byte[] data
outStream.write(data, 0, data.length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
(2)JNDI方式
//通过JNDI获得数据库连接
Context context = new InitialContext();
ds = (DataSource) context.lookup("ORA_JNDI");
Connection con = ds.getConnection();
con.setAutoCommit(false);
Statement st = con.createStatement();
//插入一个空对象empty_blob()
st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, "thename", empty_blob())");
//锁定数据行进行更新,注意"for update"语句
ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update");
if (rs.next())
{
//得到java.sql.Blob对象后强制转换为weblogic.jdbc.vendor.oracle.OracleThinBlob(不同的App Server对应的可能会不同)
weblogic.jdbc.vendor.oracle.OracleThinBlob blob = (weblogic.jdbc.vendor.oracle.OracleThinBlob) rs.getBlob("BLOBATTR");
OutputStream outStream = blob.getBinaryOutputStream();
//data是传入的byte数组,定义:byte[] data
outStream.write(data, 0, data.length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
2、出库
//获得数据库连接
Connection con = ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statement st = con.createStatement();
//不需要"for update"
ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1");
if (rs.next())
{
java.sql.Blob blob = rs.getBlob("BLOBATTR");
InputStream inStream = blob.getBinaryStream();
//data是读出并需要返回的数据,类型是byte[]
data = new byte[input.available()];
inStream.read(data);
inStream.close();
}
inStream.close();
con.commit();
con.close();
二、CLOB操作
1、入库
(1)JDBC方式
//通过JDBC获得数据库连接
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test");
con.setAutoCommit(false);
Statement st = con.createStatement();
//插入一个空对象empty_clob()
st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) values (1, "thename", empty_clob())");
//锁定数据行进行更新,注意"for update"语句
ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update");
if (rs.next())
{
//得到java.sql.Clob对象后强制转换为oracle.sql.CLOB
oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR");
Writer outStream = clob.getCharacterOutputStream();
//data是传入的字符串,定义:String data
char[] c = data.toCharArray();
outStream.write(c, 0, c.length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
(2)JNDI方式
//通过JNDI获得数据库连接
Context context = new InitialContext();
ds = (DataSource) context.lookup("ORA_JNDI");
Connection con = ds.getConnection();
con.setAutoCommit(false);
Statement st = con.createStatement();
//插入一个空对象empty_clob()
st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) values (1, "thename", empty_clob())");
//锁定数据行进行更新,注意"for update"语句
ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update");
if (rs.next())
{
//得到java.sql.Clob对象后强制转换为weblogic.jdbc.vendor.oracle.OracleThinClob(不同的App Server对应的可能会不同)
weblogic.jdbc.vendor.oracle.OracleThinClob clob = (weblogic.jdbc.vendor.oracle.OracleThinClob) rs.getClob("CLOBATTR");
Writer outStream = clob.getCharacterOutputStream();
//data是传入的字符串,定义:String data
char[] c = data.toCharArray();
outStream.write(c, 0, c.length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
2、出库
//获得数据库连接
Connection con = ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statement st = con.createStatement();
//不需要"for update"
ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1");
if (rs.next())
{
java.sql.Clob clob = rs.getClob("CLOBATTR");
Reader inStream = clob.getCharacterStream();
char[] c = new char[(int) clob.length()];
inStream.read(c);
//data是读出并需要返回的数据,类型是String
data = new String(c);
inStream.close();
}
inStream.close();
con.commit();
con.close();
需要注意的地方:
1、java.sql.Blob、oracle.sql.BLOB、weblogic.jdbc.vendor.oracle.OracleThinBlob几种类型的区别
2、java.sql.Clob、oracle.sql.CLOB、weblogic.jdbc.vendor.oracle.OracleThinClob几种类型的区别
公司项目中的用法(博客):
入库:先插一个oracle.sql.CLOB.empty_lob()进去,然后
String updateBaseSourceSql = "select content from mb_baseSource where id = ? for update";
conn.setAutoCommit(false);
ps = conn.prepareStatement(updateBaseSourceSql);
ps.setLong(1, result);
ResultSet rs = ps.executeQuery();
oracle.sql.CLOB clob = null;
if (rs.next()) {
clob = (oracle.sql.CLOB) rs.getClob(1);
}
Writer wr = clob.getCharacterOutputStream();
wr.write(baseSource[4]);
wr.flush();
wr.close();
rs.close();
ps.close();
conn.commit();
出库:
findBaseSourceSql = "select content from mb_baseSource where id = ?";
ps = conn.prepareStatement(findBaseSourceSql);
ps.setLong(1, sourceID);
rs = ps.executeQuery();
if (rs.next()) {
CLOB clob = (oracle.sql.CLOB) rs.getClob(1);
if (clob != null) {
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while (s != null) {
result[6] += s;
s = br.readLine();
}
}
}
rs.close();
ps.close();
conn.close();
***********************************
某个表A 有个子段CMMT是CLOB类型的。
System.out.println(rs.getClob("CMMT"));
打印结果为什么是：oracle.sql.CLOB@21f46a
因为它是一个对象
你要把它解析成你需要的类型
使用getAsiiStream把CLOB值物化为一个包含Ascii字节的字符流。
① 使用getAsiiStream把CLOB值物化为一个包含Ascii字节的字符流。
Clob notes = rs.getClob(“NOTES”);
java.io.InputStream in = notes.getAsciiStream();
byte b = in.read();
② 使用getCharacterStream把CLOB值物化为一个Unicode字符流。
Clob notes = rs.getClob(“NOTES”);
java.io.Reader reader = notes.getCharacterStream();
int c = reader.read();
//
③使用getSubString将CLOB值的全部或者部分化为一个String对象。
Clob notes = rs.getClob(4);
String substring= notes.getSubString(10,5);
或者
long len =notes.length();
String substring = notes.getSubString(1,(int)len);
*****************************
JDBC存取ORACLE大型数据对象LOB几种情况的示范类
JDBC存取ORACLE大型数据对象LOB几种情况的示范类。
import java.io.*;
import java.util.*;
import java.sql.*;
public class LobPros
{
/**
* ORACLE驱动程序
*/
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
/**
* ORACLE连接用URL
*/
private static final String URL = "jdbc:oracle:thin:@test2000:1521:orac";
/**
* 用户名
*/
private static final String USER = "user";
/**
* 密码
*/
private static final String PASSWORD = "pswd";
/**
* 数据库连接
*/
private static Connection conn = null;
/**
* SQL语句对象
*/
private static Statement stmt = null;
/**
* @roseuid 3EDA089E02BC
*/
public LobPros()
{
}
/**
* 往数据库中插入一个新的CLOB对象
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04A902BC
*/
public static void clobInsert(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 插入一个空的CLOB对象 */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");
/* 查询此CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
/**
* 修改CLOB对象（是在原CLOB对象基础上进行覆盖式的修改）
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04B60367
*/
public static void clobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 进行覆盖式修改 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
/**
* 替换CLOB对象（将原CLOB对象清除，换成一个全新的CLOB对象）
*
* @param infile - 数据文件
* @throws java.lang.Exception
*
@roseuid 3EDA04BF01E1
*/
public static void clobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原CLOB对象 */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 更新数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
/**
* CLOB对象读取
*
* @param outfile - 输出文件名
* @throws java.lang.Exception
* @roseuid 3EDA04D80116
*/
public static void clobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询CLOB对象 */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
while (rs.next()) {
/* 获取CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
out.close();
in.close();
}
} catch (Exception ex) {
conn.rollback();
throw ex;
}
/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
/**
* 向数据库中插入一个新的BLOB对象
*
* @param infile - 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04E300F6
*/
public static void blobInsert(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(fal
*******************************
oracle+jsp中blob类型存储大文本问题的解决
oracle 存储大文本一直是一个棘手的问题。
一、存数据库：
<%@page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<%
//定义变量
java.sql.Connection conn; //数据库连接对象
String sql;
long id;
ResultSet rs;
Statement stmt,stmt1;
java.sql.DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); //装载JDBC驱动程序
conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.2:1521:lqxm","lqxm","lqxm"); //连接数据库
request.setCharacterEncoding("GBK");
String title = request.getParameter("title");
String content = request.getParameter("content");
String sort = request.getParameter("sort");
String type = request.getParameter("type");
String rq = request.getParameter("rq");
String qy = request.getParameter("qy");
//插入数据,此时blob字段中插入的是空值
sql="insert into t_flfg (xlh,title,content,rq,sort,type,qy) ";
sql=sql+"Values(FLFG_SEQ.NEXTVAL,'" + title + "',empty_clob(),'" + rq + "','" + sort + "','" + type + "','" + qy + "')";
stmt=conn.createStatement();
stmt.executeUpdate(sql);
conn.commit();
conn.setAutoCommit(false);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//取得刚才插入的ID
sql="select max(xlh) as xlh from t_flfg ";
rs=stmt.executeQuery(sql);
if(rs.next()) {
id=rs.getInt("xlh");
}
rs.close();
sql = "select content from t_flfg where xlh="+id+" for update";
rs = stmt.executeQuery(sql);
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
clob.putString(1, content);
sql = "update t_flfg set content=? where xlh=" + id + ""; //将大文本更新进去，呵呵
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setClob(1, clob);
pstmt.executeUpdate();
}
conn.commit();
stmt.close();
conn.close();
%>
二、检索显示数据：
<%
ResultSet rs = flfgSave.searchOneInfo(request.getParameter("xlh")); //查询数据库获取记录集
rs.next();
int y;
String content = "";
oracle.sql.CLOB clob1;
char ac[] = new char[299];
String title = rs.getString("title");
clob1 = (oracle.sql.CLOB)rs.getObject("content");
Reader reader = clob1.getCharacterStream();
while((y = reader.read(ac, 0, 299)) != -1)
content += new String(ac, 0, y); //这就是取出来的大文本
%>
**************************
两种方法向Oracle数据库中写入大对象
使用put 方法写CLOB列，使用put方法写CLOB列可用以下10个步骤:
1) 将LOB列初始化以便设置LOB定位器。
在向LOB写内容之前必须先将LOB列初始化。使用EMPTY_CLOB()函数对CLOB列进行初始化。
// step 1: initialize the LOB column to set the LOB locator
myStatement.executeUpdate(
"INSERT INTO clob_content(file_name, clob_column) " +
"VALUES ('" + fileName + "', EMPTY_CLOB())"
);
其中fileName为要写入LOB的文件目录和文件名
2)将包含LOB定位器的行读入结果集。
// step 2: retrieve the row containing the LOB locator
ResultSet clobResultSet = myStatement.executeQuery(
"SELECT clob_column " +
"FROM clob_content " +
"WHERE file_name = '" + fileName + "' " +
"FOR UPDATE"
);
clobResultSet.next();
3)在Java程序中创建LOB对象，并且从结果集读取LOB定位器。
// step 3: create a LOB object and read the LOB locator
CLOB myClob =
((OracleResultSet) clobResultSet).getCLOB("clob_column");
4)从LOB对象获取LOB的组块尺寸。
// step 4: get the chunk size of the LOB from the LOB object
int chunkSize = myClob.getChunkSize();
5)创建一个缓冲区来存储来自文件的数据块。
// step 5: create a buffer to hold a block of data from the file
char [] textBuffer = new char[chunkSize];
6)创建一个文件对象。
// step 6: create a file object
File myFile = new File(fileName);
7)创建输入流对象来读取文件内容。
// step 7: create input stream objects to read the file contents
FileInputStream myFileInputStream = new FileInputStream(myFile);
InputStreamReader myReader =
new InputStreamReader(myFileInputStream);
BufferedReader myBufferedReader = new BufferedReader(myReader);
8)使用以下的循环读取文件的内容并且将它写到LOB。如果还没有到达文件的末尾:
A)将数据块从文件读入第五步中创建的缓冲区。
B)将缓冲区的内容写到LOB对象。
// step 8: read the file contents and write it to the LOB
long position = 1;
int charsRead;
while ((charsRead = myBufferedReader.read(textBuffer)) != -1) {
// write the buffer contents to myClob using the putChars() method
myClob.putChars(position, textBuffer);
// increment the end position
position += charsRead;
} // end of while
9)执行提交，使修改持久化。
// step 9: perform a commit
myStatement.execute("COMMIT");
10)关闭用于读取文件的对象。
// step 10: close the objects used to read the file
myBufferedReader.close();
myReader.close();
myFileInputStream.close();
2 使用流写CLOB列
第1，2，3，6和7步与采用put方法相同，主要的差异是这里没有提交步骤，因为流到LOB列的内容被直接发送到数据库，并且立即持久化，就不能提交和回退这些修改了。
1.步骤4:从LOB对象获取LOB的缓冲区大小
// step 4: get the buffer size of the LOB from the LOB object
int bufferSize = myClob.getBufferSize();
2.步骤5:创建一个字节缓冲区存储来自文件的数据块
// step 5: create a buffer to hold a block of data from the file
byte [] byteBuffer = new byte[bufferSize];
3.步骤8:创建一个输出流以便读取文件内容
// step 8: create an input stream object and call the appropriate
// LOB object output stream function
OutputStream myOutputStream = myClob.getAsciiOutputStream();
4.步骤9:读取文件的内容并且将它写到LOB
// step 9: while the end of the file has not been reached,
// read a block from the file into the buffer, and write the
// buffer contents to the LOB object via the output stream
int bytesRead;
while ((bytesRead = myFileInputStream.read(byteBuffer)) != -1) {
// write the buffer contents to the output stream
// using the write() method
myOutputStream.write(byteBuffer);
} // end of while
5.步骤10:关闭流对象
// step 10: close the stream objects
myFileInputStream.close();
myOutputStream.close();
1 使用put 方法写CLOB列，使用put方法写CLOB列可用以下10个步骤:
1) 将LOB列初始化以便设置LOB定位器。
在向LOB写内容之前必须先将LOB列初始化。使用EMPTY_CLOB()函数对CLOB列进行初始化。
// step 1: initialize the LOB column to set the LOB locator
myStatement.executeUpdate(
"INSERT INTO clob_content(file_name, clob_column) " +
"VALUES ('" + fileName + "', EMPTY_CLOB())"
);
其中fileName为要写入LOB的文件目录和文件名
2)将包含LOB定位器的行读入结果集。
// step 2: retrieve the row containing the LOB locator
ResultSet clobResultSet = myStatement.executeQuery(
"SELECT clob_column " +
"FROM clob_content " +
"WHERE file_name = '" + fileName + "' " +
"FOR UPDATE"
);
clobResultSet.next();
3)在Java程序中创建LOB对象，并且从结果集读取LOB定位器。
// step 3: create a LOB object and read the LOB locator
CLOB myClob =
((OracleResultSet) clobResultSet).getCLOB("clob_column");
4)从LOB对象获取LOB的组块尺寸。
// step 4: get the chunk size of the LOB from the LOB object
int chunkSize = myClob.getChunkSize();
5)创建一个缓冲区来存储来自文件的数据块。
// step 5: create a buffer to hold a block of data from the file
char [] textBuffer = new char[chunkSize];
6)创建一个文件对象。
// step 6: create a file object
File myFile = new File(fileName);
7)创建输入流对象来读取文件内容。
// step 7: create input stream objects to read the file contents
FileInputStream myFileInputStream = new FileInputStream(myFile);
InputStreamReader myReader =
new InputStreamReader(myFileInputStream);
BufferedReader myBufferedReader = new BufferedReader(myReader);
8)使用以下的循环读取文件的内容并且将它写到LOB。如果还没有到达文件的末尾:
A)将数据块从文件读入第五步中创建的缓冲区。
B)将缓冲区的内容写到LOB对象。
// step 8: read the file contents and write it to the LOB
long position = 1;
int charsRead;
while ((charsRead = myBufferedReader.read(textBuffer)) != -1) {
// write the buffer contents to myClob using the putChars() method
myClob.putChars(position, textBuffer);
// increment the end position
position += charsRead;
} // end of while
9)执行提交，使修改持久化。
// step 9: perform a commit
myStatement.execute("COMMIT");
10)关闭用于读取文件的对象。
// step 10: close the objects used to read the file
myBufferedReader.close();
myReader.close();
myFileInputStream.close();
2 使用流写CLOB列
第1，2，3，6和7步与采用put方法相同，主要的差异是这里没有提交步骤，因为流到LOB列的内容被直接发送到数据库，并且立即持久化，就不能提交和回退这些修改了。
1.步骤4:从LOB对象获取LOB的缓冲区大小
// step 4: get the buffer size of the LOB from the LOB object
int bufferSize = myClob.getBufferSize();
2.步骤5:创建一个字节缓冲区存储来自文件的数据块
// step 5: create a buffer to hold a block of data from the file
byte [] byteBuffer = new byte[bufferSize];
3.步骤8:创建一个输出流以便读取文件内容
// step 8: create an input stream object and call the appropriate
// LOB object output stream function
OutputStream myOutputStream = myClob.getAsciiOutputStream();
4.步骤9:读取文件的内容并且将它写到LOB
// step 9: while the end of the file has not been reached,
// read a block from the file into the buffer, and write the
// buffer contents to the LOB object via the output stream
int bytesRead;
while ((bytesRead = myFileInputStream.read(byteBuffer)) != -1) {
// write the buffer contents to the output stream
// using the write() method
myOutputStream.write(byteBuffer);
} // end of while
5.步骤10:关闭流对象
// step 10: close the stream objects
myFileInputStream.close();
myOutputStream.close();
****************************************
jsp读取大对象CLOB并生成xml文件示例
<％@ page info="database handler"％>
<％@ page import="java.io.*"％>
<％@ page import="java.net.*"％>
<％@ page import="java.lang.*"％>
<％@ page import="java.util.*"％>
<％@ page import="java.sql.*"％>
<％@ page import="javax.servlet.*"％>
<％@ page import="javax.servlet.http.*"％>
<％@ page import="oracle.sql.CLOB"％>
<％@ page import="oracle.jdbc.driver.OracleResultSet"％>
<html>
<head>
<meta content="text/html; charset=gb2312" http-equiv="content-type">
</head>
<body>
<％
int i=0;
String parID = request.getParameter("id_no");
String strSql;
String content="";
try{
String xmlFile = "/usr/local/tomcat/webapps/vehicles/test.xml";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dburl="jdbc:oracle:thin:@192.168.15.250:1521:ycdb";
Connection con=DriverManager.getConnection(dburl,"training","deep1704sea");
Statement stmt=con.createStatement();
//使用流读取CLOB或BLOB列
strSql = "select xmlgen.getxml('select * from account_holder where id_no=''0001''') from dual ";
ResultSet rs=stmt.executeQuery(strSql);
if(rs.next()){
CLOB clob = ((OracleResultSet)rs).getCLOB(1);
if(clob!=null){
Reader is = clob.getCharacterStream();
BufferedReader br = new BufferedReader(is);
String s = br.readLine();
while(s!=null){
//byte[] temp = s.getBytes("iso-8859-1");
//s = new String(temp);
content += s;
s=br.readLine();
}
}
}
//out.println(content);
//将从数据库中读出的内容写到文件中
FileOutputStream fo = new FileOutputStream(xmlFile);
PrintStream so = new PrintStream(fo);
so.println(content);
so.close();
rs.close();
stmt.close();
con.close();
}catch(Exception e){
out.println(e);
}
％>
</body>
</html>
*********************************