oracle 图片路径存储,Oracle 存储图片

1.建表

create table lobtest(

id int primary key,

fname varchar(30),

myfile blob

);

2.创建目录

create directory "lobdir" as 'c:/lobdir';

3.创建存储过程

create or replace procedure savepic(vFileName varchar2)

as

varF bfile;

varB blob;

vno number(8);

begin

varF := BFilename('lobdir',vFileName);

dbms_output.put_line(vFileName);

DBMS_LOB.Open(varF);

select max(id) into vno from lobtest;

if vno is null then

vno := 1;

else

vno := vno + 1;

end if;

insert into lobtest values(vno,vFileName,empty_blob());

select myFile into varB from lobtest where id = vno for update;

DBMS_LOB.loadfromfile(varB,varF,DBMS_LOB.getlength(varF));

DBMS_LOB.close(varF);

commit;

end;

/

4..执行存储过程

exec savepic('trace.jpg');

5..用代码从数据库获取图片

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.io.*;

public class 提取DB图片 {

public static void main(String[] args) {

Connection ct = null;

PreparedStatement preparedStatement = null;

ResultSet resultSet = null;

BufferedInputStream bufferedInputStream = null;

BufferedOutputStream bufferedOutputStream = null;

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

ct = DriverManager.getConnection(

"jdbc:oracle:thin:@127.0.0.1:1521:orcl", "admin", "admin");

preparedStatement = ct

.prepareStatement("select myfile, fname from lobtest where id = ?");

preparedStatement.setInt(1, 1);

resultSet = preparedStatement.executeQuery();

while (true == resultSet.next()) {

bufferedInputStream = new BufferedInputStream(resultSet

.getBinaryStream("myfile"));

bufferedOutputStream = new BufferedOutputStream(

new FileOutputStream("src/" + resultSet.getString(2)));// 读取后的保存图片路径

byte[] buffer = new byte[1024];

for (int len = 0; (len = bufferedInputStream.read(buffer)) > 0;) {

bufferedOutputStream.write(buffer, 0, len);

// bufferedOutputStream.flush();//关闭流时会自动刷新

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

if (null != resultSet)

try {

resultSet.close();

} catch (SQLException e) {

e.printStackTrace();

}

if (null != preparedStatement)

try {

preparedStatement.close();

} catch (SQLException e) {

e.printStackTrace();

}

if (null != ct)

try {

ct.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

System.out.println("----------ok-------------");

}

} 打开src下的文件，测试，文件格式正常。