java操作Oracle数据库中的Clob,Blob字段

mrmcggyloa

说明1:首先所有的文件都是以二进制存储
2:二进制文件有.doc .xls .jpg
文本文件有   .txt .html .xml
先在oracle数据库中建一张表用与测试
create table CDL_TEST
(
SID  VARCHAR2(20) not null,
IMG  BLOB,       //存储二进制
DOC  CLOB,     //存储文本
DATA NUMBER
)
-- 约束
alter table CDL_TEST
add constraint CDL_SID primary key (SID)

测试代码如下:
package DataBaseUtil;
import java.sql.*;
import java.util.Scanner;
import java.io.*;
import oracle.sql.BLOB;
class InitDB{
private static Connection con=null;
private static Statement stmt=null;
private static ResultSet rs=null;
InitDB(){
try{
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();  
String url="jdbc:oracle:thin:@localhost:1521:ORCL";  //ORCL 是sid
String user="cdl";  
String password="1";  
con= (Connection) DriverManager.getConnection(url,user,password);
InitDB.setCon(con);
}catch(Exception e){
e.printStackTrace();
}
}
public void closCon(){
try{
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
public void stmt(){
try{
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
public void rs(){
try{
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static Connection getCon() {
return con;
}
public static void setCon(Connection con) {
InitDB.con = con;
}
public static ResultSet getRs() {
return rs;
}
public static void setRs(ResultSet rs) {
InitDB.rs = rs;
}
public static Statement getStmt() {
return stmt;
}
public static void setStmt(Statement stmt) {
InitDB.stmt = stmt;
}
}
/*
* 插入Blob数据 如:图片
*/
class InsertBlobData{
private ResultSet rs=null;
private InitDB idb=null;
InsertBlobData(){
idb=new InitDB();
}
public  void insertBlob(String sql1) throws SQLException{
Connection con=idb.getCon();
try{
con.setAutoCommit(false);//不设置自动提交
BLOB blob = null; //插入空的Blob
PreparedStatement pstmt = con.prepareStatement("insert into cdl_test(sid,img) values(?,empty_blob())");
pstmt.setString(1,"100");
pstmt.executeUpdate();
pstmt.close();
rs=con.createStatement().executeQuery(sql1);
while(rs.next()){
System.out.println("rs length is:");
oracle.sql.BLOB  b=(oracle.sql.BLOB )rs.getBlob("img");
System.out.println("cloblength is:"+b.getLength());
File f=new File("d:\\img\\1.jpg");
System.out.println("file path is:"+f.getAbsolutePath());
BufferedInputStream in=new BufferedInputStream(new FileInputStream(f));  
BufferedOutputStream  out=new BufferedOutputStream(b.getBinaryOutputStream());
int   c;   
while ((c=in.read())!=-1)   {   
out.write(c);   
}   
in.close();   
out.close();
}
con.commit();
}catch(Exception e){
con.rollback();//出错回滚
e.printStackTrace();
}
}
}
/*
* 插入大文本如:1.txt
*/
class InsertClobData{
private ResultSet rs=null;
private InitDB idb=null;
InsertClobData(){
idb=new InitDB();
}
public  void insertClob(String sql1) throws SQLException{
Connection con=idb.getCon();
try{
con.setAutoCommit(false);//不设置自动提交
BLOB blob = null; //插入空的Clob
PreparedStatement pstmt = con.prepareStatement("insert into cdl_test(sid,doc) values(?,empty_clob())");
pstmt.setString(1,"101");
pstmt.executeUpdate();
pstmt.close();
rs=con.createStatement().executeQuery(sql1);
while(rs.next()){
System.out.println("sdfasdfas");
oracle.sql.CLOB  cb=(oracle.sql.CLOB)rs.getClob("doc");
File f=new File("d:\\doc\\1.txt");
System.out.println("file path is:"+f.getAbsolutePath());
BufferedWriter out = new BufferedWriter(cb.getCharacterOutputStream());
BufferedReader in = new BufferedReader(new FileReader(f));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
in.close();   
out.close();
}
con.commit();
}catch(Exception e){
con.rollback();//出错回滚
e.printStackTrace();
}
}
}
/*
* 读取图片
*/
class ReadBlobData{
private ResultSet rs=null;
private InitDB idb=null;
ReadBlobData(){
idb=new InitDB();
}
public void getBlob(String sql2) throws SQLException{
Connection con=idb.getCon();
con.setAutoCommit(false);
try{
System.out.println("sq2 is:"+sql2);
System.out.println("stmt is:"+con);
rs=con.createStatement().executeQuery(sql2);
while(rs.next()){
System.out.println("rs length is:");
Blob b=(Blob)rs.getBlob("img");
File f=new File("D:\\saveimg\\1.jpg");
FileOutputStream fos=new FileOutputStream(f);
InputStream is=b.getBinaryStream();//读出数据后转换为二进制流
byte[] data=new byte[1024];
while(is.read(data)!=-1){
fos.write(data);
}
fos.close();
is.close();
}
con.commit();//正式提交
}catch(Exception e){
e.printStackTrace();
}finally{
//rs.close();
}
}
}
/*
* 读取大文本
*/
class ReadClobData{
private ResultSet rs=null;
private InitDB idb=null;
ReadClobData(){
idb=new InitDB();
}
public void getClob(String sql2) throws SQLException{
Connection con=idb.getCon();
try{
con.setAutoCommit(false);//不设置自动提交
System.out.println("sq2 is:"+sql2);
rs=con.createStatement().executeQuery(sql2);
while(rs.next()){
oracle.sql.CLOB clob=(oracle.sql.CLOB)rs.getClob("doc");
File f=new File("d:\\savedoc\\1.txt");
BufferedReader in = new BufferedReader(clob.getCharacterStream());
//setCharacterStream()方法，可用于将CLOB字段与字节流相关联，
BufferedWriter out = new BufferedWriter(new FileWriter(f));
int c;
while ((c=in.read())!=-1) {
out.write(c);
}
out.close();
in.close();
}
con.commit();//正式提交
rs.close();
}catch(Exception e){
e.printStackTrace();
con.rollback();
}
}
}
public class TestBlob {
public static void main(String []args){
String sql1="select * from cdl_test  for update";//悲观锁锁定需更新的行
String sql2="select * from cdl_test";
System.out.println("\t\t\t欢迎使用:");
System.out.println("1:插入图片");
System.out.println("2:插入文本");
System.out.println("3:读取图片");
System.out.println("4:读取文本");
System.out.println("5:退出");
System.out.println("请选择:")
while(true){
try{
Scanner sc=new Scanner(System.in);
int i=sc.nextInt();
System.out.println("sss:"+i);
switch(i){
case 1:
InsertBlobData isd=new InsertBlobData();
isd.insertBlob(sql1); break;
case 2:
InsertClobData icd=new InsertClobData();
icd.insertClob(sql1); break;
case 3:
ReadBlobData rb=new ReadBlobData();
rb.getBlob(sql2); break;
case 4:
ReadClobData rc=new ReadClobData();
rc.getClob(sql2); break;
case 5:
System.exit(0);
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}