oracle 存储大文本一直是一个棘手的问题。
一、存数据库：
<%@page contentType="text/html; charset=gb2312" language="java" import="java.sql.*" errorPage="" %>
<%
//定义变量
java.sql.Connection conn; //数据库连接对象
String sql;
long id;
ResultSet rs;
Statement stmt,stmt1;
java.sql.DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); //装载JDBC驱动程序
conn = java.sql.DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.2:1521:lqxm","lqxm","lqxm"); //连接数据库
request.setCharacterEncoding("GBK");
String title = request.getParameter("title");
String content = request.getParameter("content");
String sort = request.getParameter("sort");
String type = request.getParameter("type");
String rq = request.getParameter("rq");
String qy = request.getParameter("qy");
//插入数据,此时blob字段中插入的是空值
sql="insert into t_flfg (xlh,title,content,rq,sort,type,qy) ";
sql=sql+"Values(FLFG_SEQ.NEXTVAL,'" + title + "',empty_clob(),'" + rq + "','" + sort + "','" + type + "','" + qy + "')";
stmt=conn.createStatement();
stmt.executeUpdate(sql);
conn.commit();
conn.setAutoCommit(false);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
//取得刚才插入的ID
sql="select max(xlh) as xlh from t_flfg ";
rs=stmt.executeQuery(sql);
if(rs.next()) {
id=rs.getInt("xlh");
}
rs.close();
sql = "select content from t_flfg where xlh="+id+" for update";
rs = stmt.executeQuery(sql);
if (rs.next()) {
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);
clob.putString(1, content);
sql = "update t_flfg set content=? where xlh=" + id + ""; //将大文本更新进去，呵呵
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setClob(1, clob);
pstmt.executeUpdate();
}
conn.commit();
stmt.close();
conn.close();
%>
二、检索显示数据：
<%
ResultSet rs = flfgSave.searchOneInfo(request.getParameter("xlh")); //查询数据库获取记录集
rs.next();
int y;
String content = "";
oracle.sql.CLOB clob1;
char ac[] = new char[299];
String title = rs.getString("title");
clob1 = (oracle.sql.CLOB)rs.getObject("content");
Reader reader = clob1.getCharacterStream();
while((y = reader.read(ac, 0, 299)) != -1)
content += new String(ac, 0, y); //这就是取出来的大文本
oracle之CLOB处理完整版
好域名抢注、高PR值域名出售 → 进入益友论坛 2006-4-20
/**
*
*操作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;
}
}
有关oracle字段类型的相关信息可以查阅oracle技术网。下面摘抄一些有关blob.clob等类型的说明。又便于大家的了解。
字段类型：blob,clob,nclob
说明：三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle 8i实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。
我们所操作的clobtest_table中属性是（字符型id，CLOB型picstr），目前我们假设一个大的字符对象str已经包含了我们需要存入 picstr字段的数据。而且connection对象conn已经建立。以下的例子程序也因为不想占用太多的空间，所以对抛出异常没有写。大家参考一下 api doc。就可以知道该抛出什么异常了，此处仅仅告诉大家如何去写。
代码：
（1）对数据库clob型执行插入操作
*************************************************
java.sql.PreparedStatement pstmt = null;
ResultSet rs = null;
String query = "";
conn.setAutoCommit(false);
query = "insert into clobtest_table(id,picstr) values(?,empty_clob())";
java.sql.PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1,"001");
pstmt.executeUpdate();
pstmt = null
query = "select picstr from clobtest_table where id = '001' for update";
pstmt = con.prepareStatement(query)
rs= pstmt.executeQuery();
oracle.sql.CLOB clobtt = null;
if(rs.next()){
clobtt = (oracle.sql.CLOB)rs.getClob(1);
}
Writer wr = clobtt.getCharacterOutputStream();
wr.write(strtmp);
wr.flush();
wr.close();
rs.close();
con.commit();
（2）通过sql/plus查询是否已经成功插入数据库
*************************************************
PL/SQL的包DBMS_LOB来处理LOB数据。察看刚才的插入是否成功。使用DBMS_LOB包的getlength这个procedure来检测是否已经将str存入到picstr字段中了。如：
SQL> select dbms_lob.getlength(picstr) from clobtest_table;
（3）对数据库clob型执行读取操作
*************************************************
读取相对插入就很简单了。基本步骤和一半的取数据库数据没有太大的差别。
String description = “”
query = “select picstr from clobtest_table where id = ‘001’”;
pstmt = con.prepareStatement(query);
ResultSet result = pstmt.executeQuery();
if(result.next()){
oracle.jdbc.driver.OracleResultSet ors =
(oracle.jdbc.driver.OracleResultSet)result;
oracle.sql.CLOB clobtmp = (oracle.sql.CLOB) ors.getClob(1);
if(clobtmp==null || clobtmp.length()==0){
System.out.println(“======CLOB对象为空 “);
description = “”;
}else{
description=clobtmp.getSubString((long)1,(int)clobtmp.length());
System.out.println(“======字符串形式 “+description);
}
}