Oracle的Blob字段比较特殊，他比long字段的性能要好很多，可以用来保存例如图片之类的二进制数据。

准备环境：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);

}

}

相关阅读

• 暂无推荐