存取算法 oracle,JAVA存取Oracle的BFile和LOBs

//通过JDBC从oracle里面取BFile(LOB)类型数据存储到磁盘

/**

*

* @author Liu Yuanyuan

*/

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);

System.out.println("class");

conn = DriverManager.getConnection(url, "lyy", "lyy");

System.out.println("connect");

stmt = conn.createStatement();

String sql = "select files from lyy.filetable";

rs = stmt.executeQuery(sql);

System.out.println("sql="+sql);

while (rs.next())

{ System.out.println(rs.getMetaData().getColumnTypeName(1));

BFILE b = (BFILE)rs.getObject(1);// BLOB b = (BLOB)rs.getObject(1);

OutputStream ops = null;

InputStream ips = null;

File file = new File("e:"+File.separator+b.getName());//getName() only for BFILE

try

{

System.out.println("isFileExist = "+b.fileExists());//fileExists() only for BFILE

//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);

}

}

}

常见错误：

ORA-22925 getting large LOB via JDBC Thin 11.2

This problem is introduced in JDBC 11.2.Read of a LOB above 2GB in size can fail in JDBC Thin in 11.2 .

也就是说对于通过JDBC读取的LOB值的上限是2G。

//将硬盘上的直接文件存入Oracle(BFile或者BLOB)

注：直接存文件对文件大小有一定限制，查处后会内存溢出，稍后将介绍for update的方法

Private void insertBinary()

{

String driver = "oracle.jdbc.driver.OracleDriver";

String url = "jdbc:oracle:thin:@127.0.0.1 :1521:orcl";

Connection conn = null;

PreparedStatement ps = null;

try

{

Class.forName(driver);

System.out.println("success find class");

conn = DriverManager.getConnection(url, "lyy", "lyy");

System.out.println("success connect");

String sql = "insert into rawtable(id,obj) values(?,?)";

ps = conn.prepareStatement(sql);

ps.setInt(1, 1122);

//设置二进制BLOB参数

File file = new File("d:\\lyy.txt");

InputStream is = new FileInputStream(file);

ps.setBinaryStream(2, is, (int) file.length());

//设置二进制CLOB参数

// File file_clob = new File("c:\\a.txt");

//InputStreamReader reader = new InputStreamReader(new FileInputStream(file_clob));

// ps.setCharacterStream(3, reader, (int) file_clob.length());

ps.executeUpdate();

is.close();

}

catch (Exception ex)

{

ex.printStackTrace(System.out);

}

finally

{

try

{

if(ps!=null)

ps.close();

if(conn!=null)

conn.close();

}

catch (SQLException ex)

{

Logger.getLogger(GetBlob.class.getName()).log(Level.SEVERE, null, ex);

}

}

}