Oracle blob字段类型 文件读写实例
Oracle blob字段类型 文件读写实例
准备环境：pl/sql java JDK Oracle JDBC driver
创建blob_test表
create table blob_test(
id number,
name varchar(50),
word blob
)
先插入空白数据然后执行更新
Code:
package sql; //JDBC connect management for Oracle require Oracle Jdbc driver
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static Connection getDBConnection() throws Exception {
Connection con = null;
String driver = "oracle.jdbc.driver.OracleDriver";//database driver
String url = "jdbc:oracle:thin:@localhost:1521:ORCL";//database URL
String user = "system"; //database name
String password = "3edc4rfv"; //database Password
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
return con;
}
}
---------------------------------------------------------------------------------------------------------------------------------
package sql;
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
import sql.DBConnection;
public class bolb_test {
public static void main(String[] args) throws SQLException {
Connection con=null ;
long start=System.currentTimeMillis(); //count runtime
CallableStatement pstmt=null;
InputStream fin=null;
OutputStream outStream=null;
String path = "D:\\test.txt";
File file = new File(path);
try{
con = DBConnection.getDBConnection();
con.setAutoCommit(false);
String sql="insert into blob_test values(?,?,?)";
//insert database
pstmt = con.prepareCall(sql);
for(int i=0;i<10;i++)
{
String name = "Young_"+i;
int id = 1;
pstmt.setInt(1,id+i);
pstmt.setString(2, name);
Blob word = BLOB.empty_lob();
pstmt.setBlob(3, word);
pstmt.execute();
}
System.out.print("instert ok\n");
Statement stmt = con.createStatement();
ResultSet rs =stmt.executeQuery("select id,word from blob_test for update");
//get specially columns and rows for update
while(rs.next()) {
//System.out.print(rs.getInt(1)+rs.getString(2)+"\n");//print select sql for debug
BLOB blob = (BLOB) rs.getBlob("word");
outStream = blob.getBinaryOutputStream();
fin = new FileInputStream(file); //put file into stream
byte[] b = new byte[blob.getBufferSize()];
int len = 0;
while ((len = fin.read(b)) != -1) {
outStream.write(b, 0, len);
}
fin.close();
outStream.flush();
outStream.close();
}
System.out.print("\nupdate ok\n");
con.commit();
}
catch (Exception e) {
e.printStackTrace();
}
con.close();
long end=System.currentTimeMillis();
System.out.println(end-start);
}
}
读取blob文件
package sql;
import java.io.*;
import java.sql.*;
import oracle.sql.BLOB;
import sql.DBConnection;
public class getbolb_test {
public static void main(String[] args) throws SQLException {
Connection con=null ;
long start=System.currentTimeMillis(); //count runtime
String path = "D:\\test1.txt";
File file = new File(path);
try{
con = DBConnection.getDBConnection();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
ResultSet rs =stmt.executeQuery("select id,word from blob_test where id=10");
//get blob form your table
if(rs.next()) {
BLOB blob = (BLOB) rs.getBlob("word");
//get word column
FileOutputStream output = new FileOutputStream(file);
// define a file output stream
InputStream input = blob.getBinaryStream();//put blob into input
byte[] buffer = new byte[blob.getBufferSize()];
//if use 1024 it will lose some bytes
int len = 0;
while ((len = input.read(buffer)) != -1) {
//get all input stream into output file stream
output.write(buffer, 0, len);
input.close();
output.flush();
output.close();
}
System.out.print("\ndownload ok\n");
}
}
catch (Exception e) {
e.printStackTrace();
}
con.close();
long end=System.currentTimeMillis();
System.out.println(end-start);
}
}
