oracle9i支不支持clob,java对oracle9i中CLOB类型的操作

深藏阁楼爱情的钟 2022-10-21 04:57 179阅读 0赞

这是一个完整的使用java对oracle9i的操作，其实，使用oracle10g后，操作将非常方便，但现在由于仍在使用oracle9i，所以不得不忍受这种痛苦。现将对oracle9i中CLOB类型的操作，完整记录于此，权作自己的学习笔记吧。

第一种情况：没有服务器，单独使用java直接连接数据库时：

package com.hyq.test;

import java.sql.SQLException;

import java.sql.\*;

import java.sql.ResultSet;

import oracle.sql.CLOB;

public class ManageOracleCLOB \{

public ManageOracleCLOB() \{

public static void main(String\[\] args) \{

String driver = "oracle.jdbc.driver.OracleDriver";

String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: HYQ";

Statement stmt = null;

ResultSet rs = null;

Connection conn = null;

ManageOracleCLOB manageOracleCLOB = null;

try \{

Class.forName(driver);

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

conn = DriverManager.getConnection(strUrl, "HYQ", "HYQ");

manageOracleCLOB = new ManageOracleCLOB();

//获取下一个标识符id

long nextId = manageOracleCLOB.getNextId(conn);

//      long nextId = 1;

//添加记录

manageOracleCLOB.add(conn,nextId);

//修改记录

//      manageOracleCLOB.modify(conn,nextId);

//获取记录

//      manageOracleCLOB.find(conn,nextId);

//删除记录

//      manageOracleCLOB.delete(conn,nextId);

conn.close();

catch (SQLException ex2) \{

ex2.printStackTrace();

catch (Exception ex2) \{

ex2.printStackTrace();

finally \{

try \{

if (rs != null) \{

rs.close();

if (stmt != null) \{

stmt.close();

if (conn != null) \{

conn.close();

catch (SQLException ex1) \{

/\*\*

\* add

\* @param conn Connection

\* @param hyq long

\*/

public void add(Connection conn, long hyq) \{

CLOB ad = null;

PreparedStatement pstmt = null;

try \{

conn.setAutoCommit(false);

pstmt = conn.prepareStatement("INSERT INTO S\_PROCLAIM\_TEST (PROCLAIM\_ID,PROCLAIM\_TITLE,PROCLAIM\_CONTENT) VALUES (?,?,'" +

CLOB.empty\_lob() + "')");

pstmt.setLong(1, hyq);

pstmt.setString(2, "第一次的尝试！");

pstmt.executeUpdate();

pstmt.close();

pstmt = conn.prepareStatement(

"select PROCLAIM\_CONTENT from S\_PROCLAIM\_TEST where PROCLAIM\_ID= ? for update");

pstmt.setLong(1, hyq);

ResultSet rset = pstmt.executeQuery();

if (rset.next()) \{

ad = (CLOB) rset.getClob(1);

pstmt = conn.prepareStatement(

"UPDATE S\_PROCLAIM\_TEST SET PROCLAIM\_CONTENT=? WHERE PROCLAIM\_ID=?");

String hyqTa = "hyq冬季到台北来看雨，别在异乡哭泣。也许有一天上天睁开了眼，看到你哭泣的脸和早已凋谢了的容颜，本想安慰你伤痛的内心却忍不住泪流满面。你是风儿它是沙，疯疯癫癫到天涯。";

ad.putString(1, hyqTa);

pstmt.setClob(1, ad);

pstmt.setLong(2, hyq);

pstmt.executeUpdate();

pstmt.close();

conn.commit();

ad = null;

catch (SQLException ex) \{

ex.printStackTrace();

finally \{

if (pstmt != null) \{

try \{

pstmt.close();

catch (SQLException ex1) \{

pstmt = null;

/\*\*

\* modify

\* @param conn Connection

\* @param hyq long

\*/

public void modify(Connection conn, long hyq) \{

CLOB ad = null;

PreparedStatement pstmt = null;

try \{

conn.setAutoCommit(false);

pstmt = conn.prepareStatement(

"select PROCLAIM\_CONTENT from S\_PROCLAIM\_TEST where PROCLAIM\_ID= ? for update");

pstmt.setLong(1, hyq);

ResultSet rset = pstmt.executeQuery();

if (rset.next()) \{

ad = (CLOB) rset.getClob(1);

pstmt.close();

pstmt = conn.prepareStatement(

"UPDATE S\_PROCLAIM\_TEST SET PROCLAIM\_CONTENT=? WHERE PROCLAIM\_ID=?");

String hyqTa = "这两天做一个小东西，要求将一个文件打包到.war文件中，然后还要将这个文件从.war包中读取出来，并在服务器硬盘上重新建一个新的文件。本来很简单的东西，却浪费了不少时间，写出来，做一下笔记，同时给那些需要的朋友提供一下参考，下面是我写的一个示例，在servlet中写的";

ad.putString(1, hyqTa);

pstmt.setClob(1, ad);

pstmt.setLong(2, hyq);

pstmt.executeUpdate();

pstmt.close();

conn.commit();

ad = null;

catch (SQLException ex) \{

ex.printStackTrace();

finally \{

if (pstmt != null) \{

try \{

pstmt.close();

catch (SQLException ex1) \{

pstmt = null;

/\*\*

\* find

\* @param conn Connection

\* @param hyq long

\*/

public void find(Connection conn, long hyq) \{

Clob aa = null;

PreparedStatement pstmt = null;

try \{

pstmt = conn.prepareStatement(

"select PROCLAIM\_CONTENT from S\_PROCLAIM\_TEST where PROCLAIM\_ID= ?");

pstmt.setLong(1, hyq);

ResultSet rset2 = pstmt.executeQuery();

if (rset2.next()) \{

aa = rset2.getClob(1);

conn.commit();

String content = aa.getSubString(1, (int) aa.length());

System.out.println("==hyq==getContent===" + content);

catch (SQLException ex) \{

ex.printStackTrace();

finally \{

if (pstmt != null) \{

try \{

pstmt.close();

catch (SQLException ex1) \{

pstmt = null;

/\*\*

\* delete

\* @param conn Connection

\* @param hyq long

\*/

public void delete(Connection conn, long hyq) \{

PreparedStatement pstmt = null;

try \{

pstmt = conn.prepareStatement(

"DELETE FROM S\_PROCLAIM\_TEST WHERE PROCLAIM\_ID = ?");

pstmt.setLong(1, hyq);

pstmt.executeQuery();

catch (SQLException ex) \{

ex.printStackTrace();

finally \{

if (pstmt != null) \{

try \{

pstmt.close();

catch (SQLException ex1) \{

pstmt = null;

/\*\*

\* getNextId

\* @param conn Connection

\* @return long

\*/

public long getNextId(Connection conn) \{

long nextProclaimId = 0;

PreparedStatement pstmt = null;

ResultSet rs = null;

String sqlStr = "SELECT MAX(PROCLAIM\_ID) FROM  S\_PROCLAIM\_TEST";

try \{

pstmt = conn.prepareStatement(sqlStr);

rs = pstmt.executeQuery();

if (rs != null && rs.next()) \{

if (rs.getString(1) != null) \{

nextProclaimId = Long.parseLong(rs.getString(1))+1;

else \{

nextProclaimId = 1;

else \{

nextProclaimId = 1;

catch (SQLException ex) \{

ex.printStackTrace();

finally \{

if (rs != null) \{

try \{

rs.close();

catch (SQLException ex1) \{

rs = null;

if (pstmt != null) \{

try \{

pstmt.close();

catch (SQLException ex2) \{

pstmt = null;

return nextProclaimId;

第二种：使用weblogic服务器时出现的问题

使用webligic时，在上面的方法中(CLOB) rset.getClob(1);这一行会报错java.lang.ClassCastException  ，说是类型转换错误。很郁闷。修改结果是，去掉CLOB类型，使用weblogic自带的weblogic.jar(可以在weblojic的安装目录下找到)，使用weblogic的weblogic.jdbc.vendor.oracle.OracleThinClob。代码如下：

weblogic.jdbc.vendor.oracle.OracleThinClob clob = null;

ad= (OracleThinClob)tempRset.getClob(1);//替换掉ad = (CLOB) rset.getClob(1);

然后添加时还要进行类型转换：pstmt.setClob(1,(Clob)ad);

这样一切就OK了！

真是麻烦之极！

第三种：使用tomcat连接池的问题

在使用jbuilder编译时，会出现一个classes12.jar，把lib下的classes12.jar(oracle包)删除就可以了。