这是一段关于LOB字段的程序,希望对你有用
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import java.io.*;
//import javax.sql.*;
public class blobtest
{
public static void main(String[] args)
{
BLOB bloba;
Connection con = null;
// PreparedStatement pstmt = null;
ResultSet rs;
String sQueryP = "SELECT TCBlob FROM TeeColor";
try // Attempt to load the JDBC driver
{ // with newInstance
Class.forName( "oracle.jdbc.driver.OracleDriver" ).newInstance();
}
catch( Exception e ) // error
{
System.err.println("Failed to load current driver.");
return;
} // end catch
try
{
con = DriverManager.getConnection ( "jdbc:oracle:thin:@fzw:1521:orcl",
"oa",
"fcgoamain");
Statement stmt = con.createStatement ();
rs = stmt.executeQuery( sQueryP );
}
catch ( Exception e)
{
System.err.println( "problems connecting to 1 :" );
System.err.println( e.getMessage() ); if( con != null)
{
try { con.close(); }
catch( Exception e2 ) {}
} return;
} // end catch
try
{
//rs = pstmt.executeQuery();
while( rs.next() )
{
System.out.println("select blob");
bloba =(BLOB)rs.getObject( 1 );
System.out.println("select blob success");
byte[] byte_a=new byte[(int)bloba.length()];
InputStream r_stream=bloba.getBinaryStream();
for(int i=0;i<bloba.length();i++)
{
try{byte_a[i]=(byte)r_stream.read();}
catch( Exception e2 ) {}
}
String a=new String(byte_a);
System.out.println(a);
} // end try
}
catch ( SQLException SQLe)
{
System.err.println("problems in DoRetrieve():" );
SQLe.printStackTrace();
}
}
}
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
import javax.swing.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
import java.io.*;
//import javax.sql.*;
public class blobtest
{
public static void main(String[] args)
{
BLOB bloba;
Connection con = null;
// PreparedStatement pstmt = null;
ResultSet rs;
String sQueryP = "SELECT TCBlob FROM TeeColor";
try // Attempt to load the JDBC driver
{ // with newInstance
Class.forName( "oracle.jdbc.driver.OracleDriver" ).newInstance();
}
catch( Exception e ) // error
{
System.err.println("Failed to load current driver.");
return;
} // end catch
try
{
con = DriverManager.getConnection ( "jdbc:oracle:thin:@fzw:1521:orcl",
"oa",
"fcgoamain");
Statement stmt = con.createStatement ();
rs = stmt.executeQuery( sQueryP );
}
catch ( Exception e)
{
System.err.println( "problems connecting to 1 :" );
System.err.println( e.getMessage() ); if( con != null)
{
try { con.close(); }
catch( Exception e2 ) {}
} return;
} // end catch
try
{
//rs = pstmt.executeQuery();
while( rs.next() )
{
System.out.println("select blob");
bloba =(BLOB)rs.getObject( 1 );
System.out.println("select blob success");
byte[] byte_a=new byte[(int)bloba.length()];
InputStream r_stream=bloba.getBinaryStream();
for(int i=0;i<bloba.length();i++)
{
try{byte_a[i]=(byte)r_stream.read();}
catch( Exception e2 ) {}
}
String a=new String(byte_a);
System.out.println(a);
} // end try
}
catch ( SQLException SQLe)
{
System.err.println("problems in DoRetrieve():" );
SQLe.printStackTrace();
}
}
}
import java.sql.*;
import java.io.*;
import java.awt.*;
import javax.swing.*;
import java.awt.event.*;
import oracle.jdbc.*;
import oracle.sql.*;
/**
在使用程序前要建立一个table
表名:largedate
字段1:name(varchar2)
字段2:blobcol(blob)
字段3:clobcol(clob)*/public class OracleLobsDemo extends javax.swing.JFrame{
private Connection conn = null;
private Statement st = null;
private ResultSet rs = null;
private String dburl = "jdbc:oracle:thin:@134.202.128.125:1521:joe";
private String user = "oa";
private String passwd = "fcgoamain";
private String sqlCommand = "select name,blobcol,clobcol from largedate";
private BLOB aBlob = null;
private CLOB aClob = null;
GridLayout gridLayout1 = new GridLayout();
JLabel jLabel1 = new JLabel();
JLabel jLabel2 = new JLabel();
JScrollPane jScrollPane1 = new JScrollPane();
JScrollPane jScrollPane2 = new JScrollPane();
JTextArea blobContainer = new JTextArea();
JTextArea clobContainer = new JTextArea();
JButton jButton1 = new JButton();
JButton jButton2 = new JButton();
public OracleLobsDemo()
{
super();
init();
try
{
jbInit();
this.setBounds(100,100,400,400);
}
catch(Exception e) {
e.printStackTrace();
}
}
public void init()
{
try
{
System.out.println("Connect to DB!");
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(dburl,user,passwd);
System.out.println("Connect Success!");
st = conn.createStatement();
// st.execute("delete from largedate");
}
catch(Exception e)
{
e.printStackTrace();
}
}
public void loadLobs()
{
try
{
System.out.println("Query Data from DB !");
rs = st.executeQuery(sqlCommand);
while(rs.next())
{
System.out.println("Obtain a line from Oracle Table largedate :");
String aName = rs.getString("name");
System.out.println("Name = "+aName);
aBlob = (BLOB)rs.getObject(2);
if(aBlob!=null)
{
System.out.println("Blob Length = "+aBlob.length());
byte[] b = new byte[(int)aBlob.length()];
java.io.InputStream is = aBlob.getBinaryStream();
for(int i=0;i<aBlob.length();i++)
{
b[i]=(byte)is.read();
}
blobContainer.setText(new String(b));
}
else
System.out.println("Blod Colum is null ");
aClob = (CLOB)rs.getObject(3);
if(aClob!=null)
{
System.out.println("Clob Length = "+aClob.length());
java.io.Reader is = aClob.getCharacterStream();
char[] c = new char[(int)aClob.length()];
is.read(c);
clobContainer.setText(new String(c));
}
else
System.out.println("Clob Colum is null ");
}
}
catch(Exception e)
{
e.printStackTrace();
}
}
public void storeLobs()
{
try
{
// PreparedStatement pst = conn.prepareStatement("insert into largedate(name,blobcol) values(?,?)");
PreparedStatement pst = conn.prepareStatement("insert into largedate values(?,?,?)");
System.out.println("Update data from DB ....");
pst.setString(1,"1");
String s = "MQSeries和Tuxedo/Q提供了一些相似的功能, 但是实际上他们是以完全不同的方式工作的。在Tuxedo/Q中的远程队列是通过直接连接到远程服务器上并在一个工作单元中进行消息排队(即类似于一个RPC操作)。如果远程Tuxedo服务器或连接路径中的任何一个部件不可用,消息将不能写入队列中--Tuxedo/Q没有存储-转发的能力。这并不是行业中所定义的消息排队。";
//当文件长度>4K时,将无法写入Oracle中!!!奇怪
File file = new File("/c:/test.txt");
InputStream is1 = new java.io.FileInputStream(file);
Reader is2 = new StringReader(s);
pst.setBinaryStream(2,is1,(int)file.length());
System.out.println("FileLength = "+file.length());
pst.setCharacterStream(3,is2,(int)s.length());
pst.executeUpdate();
System.out.println("Update Successed .");
}
catch(Exception e)
{
System.out.println("Error -- >"+e.getMessage());
e.printStackTrace();
}
}
public void finalize(){
try{
st.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
System.out.println("Close all Connections !");
}
public static void main(String[] args) {
OracleLobsDemo oracleLobsDemo1 = new OracleLobsDemo();
oracleLobsDemo1.storeLobs();
oracleLobsDemo1.show(); }
private void jbInit() throws Exception {
jLabel1.setText("Blob Content");
gridLayout1.setRows(3);
gridLayout1.setColumns(2);
this.getContentPane().setLayout(gridLayout1);
jLabel2.setText("Clob Content");
jButton1.setText("Reload");
jButton1.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jButton1_actionPerformed(e);
}
});
jButton2.setText("Quit");
jButton2.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(ActionEvent e) {
jButton2_actionPerformed(e);
}
});
this.getContentPane().add(jLabel1, null);
this.getContentPane().add(jLabel2, null);
this.getContentPane().add(jScrollPane2, null);
jScrollPane2.getViewport().add(blobContainer, null);
this.getContentPane().add(jScrollPane1, null);
this.getContentPane().add(jButton1, null);
this.getContentPane().add(jButton2, null);
jScrollPane1.getViewport().add(clobContainer, null);
} void jButton1_actionPerformed(ActionEvent e) {
loadLobs(); } void jButton2_actionPerformed(ActionEvent e) {
finalize();
System.exit(0);
}}
public static void setClob(String strSql,String lob_fieldName,String strValue) throws Exception { PreparedStatement stmt = null;
OracleCallableStatement cstmt=null;
ResultSet rs = null;
Writer writer; DBConnection conn=new DBConnection();
try{ conn.beginTrans();
stmt = conn.getPreparedStmt(strSql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE);
rs = stmt.executeQuery();
if (rs.next()) { try{
CLOB clob =(CLOB) rs.getClob(lob_fieldName);
//调用oracle dbms_lob包以读写模式打开CLOB
cstmt=(OracleCallableStatement)(conn.getConnection().prepareCall("BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READWRITE);END;"));
cstmt.setCLOB(1,clob)
//下面这行出错
//=============>>>>>>>>>>>>>>>>>>>>
cstmt.execute();
//裁减原有lob字段长度为零
cstmt=(OracleCallableStatement)(conn.getConnection().prepareCall("BEGIN DBMS_LOB.TRIM(?,0);END;"));
cstmt.setCLOB(1,clob);
cstmt.execute();
//关闭CLOB
cstmt=(OracleCallableStatement)(conn.getConnection().prepareCall("BEGIN DBMS_LOB.CLOSE(?);END;"));
cstmt.setCLOB(1,clob);
cstmt.execute();
writer=clob.getCharacterOutputStream();
writer.write(strValue);
writer.flush();
writer.close();
conn.commit();
}
catch(Exception e) {
conn.rollback();
System.out.println("**错误:存储CLOB对象异常: "+e.getMessage());
throw e;
}
}
else {
conn.rollback();
}
rs.close();
stmt.close();
conn.close();
}catch(SQLException e){
System.out.println("**错误:存储CLOB对象异常: "+e.getMessage());
throw e;
}
}错误信息:**错误:存储CLOB对象异常: ORA-22292: 无法在没有事务处理的情况下以读写模式打开 LOBORA-06512: 在"SYS.DBMS_LOB", line 599
ORA-06512: 在line 1
conn.setAutoCommit(true);
在下面这句前加上
conn.beginTrans();
cstmt=(OracleCallableStatement)(conn.getConnection().prepareCall("BEGIN DBMS_LOB.OPEN(?,DBMS_LOB.LOB_READWRITE);END;"));
conn.commit();