//通过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);
}
}
}