clob oracle 连接_oracle之CLOB处理完整版

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())

{