JAVA完全控制Oracle中BLOB、CLOB说明
网络上很多关于JAVA对Oracle中BLOB、CLOB类型字段的操作说明，有的不够全面，有的不够准确，甚至有的简直就是胡说八道。最近的项目正巧用到了这方面的知识，在这里做个总结。
环境：
Database: Oracle 9i
App Server: BEA Weblogic 8.14
表结构：
CREATE TABLE TESTBLOB (ID Int, NAMEVarchar2(20), BLOBATTR Blob)
CREATE TABLE TESTBLOB (ID Int, NAMEVarchar2(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 whereID=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 whereID=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 whereID=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 whereID=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 whereID=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 whereID=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 = "selectcontent 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 contentfrom 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","SFC","SFC"); //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","SFC","SFC"); 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-- |
