JDBC操作BFILEフィールド
5214 ワード
import java.io.*;
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class JdbcBfile {
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:@db_server:1521:SID","username","password");
stmt = conn.createStatement();
conn.setAutoCommit(false);
String dbDir = "LOB_DIR";
String targetDir = "C:\\";
String fileName = "a.zip";
System.out.println("Adding BFILE to db...");
addBfile(stmt,dbDir, fileName);
System.out.println("Retrieving BFILE from db...");
retrieveBfile(stmt,targetDir,fileName);
}
catch(SQLException e){
e.printStackTrace();
}
finally{
try{
stmt.close();
conn.close();
}
catch(SQLException e){
e.printStackTrace();
}
}
}
private static void addBfile(
Statement stmt,
String directory,
String fileName){
String sqlInsert = "INSERT INTO bfile_content VALUES('"+fileName+"', bfilename('"+directory+"','"+fileName+"'))";
System.out.println(sqlInsert);
try{
stmt.executeUpdate(sqlInsert);
stmt.execute("COMMIT");
System.out.println("Added pointer to file"+ fileName+" to BFILE in DB Directory "+directory+"
");
}
catch(SQLException e){
System.out.println("Error Code: "+e.getErrorCode());
System.out.println("Error Message: "+e.getMessage());
e.printStackTrace();
}
}
private static void retrieveBfile(
Statement stmt,
String targetDir,
String fileName){
String sqlSelect = "SELECT bfile_column FROM bfile_content WHERE file_name='"+fileName+"'";
ResultSet bfileRS = null;
try{
//step1: retrieve the row containing BFILE locator
bfileRS = stmt.executeQuery(sqlSelect);
bfileRS.next();
System.out.println(sqlSelect);
//step2: create a BFILE obj and read the locator
BFILE myBfile = ((OracleResultSet) bfileRS).getBFILE("bfile_column");
//step3: get the file name from BFILE obj
String bfileName = myBfile.getName();
//step4: check the external file exists
myBfile.fileExists();
//step5: open the external file
myBfile.openFile();
//step6: create an input stream to read the external file
InputStream in = myBfile.getBinaryStream();
//step7: save the file contents to a new file
String saveFileName = targetDir+"retrievedBFILE"+bfileName;
saveFile(in, saveFileName);
//step8: close the input stream
in.close();
myBfile.closeFile();
System.out.println("Retrieved BFILE and saved to "+saveFileName);
}
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();
}
}
private static void saveFile(
InputStream in,
String saveFileName){
try{
File file = new File(saveFileName);
FileOutputStream out = new FileOutputStream(file);
byte[] byteBuffer = new byte[8132];
int bytesRead;
while((bytesRead = in.read(byteBuffer)) != -1){
out.write(byteBuffer);
}
out.close();
}
catch(IOException e){
e.printStackTrace();
}
}
}