//Source file: C:\\Rose2Java\\zhsoft\\test\\LobPros.java
package zhsoft.test;
import java.io.*;
import java.util.*;
import java.sql.*;
/**
* 展示JDBC存取ORACLE大型数据对象LOB几种情况的示范类
*
* @author 雨亦奇([email]zhsoft88@sohu.com[/email])
* @version 1.0
* @since 2003.05.28
*/
public class LobPros
{
/**
* ORACLE驱动程序
*/
private static final String DRIVER = “oracle.jdbc.driver.OracleDriver”;
/**
* ORACLE连接用URL
*/
private static final String URL = “jdbc:oracle:thin:@10.11.25.66:1521:wjc”;
/**
* 用户名
*/
private static final String USER = “wjc”;
/**
* 密码
*/
private static final String PASSWORD = “anyue”;
/**
* 数据库连接
*/
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(false);
try {
/* 插入一个空的BLOB对象 */
stmt.executeUpdate(“INSERT INTO TEST_BLOB VALUES (‘222′, EMPTY_BLOB())”);
/* 查询此BLOB对象并锁定 */
ResultSet rs = stmt.executeQuery(“SELECT BLOBCOL FROM TEST_BLOB WHERE ID=’222′ FOR UPDATE”);
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(“BLOBCOL”);
/* 向BLOB对象中写入数据 */
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(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);
}
/**
* 修改BLOB对象（是在原BLOB对象基础上进行覆盖式的修改）
*
* @param infile – 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA04E90106
*/
public static void blobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询BLOB对象并锁定 */
ResultSet rs = stmt.executeQuery(“SELECT BLOBCOL FROM TEST_BLOB WHERE ID=’222′ FOR UPDATE”);
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(“BLOBCOL”);
/* 向BLOB对象中写入数据 */
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(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);
}
/**
* 替换BLOB对象（将原BLOB对象清除，换成一个全新的BLOB对象）
*
* @param infile – 数据文件
* @throws java.lang.Exception
* @roseuid 3EDA0505000C
*/
public static void blobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 清空原BLOB对象 */
stmt.executeUpdate(“UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID=’222′”);
/* 查询此BLOB对象并锁定 */
ResultSet rs = stmt.executeQuery(“SELECT BLOBCOL FROM TEST_BLOB WHERE ID=’222’ FOR UPDATE”);
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(“BLOBCOL”);
/* 向BLOB对象中写入数据 */
BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream());
BufferedInputStream in = new BufferedInputStream(new FileInputStream(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);
}
/**
* BLOB对象读取
*
* @param outfile – 输出文件名
* @throws java.lang.Exception
* @roseuid 3EDA050B003B
*/
public static void blobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);
try {
/* 查询BLOB对象 */
ResultSet rs = stmt.executeQuery(“SELECT BLOBCOL FROM TEST_BLOB WHERE ID=’222′”);
while (rs.next()) {
/* 取出此BLOB对象 */
oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(“BLOBCOL”);
/* 以二进制形式输出 */
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile));
BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream());
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);
}
/**
* 建立测试用表格
* @throws Exception
*/
public static void createTables() throws Exception {
try {
stmt.executeUpdate(“CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)”);
stmt.executeUpdate(“CREATE TABLE TEST_BLOB ( ID NUMBER(3), BLOBCOL BLOB)”);
} catch (Exception ex) {
}
}
/**
* @param args – 命令行参数
* @throws java.lang.Exception
* @roseuid 3EDA052002AC
*/
public static void main(String[] args) throws Exception
{
/* 装载驱动,建立数据库连接 */
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL,USER,PASSWORD);
stmt = conn.createStatement();
/* 建立测试表格 */
createTables();
/* CLOB对象插入测试 */
clobInsert(“c:/clobInsert.txt”);
clobRead(“c:/clobInsert.out”);
/* CLOB对象修改测试 */
clobModify(“c:/clobModify.txt”);
clobRead(“c:/clobModify.out”);
/* CLOB对象替换测试 */
clobReplace(“c:/clobReplace.txt”);
clobRead(“c:/clobReplace.out”);
/* BLOB对象插入测试 */
blobInsert(“c:/blobInsert.doc”);
blobRead(“c:/blobInsert.out”);
/* BLOB对象修改测试 */
blobModify(“c:/blobModify.doc”);
blobRead(“c:/blobModify.out”);
/* BLOB对象替换测试 */
blobReplace(“c:/blobReplace.doc”);
blobRead(“c:/bolbReplace.out”);
/* 关闭资源退出 */
conn.close();
System.exit(0);
}
}
==================
conn.setAutoCommit(false);
//插入一个空对象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.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);
}
读取
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();
写入
String content = “”;
Connection con = getCon();
Statement stmt = con.createStatement();
ResultSet rs1 = stmt.executeQuery(sql1);
while (rs1.next()){
CLOB clob = ( (OracleResultSet) rs1).getCLOB(2);
if (clob != null){
BufferedReader br = new BufferedReader(clob.getCharacterStream());
String s = br.readLine() + “\n”;
while (s != null){
//byte[] b = s.getBytes(“ASCII”);
content += s + “\n”;
s = br.readLine();
}
}
}
读取
BufferedInputStream bi = new BufferedInputStream(clob.getAsciiStream());
int len = (int) clob.length();
byte[] by = new byte[len];
int i;
while (-1 != (i = bi.read(by, 0, by.length))) {
bi.read(by, 0, i);
}
String clobValue = new String(by);
bi.close();
写入
oracle.sql.CLOB clob=(oracle.sql.CLOB)rs1.getClob(1);
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
String content=”1234″;//假定这是新闻的内容，当然可以也可以是其他的内容
out.write(content,0,content.length());
out.close();
读取
oracle.sql.CLOB clob=(oracle.sql.CLOB)rs1.getClob(1);
BufferedReader in=new BufferedReader(clob.getCharacterStream());
StringWriter out=new StringWriter();
int c;
while((c=in.read())!=-1){
out.write(c);
String content=out.toString();
System.out.println (content);//输出CLOB内容
写入
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();
读取
clob=rs.getCLOB(“”);
InputStream input=clob.getAsciiStream();
int len=(int)clob.length();
byte[] by = new byte[len];
int i;
while(-1!=(i=input.read(by,0,by.length))){
input.read(by,0,i);
}
rtn = new String(by);
getCharacterStream()读取Oracle的CLOB字段
getCharacterOutputStream()将数据写入Oracle的CLOB字段
setAsciiStream()
setBinaryStream()
setUnicodeStream()
new StringBufferInputStream(String s),
需要注意的地方：
1、java.sql.Blob、oracle.sql.BLOB、weblogic.jdbc.vendor.oracle.OracleThinBlob几种类型的区别
2、java.sql.Clob、oracle.sql.CLOB、weblogic.jdbc.vendor.oracle.OracleThinClob几种类型的区别
获取结果集的信息:
大多数情况下编程人员对数据库结构是了解的,可以知道结果集中各列的情况,但有时并
不知道结果集中有哪些列,是什么类型.这时可以通过getMetaData()来获取结果集的情况:
ResulSetMetaData rsmd = rs.getMetaData();
rsmd.getColumnCount()返回列的个数.
getColumnLabel(int)返回该int所对应的列的显示标题
getColumnName(int)返回该int所对应的列的在数据库中的名称.
getColumnType(int)返回该int所对应的列的在数据库中的数据类型.
getColumnTypeName(int)返回该int所对应的列的数据类型在数据源中的名称.
isReadOnly(int)返回该int所对应的列是否只读.
isNullable(int)返回该int所对应的列是否可以为空
ResultSet rsclob = stmt1.executeQuery(
“select htmlhead,htmlbody from ls_file where fileid=” + fileid +
” FOR UPDATE”);
while (rsclob.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob_htmlhead = (oracle.sql.CLOB) rsclob.getClob(1);
oracle.sql.CLOB clob_htmlbody = (oracle.sql.CLOB) rsclob.getClob(2);
/* 向CLOB对象中写入数据 */
byte[] b_htmlhead = ddlhead.getBytes(“ASCII”);
byte[] b_htmlbody = ddlfoot.getBytes(“ASCII”);
java.io.OutputStream os_htmlhead = ( (oracle.sql.CLOB) clob_htmlhead).
getAsciiOutputStream();
java.io.OutputStream os_htmlbody = ( (oracle.sql.CLOB) clob_htmlbody).
getAsciiOutputStream();
os_htmlhead.write(b_htmlhead);
os_htmlbody.write(b_htmlbody);
os_htmlhead.flush();
os_htmlbody.flush();
//读取clob字段，如下：
ResultSet rsreadclob = stmt.executeQuery(
“select htmlhead,htmlbody from ls_file where fileid=20”);
while (rsreadclob.next()) {
Clob myReadhtmlhead = rsreadclob.getClob(1);
Clob myReadhtmlbody = rsreadclob.getClob(2);
java.io.InputStream is_htmlhead = myReadhtmlhead.getAsciiStream();
java.io.InputStream is_htmlbody = myReadhtmlbody.getAsciiStream();
java.io.Reader r_htmlhead = myReadhtmlhead.getCharacterStream();
java.io.Reader r_htmlbody = myReadhtmlbody.getCharacterStream();
if (r_htmlhead != null) {
try {
int i = r_htmlhead.read();
while (i != -1) {
sbclob_head.append( (char) i);
i = r_htmlhead.read();
}
r_htmlhead.close();
} catch (Exception e) {
}
}
if (r_htmlbody != null) {
try {
int ii = r_htmlbody.read();
while (ii != -1) {
sbclob_body.append( (char) ii);
ii = r_htmlbody.read();
}
r_htmlbody.close();
} catch (Exception e) {
}
}
[url]http://edocs.bea.com/wls/docs70/oracle/advanced.html#1158566[/url]
[url]http://www.hibernate.org/56.html[/url]
[url]http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jdbc/clob10g/handlingclobsinoraclejdbc10g.html[/url]