Oracle8i对Blob和Clob类型的操作

环境：
JDK:1.4
Database: Oracle 9i
App Server: Apache Tomcat 5.0
表结构：
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、入库(JDBC方式)
package test;
import java.sql.*;
import java.io.*;
public class BlobTest1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "oracle");
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");
long sum=0;
if (rs.next())
{
//得到java.sql.Blob对象后强制转换为oracle.sql.BLOB
oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR");
OutputStream outStream = blob.getBinaryOutputStream();

InputStream in = new FileInputStream("d:/aaa.rar");
byte[] data = new byte[1024*4];
//in.read(data);输入流读取数据，返回读取字节数
int len;
//data是传入的byte(字节)数组，定义：byte[] data
while((len = in.read(data))!=-1){
sum=sum+len;
outStream.write(data, 0, len);//输出流写入数据库
outStream.flush();
}
System.out.println();
outStream.close();
}

con.commit();
con.close();
System.out.println("写入完毕\n文件大小"+sum+"字节");
}
}

2、出库(JDBC方式)
package test;
import java.sql.*;
import java.io.*;
public class BlobTest2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "oracle");
con.setAutoCommit(false);
Statement st = con.createStatement();
//不需要"for update"
ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1");
con.commit();
long sum=0;//存放文件大小的变量
if (rs.next())
{
java.sql.Blob blob = rs.getBlob("BLOBATTR");
InputStream inStream = blob.getBinaryStream();
byte[] data = new byte[1024*4];//data是读出并需要返回的数据，类型是byte[]
FileOutputStream outStream = new FileOutputStream("d:/bbb.rar");
int len;
while((len = inStream.read(data))!=-1){
sum+=len;
outStream.write(data, 0, len);
outStream.flush();
}
System.out.println();
outStream.close();
inStream.close();
}
con.close();
System.out.println("读出完毕\n文件大小:"+sum+"字节");
}
}

3、入库(JNDI方式)
待整理……
4、出库(JNDI方式)
待整理……


二、CLOB操作

1、入库(JDBC方式)
package test;

import java.sql.*;
import java.io.*;

public class ClobTest1 {
public static void main(String[] args) throws ClassNotFoundException,
SQLException, IOException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "oracle");
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();

Reader in = new FileReader("d:/aaa.txt");
if (in.ready()) {
int len = 0;
int sum = 0;
char[] c = new char[1024*2];
while ((len = in.read(c)) != -1) {
sum+=len;
outStream.write(c, 0, len);
outStream.flush();
}
System.out.println("总共" + c.length + "个字符");

/*
String data;//data是传入的字符串，定义：String data.若是jsp文本域内容，可直接用data接收request.getParameter("mytxtarea")
data="不抛弃，不放弃！";//假设Servlet取到了
char[] ch = data.toCharArray();
outStream.write(ch, 0, ch.length);
outStream.flush();
System.out.println("总共" + ch.length + "个字符");
outStream.close();
*/
}
}
con.commit();
con.close();
}

}

2、出库(JDBC方式)
package test;

import java.io.IOException;
import java.io.Reader;
import java.sql.*;

public class ClobTest2 {
public static void main(String[] args) throws ClassNotFoundException,
SQLException, IOException {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "hr", "oracle");
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
String data = new String(c);
inStream.close();
System.out.println(data);
}
con.commit();
con.close();
}

}