在java 中处理oracle中的大对象(CLOB和BLOB数据类型):
import
java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
public class Test ... {
/** *//**
* @param args
*/
public static void main(String[] args) ...{
// TODO Auto-generated method stub
System.out.println("Process Start ...");
testClob();
}
public static void testClob() ...{
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String strsql = "truncate table test";
String strsql1 = "insert into test values('test', empty_clob(), empty_blob())";
String strsql2 = "update test set bigobject1 = ?, bigobject2 = ? where testid = ?";
String strsql3 = "select * from test where testid = 'test' for update";
String strLong = StringLong.strLong;
System.out.println("strLong.length() : " + strLong.length());
// File file = new File("E:/pic/2006_10_02/IMG_0087.JPG");
File file = new File("D:/2007.wmv");
con = ConnDB.getConnection66();
try ...{
con.setAutoCommit(false);
pst = con.prepareStatement(strsql);
pst.executeUpdate();
pst = con.prepareStatement(strsql1);
pst.executeUpdate();
pst = con.prepareStatement(strsql3);
rs = pst.executeQuery();
while (rs.next()) ...{
long stime = System.currentTimeMillis();
BLOB bb = (BLOB) rs.getBlob(3);
CLOB cb = (CLOB) rs.getClob(2);
/** *//************************ CLOB Reader String *************************/
Writer wt = cb.getCharacterOutputStream();
wt.write(strLong);
wt.flush();
wt.close();
/** *//************************ CLOB Reader String *************************/
/** *//************************ CLOB Reader File *************************/
// BufferedWriter bw = new BufferedWriter(cb.getCharacterOutputStream());
// BufferedReader bfr = new BufferedReader(new FileReader("D:/test.txt"));
// int read =0;
// while((read = bfr.read()) != -1){
// bw.write(read);
// }
// bw.flush();
// bw.close();
/** *//************************ CLOB Reader File *************************/
/** *//************************ BLOB Reader File *************************/
OutputStream outstrm = bb.getBinaryOutputStream();
FileInputStream inpstrm = new FileInputStream(file);
byte[] buffer = new byte[inpstrm.available()];
inpstrm.read(buffer);
outstrm.write(buffer);
outstrm.flush();
inpstrm.close();
outstrm.close();
/** *//************************ BLOB Reader File *************************/
pst = con.prepareStatement(strsql2);
pst.setClob(1, cb); // Clob
pst.setBlob(2, bb); // Blob
pst.setString(3, "test");
pst.executeUpdate();
long etime = System.currentTimeMillis();
System.out.println("Execute Time : " + (etime - stime) / 1000.0);
}
con.commit();
/** *//************************ Output CLOB String From DB *************************/
pst = con.prepareStatement("select * from test");
rs = pst.executeQuery();
if (rs.next())...{
CLOB clb = (CLOB) rs.getClob(2);
BufferedReader br = new BufferedReader(clb.getCharacterStream());
String s = br.readLine();
while(s != null)...{
System.out.println(s);
s = br.readLine();
}
br.close();
}
/** *//************************ Output CLOB String From DB *************************/
System.out.println("Process End !");
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e1) ...{
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (Exception ex) ...{
ex.printStackTrace();
} finally ...{
try ...{
pst.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.sql.BLOB;
import oracle.sql.CLOB;
public class Test ... {
/** *//**
* @param args
*/
public static void main(String[] args) ...{
// TODO Auto-generated method stub
System.out.println("Process Start ...");
testClob();
}
public static void testClob() ...{
Connection con = null;
PreparedStatement pst = null;
ResultSet rs = null;
String strsql = "truncate table test";
String strsql1 = "insert into test values('test', empty_clob(), empty_blob())";
String strsql2 = "update test set bigobject1 = ?, bigobject2 = ? where testid = ?";
String strsql3 = "select * from test where testid = 'test' for update";
String strLong = StringLong.strLong;
System.out.println("strLong.length() : " + strLong.length());
// File file = new File("E:/pic/2006_10_02/IMG_0087.JPG");
File file = new File("D:/2007.wmv");
con = ConnDB.getConnection66();
try ...{
con.setAutoCommit(false);
pst = con.prepareStatement(strsql);
pst.executeUpdate();
pst = con.prepareStatement(strsql1);
pst.executeUpdate();
pst = con.prepareStatement(strsql3);
rs = pst.executeQuery();
while (rs.next()) ...{
long stime = System.currentTimeMillis();
BLOB bb = (BLOB) rs.getBlob(3);
CLOB cb = (CLOB) rs.getClob(2);
/** *//************************ CLOB Reader String *************************/
Writer wt = cb.getCharacterOutputStream();
wt.write(strLong);
wt.flush();
wt.close();
/** *//************************ CLOB Reader String *************************/
/** *//************************ CLOB Reader File *************************/
// BufferedWriter bw = new BufferedWriter(cb.getCharacterOutputStream());
// BufferedReader bfr = new BufferedReader(new FileReader("D:/test.txt"));
// int read =0;
// while((read = bfr.read()) != -1){
// bw.write(read);
// }
// bw.flush();
// bw.close();
/** *//************************ CLOB Reader File *************************/
/** *//************************ BLOB Reader File *************************/
OutputStream outstrm = bb.getBinaryOutputStream();
FileInputStream inpstrm = new FileInputStream(file);
byte[] buffer = new byte[inpstrm.available()];
inpstrm.read(buffer);
outstrm.write(buffer);
outstrm.flush();
inpstrm.close();
outstrm.close();
/** *//************************ BLOB Reader File *************************/
pst = con.prepareStatement(strsql2);
pst.setClob(1, cb); // Clob
pst.setBlob(2, bb); // Blob
pst.setString(3, "test");
pst.executeUpdate();
long etime = System.currentTimeMillis();
System.out.println("Execute Time : " + (etime - stime) / 1000.0);
}
con.commit();
/** *//************************ Output CLOB String From DB *************************/
pst = con.prepareStatement("select * from test");
rs = pst.executeQuery();
if (rs.next())...{
CLOB clb = (CLOB) rs.getClob(2);
BufferedReader br = new BufferedReader(clb.getCharacterStream());
String s = br.readLine();
while(s != null)...{
System.out.println(s);
s = br.readLine();
}
br.close();
}
/** *//************************ Output CLOB String From DB *************************/
System.out.println("Process End !");
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
} catch (FileNotFoundException e1) ...{
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (Exception ex) ...{
ex.printStackTrace();
} finally ...{
try ...{
pst.close();
con.close();
} catch (SQLException e) ...{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}