一、Oracle的BFILE数据类型简介
1. BFILE数据类型介绍
- BFILE类型是Oracle数据库中LOB类型的一种
- BFILE字段存放指向操作系统文件的指针
- BFILE字段只支持只读访问
- BFILE字段支持最大2^64-1 bytes文件
- BFILE字段指向的文件不是数据库的一部份,只能在数据库外维护
- 对BFILE字段操作要使用BFILENAME函数
- 读取BFILE字段需要使用DBMS_LOB包
2、BFILENAME函数介绍
- BFILENAME函数语法如下
- BFILENAME函数有两个参数 '
directory
','filename
' - '
directory
'参数是大小写敏感的 - '
directory
'参数所指定的目录必须在dba_directories中存在
二、创建包含BFILE字段的表
1、新建目录/tmp/bfile
[oracle@localhost ~]$ mkdir -p /tmp/bfile/
2、创建directory
SQL> create directory BFILEDIR as '/tmp/bfile';
3、新建两个文件
[oracle@localhost ~]$ cd /tmp/bfile/
[oracle@localhost bfile]$ pwd
/tmp/bfile
[root@db2 bfile]# ls -l
total 8
-rw-r--r-- 1 root root 51 Nov 7 18:10 aaa.txt
-rw-r--r-- 1 root root 52 Nov 7 18:11 bbb.txt
[oracle@localhost bfile]$ cat aaa.txt
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
[oracle@localhost bfile]$ cat bbb.txt
aaaaaaa
bbbbbb
ccccccc
dddddddd
111111
22222
333333
4、创建表
SQL> CREATE TABLE "TANGYIBO"."t_oralce_bfile" ( "id" NUMBER NOT NULL ENABLE,"file" BFILE, PRIMARY KEY ("id")) ;
Table created.
5、初始化表数据
SQL>INSERT INTO "TANGYIBO"."t_oralce_bfile" ("id", "file") VALUES ('1', BFileName('BFILEDIR', 'aaa.txt'));
SQL>INSERT INTO "TANGYIBO"."t_oralce_bfile" ("id", "file") VALUES ('2', BFileName('BFILEDIR', 'bbb.txt'));
SQL> commit;
三、JDBC方式读取oracle的BFILE类型数据
private void getBFile() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
Class.forName(driver);
conn = DriverManager.getConnection(url, "tangyibo", "123456");
System.out.println("connect");
stmt = conn.createStatement();
String sql = "SELECT \"id\",\"file\" FROM \"TANGYIBO\".\"t_oralce_bfile\" ";
rs = stmt.executeQuery(sql);
System.out.println("sql="+sql);
while (rs.next()) {
BFILE b = (BFILE)rs.getObject(1);
OutputStream ops = null;
InputStream ips = null;
//getName() only for BFILE
File file = new File("d:"+File.separator+b.getName());
try {
//fileExists() only for BFILE
System.out.println("isFileExist = "+b.fileExists());
//For BFILE,must open File,and close File after its operation
//for BLOB,needn’t openFile() and closeFile()
b.openFile();
ips = b.getBinaryStream();
byte[] buffer =new byte[b.getBytes().length];//
ops = new FileOutputStream(file);
//将文件写到硬盘
for (int i; (i = ips.read(buffer)) > 0;) {
ops.write(buffer, 0, i);
ops.flush();
}
b.closeFile();
}
catch (Exception ex) {
ex.printStackTrace(System.out);
}
finally {
ips.close();
ops.close();
}
}
}
catch (Exception ex) {
ex.printStackTrace(System.out);
}
finally {
try {
if(rs!=null) {
rs.close();
}
if(stmt!=null) {
stmt.close();
}
if(conn!=null) {
conn.close();
}
}
catch (SQLException ex) {
ex.printStackTrace(System.out);
}
}
}
四、参考文章
1、https://blog.csdn.net/baobing726/article/details/89048584
2、https://my.oschina.net/liuyuanyuangogo/blog/151215