JDBC操作Oracle BLOB对象
来源:互联网 发布:sql查询结果取第一条 编辑:程序博客网 时间:2024/05/17 06:17
将文件C:\temp\cbr_order_version.dmp从本机存入Oracle数据库BLOB字段,又从数据库读出另存为C:\temp\retrieved\retrievedBLOBcbr_order_version.dmp。
//blob_content表结构
SQL> desc blob_content
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE_NAME NOT NULL VARCHAR2(100)
BLOB_COLUMN NOT NULL BLOB
//C:\temp\cbr_order_version.dmp文件属性,
//大小为8941568/1024/1024=8.527MB
C:\TEMP>dir cbr_order*.dmp
Volume in drive C is OS
Volume Serial Number is 94D2-727F
Directory of C:\TEMP
02/06/2012 06:03 PM 8,941,568 cbr_order_version.dmp
1 File(s) 8,941,568 bytes
0 Dir(s) 23,012,745,216 bytes free
//java代码
package com.ssgm.jyu.jdbc;import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class JdbcBlob {
public static void main(String[] args){
Connection conn = null;
Statement stmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
try{
conn = DriverManager.getConnection("jdbc:oracle:thin:@Host:1521:SID","username","passwd");
stmt = conn.createStatement();
conn.setAutoCommit(false);
String sourceDir = "C:\\temp\\";
String targetDir = "C:\\temp\\retrieved\\";
String fileName = "cbr_order_version.dmp";
System.out.println("Writing BLOB to blob_content...");
writeBLOB(stmt,sourceDir+fileName);
System.out.println("Reading BLOB from blob_content...");
readBLOB(stmt,fileName,sourceDir,targetDir);
}
catch(SQLException e){
e.printStackTrace();
}
finally{
try{
stmt.close();
conn.close();
}
catch(SQLException e){
e.printStackTrace();
}
}
}
public static void writeBLOB(Statement stmt, String fullName){
ResultSet blobRS = null;
try{
String sqlInsert = "INSERT INTO blob_content VALUES ('"+fullName+"',EMPTY_BLOB())";
String sqlSelect = "SELECT blob_column FROM blob_content WHERE file_name='"+fullName+"' FOR UPDATE";
//step1: initialize the LOB column to set the LOB locator
stmt.executeUpdate(sqlInsert);
//step2: retrieve the row containing the LOB locator
blobRS = stmt.executeQuery(sqlSelect);
blobRS.next();
//step3: create a LOB obj and read the LOB locator
BLOB myBlob = ((OracleResultSet) blobRS).getBLOB("blob_column");
//step4: get the chunksize of the LOB from the LOB object
int chunkSize = myBlob.getChunkSize();
//step5: create a buffer to hold a block of data from the file
byte[] byteBuffer = new byte[chunkSize];
//step6: create a file obj to open the file
File file = new File(fullName);
//step7: create an input stream obj to read the file contents
FileInputStream in = new FileInputStream(file);
//step8: read the file contents and write it to the LOB
long position = 1;
int bytesRead;
while((bytesRead = in.read(byteBuffer)) != -1){
//write the buffer contents to myBlob
myBlob.setBytes(position, byteBuffer);
position += bytesRead;
}
//step9: commit
stmt.execute("COMMIT");
//step10: close the objects used to read the file
in.close();
blobRS.close();
System.out.println("Wrote content from "+fullName+" to BLOB\n");
}
catch(SQLException e){
System.out.println("Error Code: "+e.getErrorCode());
System.out.println("Error Message: "+e.getMessage());
e.printStackTrace();
}
catch(IOException e){
System.out.println("Error Message: "+e.getMessage());
e.printStackTrace();
}
}
public static void readBLOB(
Statement stmt,
String fileName,
String sourceDir,
String targetDir){
String sqlSelect = "SELECT blob_column FROM blob_content WHERE file_name='"+
sourceDir+fileName+"'";
ResultSet blobRS = null;
try{
//step1: retrieve the row containing the BLOB locator
blobRS = stmt.executeQuery(sqlSelect);
blobRS.next();
//step2: create a LOB obj and read the LOB locator
BLOB myBlob = ((OracleResultSet) blobRS).getBLOB("blob_column");
//step3: get the chunk size of the LOB from the LOB obj
int chunkSize = myBlob.getChunkSize();
//setp4: create a buffer to hold a chunk of data from LOB
byte[] byteBuffer = new byte[chunkSize];
//step5: create a file obj
String saveFile = targetDir + "retrievedBLOB"+fileName;
File file = new File(saveFile);
//step6: create output stream obj to write the LOB contents
FileOutputStream out = new FileOutputStream(file);
//step7: get the long of LOB contents
long blobLength = myBlob.length();
//step8: read a chunk of data from myBlob,
//then write the buffer contents to file
for (long position=1; position<=blobLength; position += chunkSize){
int bytesRead = myBlob.getBytes(position, chunkSize, byteBuffer);
out.write(byteBuffer);
}
//step9: close the stream obj
out.close();
System.out.println("Read BLOB and save file"+saveFile);
}
catch(SQLException e){
System.out.println("Error Code: "+e.getErrorCode());
System.out.println("Error Message: "+e.getMessage());
e.printStackTrace();
}
catch(IOException e){
System.out.println("Error Message: "+e.getMessage());
e.printStackTrace();
}
}
}
//运行时屏幕输出
Writing BLOB to blob_content...
Wrote content from C:\temp\cbr_order_version.dmp to BLOB
Reading BLOB from blob_content...
Read BLOB and save fileC:\temp\retrieved\retrievedBLOBcbr_order_version.dmp
//验证
//程序运行前USERS表空间使用空间为81M
SQL> select sum(bytes)/1024/1024 MB from dba_extents where tablespace_name='USERS';
MB
----------
81
//程序运行后USERS表空间使用空间为89M
SQL> select sum(bytes)/1024/1024 MB from dba_extents where tablespace_name='USERS';
MB
----------
89
//用sql*plus检索
SQL> select * from blob_content;
SP2-0678: Column or attribute type can not be displayed by SQL*Plus
SQL> select FILE_NAME from blob_content;
FILE_NAME
--------------------------------------------------------------------------------
C:\temp\cbr_order_version.dmp
//检查读出来的文件
C:\TEMP\retrieved>dir
Volume in drive C is OS
Volume Serial Number is 94D2-727F
Directory of C:\TEMP\retrieved
02/10/2012 09:03 AM <DIR> .
02/10/2012 09:03 AM <DIR> ..
02/10/2012 09:16 AM 8,945,200 retrievedBLOBcbr_order_version.dmp
1 File(s) 8,945,200 bytes
2 Dir(s) 22,989,176,832 bytes free
- JDBC操作Oracle BLOB对象
- JDBC中操作Blob、Clob等对象
- Oracle操作大对象BLOB示例
- JDBC ORACLE BLOB处理
- jdbc操作blob,clob
- JDBC CLOB\BLOB操作
- jdbc操作blob,clob
- jdbc操作数据库blob
- 通过JDBC 操作 ORACLE BLOB,CLOB字段类型
- 使用JDBC操作基于Oracle的CLOB,BLOB字段类型
- (转)通过JDBC 操作 ORACLE BLOB,CLOB字段类型
- JDBC 对oracle 的clob ,blob类型的操作方式
- 利用JDBC操作Oracle CLOB和BLOB类型数据
- JDBC 对oracle 的clob ,blob类型的操作方式
- 使用java JDBC方式操作oracle blob类型数据
- JDBC和Hibernate操作Oracle中的Blob字段的小结
- 使用java JDBC方式操作oracle blob类型数据
- jdbc学习:操作Oracle的CLOB和BLOB数据
- 整数开方算法
- 命令行取当前windows登录用户名和所在组
- 消除Jquery Ajax 缓存带来的影响
- java中跳出某层循环
- jQuery.Validate验证库
- JDBC操作Oracle BLOB对象
- 编辑器控制台与对话框
- Windows7系统蓝屏代码和含义详解
- Login控件在浏览器中打开时显示英文
- MySql 5.5.20 Installation on Oracle Linux Server release 5.7
- SQLite 管理工具
- C程序代码的常用优化办法(1)
- struts2.2.3 配置中文乱码拦截过滤器 解决接收中文参数乱码问题
- PROTEL设计的项目文件太大,怎么减肥