Java操作Clob,很实用

来源：意榕旅游网

JAVA完全控制Oracle中BLOB、CLOB说明

网络上很多关于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\

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\

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(); 运用Java如何存取Oracle中的CLOB类型字段 import java.sql.*; import java.io.*; import oracle.jdbc.driver.OracleResultSet; import oracle.sql.CLOB; public class TestOracleClob implements Serializable{ public static void main(String[] args) { //create table test (id integer,content clob); System.out.println(\"-------------------insert -----------------\"); try{ Class.forName(\"oracle.jdbc.driver.OracleDriver\"); Connection con=DriverManager.getConnection(\"jdbc:oracle:thin:@fangm:1521:LICSFC\//con.setAutoCommit(false); //Ok 1 String sql=\"insert into test values(1,empty_clob())\"; Statement stmt=con.createStatement(); ResultSet rs=stmt.executeQuery(sql); String sqll=\"select content from test where id=1 for update\"; ResultSet rss=stmt.executeQuery(sqll); if(rss.next()){ CLOB clob = ((OracleResultSet)rss).getCLOB(1); clob.putString(1,\"ddddddddddddddddddddddddddddddddddd\"); sql=\"update test set content=? where id=1\"; PreparedStatement pstmt=con.prepareStatement(sql); pstmt.setClob(1,clob); pstmt.executeUpdate(); pstmt.close(); } //Ok 2 //String sql1=\"insert into test values(2,empty_clob())\"; //ResultSet rs3=stmt.executeQuery(sql1); String sql12=\"insert into test values(?,?)\"; PreparedStatement pstmt1=con.prepareStatement(sql12); pstmt1.setInt(1,2); pstmt1.setClob(2,oracle.sql.CLOB.empty_lob()); pstmt1.executeUpdate(); String sqll2=\"select content from test where id=2 for update\"; ResultSet rss2=stmt.executeQuery(sqll2); if(rss2.next()){ CLOB clob = ((OracleResultSet)rss2).getCLOB(1); clob.putString(1,\"affffffffffdfdfdfdddddddffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffdddfff\"); String sql1=\"update test set content=? where id=2\"; PreparedStatement pstmt=con.prepareStatement(sql1); pstmt.setClob(1,clob); pstmt.executeUpdate(); pstmt.close(); } //con.commit(); rss.close(); rss2.close(); pstmt1.close(); rs.close(); stmt.close(); con.close(); System.out.println(\"-------------insert ok-------------\"); }catch(Exception e){ System.out.println(\"insert:\"+e); } System.out.println(\"-------------------query -----------------\"); try{ String content=\"\"; Class.forName(\"oracle.jdbc.driver.OracleDriver\"); Connection con=DriverManager.getConnection(\"jdbc:oracle:thin:@fangm:1521:LICSFC\Statement stmt=con.createStatement(); String sql=\"select content from test where id=1\"; ResultSet rs=stmt.executeQuery(sql); 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){ content+=s+\s=br.readLine(); } } } rs.close(); stmt.close(); con.close(); System.out.println(\"clob:\"+content); System.out.println(\"-------------query ok-------------\"); }catch(Exception ee){ System.out.println(\"query:\"+ee); } } } Java调用ORACLE中LOB参数的存储过程返回记录集操作表为单表，可自行创建一个包含LOB字段的表。一、创建包 sql 代码 CREATE OR REPLACE PACKAGE PAG_PRODUCT AS TYPE RESULT_CURSOR IS REF CURSOR; /* 保存一条产品信息 P_NAME 产品名称 P_GUIGE 产品规格 P_CHANDI 产品产地 P_TIHUODIAN 产品提货地点 P_SHULIANG 产品数量 P_ZUIXINJIAGE 产品最新价格 P_MIAOSHU 产品描述 P_IMG 产品图片为BLOB类型 P_ID 产品ID */ PROCEDURE SAVEPRODECT_JFJS(P_NAME PRODUCT_JFJS.PRODUCT_NAME%TYPE, P_GUIGE PRODUCT_JFJS.PRODUCT_GUIGE%TYPE, P_CHANDI PRODUCT_JFJS.PRODUCT_CHANDI%TYPE, P_TIHUODIAN PRODUCT_JFJS.PRODUCT_TIHUODIAN%TYPE, P_SHULIANG PRODUCT_JFJS.PRODUCT_SHULIANG%TYPE, P_ZUIXINJIAGE PRODUCT_JFJS.PRODUCT_ZUIXINJIAGE%TYPE, P_MIAOSHU PRODUCT_JFJS.PRODUCT_MIAOSHU%TYPE, P_IMG PRODUCT_JFJS.PRODUCT_IMG%TYPE, P_ID OUT PRODUCT_JFJS.PRODUCT_ID%TYPE); END PAG_PRODUCT; 二、创建包体 sql 代码 CREATE OR REPLACE PACKAGE BODY PAG_PRODUCT AS PROCEDURE SAVEPRODECT_JFJS(P_NAME PRODUCT_JFJS.PRODUCT_NAME%TYPE, P_GUIGE PRODUCT_JFJS.PRODUCT_GUIGE%TYPE, P_CHANDI PRODUCT_JFJS.PRODUCT_CHANDI%TYPE, P_TIHUODIAN PRODUCT_JFJS.PRODUCT_TIHUODIAN%TYPE, P_SHULIANG PRODUCT_JFJS.PRODUCT_SHULIANG%TYPE, P_ZUIXINJIAGE PRODUCT_JFJS.PRODUCT_ZUIXINJIAGE%TYPE, P_MIAOSHU PRODUCT_JFJS.PRODUCT_MIAOSHU%TYPE, P_IMG PRODUCT_JFJS.PRODUCT_IMG%TYPE, P_ID OUT PRODUCT_JFJS.PRODUCT_ID%TYPE) AS V_ID PRODUCT_JFJS.PRODUCT_ID%TYPE; DEST_BLOB PRODUCT_JFJS.PRODUCT_IMG%TYPE; BEGIN SELECT PRODUCT_PK_SEQ.NEXTVAL INTO V_ID FROM DUAL; INSERT INTO PRODUCT_JFJS (PRODUCT_ID, PRODUCT_NAME, PRODUCT_GUIGE, PRODUCT_CHANDI, PRODUCT_TIHUODIAN, PRODUCT_SHULIANG, PRODUCT_ZUIXINJIAGE, PRODUCT_FABUSHIJIAN, PRODUCT_MIAOSHU, PRODUCT_IMG) VALUES (V_ID, P_NAME, P_GUIGE, P_CHANDI, P_TIHUODIAN, P_SHULIANG, P_ZUIXINJIAGE, SYSDATE, P_MIAOSHU, EMPTY_BLOB()); P_ID := V_ID; COMMIT; IF P_IMG IS NOT NULL THEN SELECT PRODUCT_JFJS.PRODUCT_IMG INTO DEST_BLOB FROM PRODUCT_JFJS WHERE PRODUCT_JFJS.PRODUCT_ID = P_ID FOR UPDATE; DBMS_LOB.COPY(DEST_BLOB, P_IMG, DBMS_LOB.GETLENGTH(P_IMG)); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; END PAG_PRODUCT; SQL中若要添加LOB信息需要先新增一个空的LOB，调用EMPTY_BLOB()或EMPTY_CLOB()函数。因此，在上例中我们INSERT的时候调用的是EMPTY_BLOB()，保存一个空的BLOB。然后再用LOB的处理函数来操作相关LOB。PK是通过SQL语句 sql 代码 --调用序列给PK赋值 SELECT PRODUCT_PK_SEQ.NEXTVAL INTO V_ID FROM DUAL; 而产生的。最后调用函数DBMS_LOB.COPY()完成最终的赋值操作。三、JAVA调用存储过程以下为JAVA调用该过程的代码 java 代码 /** * 添加一个产品 * @param PRODUCT_NAME 产品名称 * @param PRODUCT_GUIGE 产品规格 * @param PRODUCT_CHANDI 产品产地 * @param PRODUCT_TIHUODIAN 产品提货地点 * @param PRODUCT_SHULIANG 产品数量 * @param PRODUCT_ZUIXINJIAGE 产品最新价格 * @param PRODUCT_MIAOSHU 产品描述 * @param PRODUCT_IMG 产品显示图片,为FILE类型 * @return * @throws SQLException * @throws IOException */ public static Integer saveprodect_jfjs(String PRODUCT_NAME, String PRODUCT_GUIGE, String PRODUCT_CHANDI, String PRODUCT_TIHUODIAN, int PRODUCT_SHULIANG, String PRODUCT_ZUIXINJIAGE, String PRODUCT_MIAOSHU, File PRODUCT_IMG) throws SQLException, IOException { /**首先创建一个ORACLE BLOB对象，注意BLOB类大为大写字母，ConnectionUtil.getCurrentConnection()实际上 * 返回一个java.sql.Connection对象，FileUtil.getBytes(PRODUCT_IMG)返回一个File类型的byte数组，可用 * 自己熟悉的方式来获得相关信息，这里是LIFERAY的封装。 */ BLOB blob = new BLOB((OracleConnection) ConnectionUtil .getCurrentConnection(), FileUtil.getBytes(PRODUCT_IMG)); //创建一个READ操作的流 InputStream inputStream = new FileInputStream(PRODUCT_IMG); Integer p_id = null; CallableStatement cstmt = null; //存储过程调用，调用saveprodect_jfjs过程 cstmt = ConnectionUtil .prepareCall(\"PAG_PRODUCT.saveprodect_jfjs(?,?,?,?,?,?,?,?,?)\"); //参数设定 cstmt.setString(1, PRODUCT_NAME); cstmt.setString(2, PRODUCT_GUIGE); cstmt.setString(3, PRODUCT_CHANDI); cstmt.setString(4, PRODUCT_TIHUODIAN); cstmt.setInt(5, PRODUCT_SHULIANG); cstmt.setString(6, PRODUCT_ZUIXINJIAGE); cstmt.setString(7, PRODUCT_MIAOSHU); //传递存储过程的BLOB参数 cstmt.setBinaryStream(8, inputStream, (int) blob.getLength()); //注册返回值，返回保存后的产品PK值 cstmt.registerOutParameter(9, Types.INTEGER); //调用执行 cstmt.execute(); //获得返回值 p_id = (Integer) cstmt.getObject(9); return p_id; } END--

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文