一、 CLOB对象的存取1、往数据库中插入一个新的CLOB对象public static void clobInsert(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 插入一个空的CLOB对象 */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");
/* 查询此CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}2、修改CLOB对象(是在原CLOB对象基础上进行覆盖式的修改)public static void clobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 进行覆盖式修改 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
} 3、替换CLOB对象(将原CLOB对象清除,换成一个全新的CLOB对象)public static void clobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 清空原CLOB对象 */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 更新数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}4、CLOB对象读取public static void clobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 查询CLOB对象 */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
while (rs.next()) {
/* 获取CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
out.close();
in.close();
}
} catch (Exception ex) {
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 插入一个空的CLOB对象 */
stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");
/* 查询此CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 取出此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 向CLOB对象中写入数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}2、修改CLOB对象(是在原CLOB对象基础上进行覆盖式的修改)public static void clobModify(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 进行覆盖式修改 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
} 3、替换CLOB对象(将原CLOB对象清除,换成一个全新的CLOB对象)public static void clobReplace(String infile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 清空原CLOB对象 */
stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");
/* 查询CLOB对象并锁定 */
ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
while (rs.next()) {
/* 获取此CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 更新数据 */
BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(infile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();
out.close();
}
/* 正式提交 */
conn.commit();
} catch (Exception ex) {
/* 出错回滚 */
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}4、CLOB对象读取public static void clobRead(String outfile) throws Exception
{
/* 设定不自动提交 */
boolean defaultCommit = conn.getAutoCommit();
conn.setAutoCommit(false);try {
/* 查询CLOB对象 */
ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
while (rs.next()) {
/* 获取CLOB对象 */
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
/* 以字符形式输出 */
BufferedReader in = new BufferedReader(clob.getCharacterStream());
BufferedWriter out = new BufferedWriter(new FileWriter(outfile));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
out.close();
in.close();
}
} catch (Exception ex) {
conn.rollback();
throw ex;
}/* 恢复原提交状态 */
conn.setAutoCommit(defaultCommit);
}
解决方案 »
- 求助,这个sql语句在mssql可以通过,但在oracle却不能
- 请问下是不是官方网站下载的ORACLE就可以用啊?
- 如何在 Oracle Apex 中制作上传文件 的网页呢?
- exec函数例子
- Oracle数据库的按时间查询问题,很简单,但是我不会
- 小型机上的归档日志,能不能在windows系统上用啊??
- PowerDesigner
- 如果procedure中执行的是一条SQL语句,如何得到返回的数据集?
- 请教!当需入库的txt文件名称是随时间变化时,sqlload如何识别导入!
- oracle12c安装失败【INS-30131】执行安装程序验证所需要的初始设置失败
- for cursor_a in cursor_test loop这样的命名规则可取吗?
- 救命啊!! blob问题,怎么向blob字段插入数据! 救命……
用的weblogic连接池
package com.westerasoft.kdcerp.servlets.drapshap;import java.net.*;
import java.sql.*;
import java.io.*;
import com.westerasoft.util.dbutil.dataaccess.DBconn;
/**
*
* <p>Title:处理blob数据类
* <p>Description: 其中包括两个方法:1。将文件写入数据库2。从数据库中读数据显示到客户端
* <p>Copyright: Copyright (c) 2003</p>
* <p>author: liuyi </p>
* @
* @version 1.0
*/
public class BlobData {
public Connection con=null;
public ResultSet rs=null;
public java.sql.PreparedStatement pst=null;
File myFile=null;
java.io.InputStream bais=null;
String sqlblob[]=new String[2];
public BlobData(){
init();
}
private void init(){ sqlblob[0]="select dcblob from p_dcblob where ";//p_dcblob
sqlblob[1]="select jkzzblob from p_jkzzblob where ";//p_jkzzblob
} /**
* Get Blob
* @param index Array
* @param ifwhere condition
* @return ByteArrayOutputStream
*/
public java.io.ByteArrayOutputStream readFileFromDB(int index,String ifwhere){ InputStream is=null;
int bytesread=0;
byte []butter=new byte[8*1024];
java.io.ByteArrayOutputStream bos=null;
String sql="";
java.sql.Blob blob = null;
sql=sqlblob[index]+ifwhere;
System.out.println(sql); System.out.println(sql);
try{
//Initialization ByteArrayOutputStream
bos = new java.io.ByteArrayOutputStream();
//Get Connection
con = new DBconn().getConnection(); pst = con.prepareStatement(sql);
rs = pst.executeQuery();
int size = 0;
while (rs.next()) {
//Get Blob
blob = rs.getBlob(1);
if(blob.length()!=0)
is = blob.getBinaryStream();
} while(((bytesread=is.read())!=-1)){
bos.write(bytesread);
} }catch (Exception e) {
System.out.println(e.toString());
}finally {
try {
if (rs != null)
rs.close();
if (pst != null)
pst.close();
if (con != null)
con.close(); }
catch (Exception e) {
System.out.println("数据库连接错误:"+e.toString());
}
}
return bos;
}
/**
* 将文件写入数据库
* @param pathname:文件全路径
*/
public void writeDBFromFile(String pathname){
myFile=new File(pathname);
try{
//将文件转化为文件流
java.io.FileInputStream fis=new FileInputStream(myFile);
//得到一个数据库连接
con=new DBconn().getConnection();
pst=con.prepareStatement("update test set mblob=? where id=2");
//pst.setInt(1,2);
byte bb[];
String sql="ab";
bb=sql.getBytes();
pst.setBinaryStream(1,new java.io.ByteArrayInputStream(bb),bb.length);
//pst.setBinaryStream(1,fis,(int)myFile.length());
pst.executeUpdate();
}catch(Exception e){
System.out.println(e.toString());
}finally{
try{
if (rs!=null) rs.close();
if (pst!=null) pst.close();
if (con!=null) con.close(); }catch(Exception e){
System.out.println(e.toString());
}
}
}
public void ss(){
String sql="update H_ZGJJJSXX set enddate = to_date('2003-12-04','yyyy-mm-dd'),declarefundbase = 44.0,checkedfundbase = 66.0 where 1=1 and stardate = to_date('2003-10-02','yyyy-mm-dd') and rybm = '001'";
try{
con = new DBconn().getConnection();
java.sql.Statement st = con.createStatement();
st.executeUpdate(sql);
}catch(Exception e){
}
}
public static void main(String[] args){
new BlobData().readFileFromDB(1,"");
//new testBlob().readFileFromDB(0,"1");
} }