weblogic.jdbc.vendor.oracle.oraclethinblob,weblogic访问ORACLE，thin连接方式存取BLOB和CLOB字段（原创）...

weblogic服务器中，在通过datasourse获取connection，BLOB字段取出来的就不是oracle.sql.BLOB类型，而是weblogic封装过的OracleThinBlob类型，执行BLOB

oBLOB = (BLOB)

rs.getBlob(1);所以cast的时候肯定会出错，出现ClassCaseException异常。

一，在使用JDBC直接连接的时候(代码引用dev2dev.bea.com.cn)

java.sql.Blob myBlob=

null;

java.sql.Clob myClob=

null;

Connection conn =

null;

Properties props = new

Properties();

props.put("user",user);

props.put("password",

password);

props.put("server",server);

Driver myDriver =

(Driver)

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

conn =

myDriver.connect("jdbc:oracle:thin:" , props);

Statement crstmt =

conn.createStatement();

crstmt.execute("create table lobtest (id int, blobcol Blob, clobcol

Clob)");//建表

stmt.execute("insert into lobtest values

(44,EMPTY_BLOB(),EMPTY_CLOB())");//插入数据

stmt.execute("select * from lobtest where id=44");//取数据

ResultSet rs =

stmt.getResultSet();

while (

rs.next() ) {

myBlob =

rs.getBlob("blobcol");

}

// Create a byte array and store some data in

it

System.out.println("\nCreating the following byte

array:");

int STREAM_SIZE = 10;

byte[] b = new byte[STREAM_SIZE];

for (int i=0; i < STREAM_SIZE; i++) {

b[i] = (byte)(40 + (i%20)); // range 40-60

System.out.println("byte[" + i + "] = " +

b[i]);

}

// Write the byte array to a stream and store it in

the Blob column

System.out.println

("\nWriting the byte array to a stream" +

" and storing it in the table as a blob...");

InputStream is = new ByteArrayInputStream(b);

OutputStream os = ((oracle.sql.BLOB)

myBlob).getBinaryOutputStream();

byte[] inBytes = new byte[STREAM_SIZE];

int numBytes = is.read(inBytes);

// write the input stream to the output stream

while (numBytes > 0) {

os.write(inBytes, 0, numBytes);

numBytes = is.read(inBytes);

}

// The flush() method causes the data to be written

to the table

os.flush();

//read back the blob

System.out.println("\nReading the blob back from

the table and displaying:");

Statement readblob = conn.createStatement();

readblob.execute("select * from lobtest where id=44");

ResultSet rsreadblob = readblob.getResultSet();

// read the blob into a byte array and display

byte[] r = new byte[STREAM_SIZE];

while ( rsreadblob.next() ) {

Blob myReadBlob =rsreadblob.getBlob("blobcol");

java.io.InputStream readis = myReadBlob.getBinaryStream();

for (int i=0 ; i < STREAM_SIZE ; i++) {

r[i] = (byte) readis.read();

System.out.println("output [" + i + "] = " + r[i]);

}

}

// create some character data to work with

String ss = "abcdefghijklmnopqrstuvwxyz";

System.out.println("\nCreated the following string to be stored

as a clob:\n" +

ss);

// ============== Manipulating the Clob column

======================

// get a reference to the clob column

stmt.execute("select * from lobtest where id=44");

ResultSet crs = stmt.getResultSet();

while ( crs.next() ) {

myClob = crs.getClob("clobcol");

java.io.OutputStream osss =

((oracle.sql.CLOB) myClob).getAsciiOutputStream();

byte[] bss = ss.getBytes("ASCII");

osss.write(bss);

osss.flush();

}

conn.commit();

// read back the clob

System.out.println("\nReading the clob back from the table and

displaying:");

Statement readclob = conn.createStatement();

readclob.execute("select * from lobtest where id=44");

ResultSet rsreadclob = readclob.getResultSet();

// read the clob in as and ASCII stream, write to a character

array, and display

while ( rsreadclob.next() ) {

Clob myReadClob =rsreadclob.getClob("clobcol");

java.io.InputStream readClobis =

myReadClob.getAsciiStream();

char[] c = new char[26];

for (int i=0 ; i < 26; i++) {

c[i] = (char) readClobis.read();

System.out.println("output [" + i + "] = " + c[i]);

}

}

// Drop the table and clean up connections

System.out.println("\nDropping table...");

Statement dropstmt = conn.createStatement();

dropstmt.execute("drop table lobtest");

System.out.println("Table dropped.");

} catch (Exception e) {

System.out.println("Exception was thrown: " +

e.getMessage());

throw e;

} finally {

try {

if (conn != null)

conn.close();

} catch (SQLException sqle) {

System.out.println("SQLException was thrown: " +

sqle.getMessage());

throw sqle;

}

}

}

二，使用数据源的情况，并且使用oracle

thin驱动方式

为了使代码适应各种应用服务器，可以对代码这样修改

在weblogic中找到这个jar包加入到工程项目中。weblogic.jar

在处理BLOB字段的类中添加引用import

weblogic.jdbc.vendor.oracle.OracleThinBlob;

取得BLOB字段的时候原来的处理方式是BLOB

oBLOB = (BLOB) rs.getBlob(j + 1);这里的BLOB是

oracle.sql.BLOB。在weblogic服务器下这样使用就会报ClassCaseException，因为在强制类型转换的时候，rs.getBlob(j

+ 1)得到的是OracleThinBlob类型。所以代码更改为

if(rs.getBlob(j

+

1).getClass().equals(oracle.sql.BLOB.class)) {//except weblogic

BLOB oBLOB = (BLOB) rs.getBlob(j + 1);

}else{//for weblogic

OracleThinBlob oBLOB = (OracleThinBlob) rs.getBlob(j +

1);

附录：引用自javaeye JAVA完全控制Oracle中BLOB CLOB说明

----------厚厚发表于

2006年06月27日

网络上很多关于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几种类型的区别