oracle边读边写,测试向oracle 读,写文件Blob 读,写大文本Clob

package com.semovy.test;

import java.io.BufferedInputStream;

import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.OutputStream;

import java.io.PrintWriter;

import java.io.Reader;

import java.sql.Blob;

import java.sql.Clob;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.sql.Statement;

/**

*

* @author semovy 测试向oracle 读,写文件Blob 读,写大文本Clob

*/

public class OracleBlobTest {

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

private String url = "jdbc:oracle:thin:@localhost:1521:teckotooling";

private String user = "scott";

private String pwd = "tiger";

public OracleBlobTest() {

}

public static void main(String[] args) {

OracleBlobTest obt = new OracleBlobTest();

obt.writeBlob();

obt.readBlob();

obt.writeClob();

obt.readClob();

}

/**

* 读二进制文件

*

*/

private void readBlob() {

Connection conn = null;

try {

conn = getConnection();

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from test where id=1");

byte[] buffer = new byte[1024];

OutputStream out = new FileOutputStream("d:/360安全卫士定1.exe");

int tempLen = 0;

int amount = 0;

if (rs.next()) {

Blob blob = rs.getBlob("BINARYCONTENT");

InputStream in = blob.getBinaryStream();

while ((tempLen = in.read(buffer)) != -1) {

out.write(buffer, 0, tempLen);

amount += tempLen;

System.out.println("已经读出并写:" + amount + " 字节");

}

System.out.println("已经读出并写:完成");

out.flush();

out.close();

in.close();

rs.close();

stmt.close();

}

} catch (ClassNotFoundException e) {

System.out.println(e.getLocalizedMessage());

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

} catch (IOException e) {

System.out.println(e.getLocalizedMessage());

} finally {

try {

if (conn != null)

conn.close();

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

}

}

}

/**

* 写二进制文件

*

*/

private void writeBlob() {

Connection conn = null;

try {

conn = getConnection();

conn.setAutoCommit(false);

String sql = null;

Statement stmt = conn.createStatement();

sql = "delete from test where id=1";

stmt.executeUpdate(sql);

sql = "insert into test(1,BINARYCONTENT,CLOBCONTENT) values(1,empty_blob(),empty_clob())";

stmt.executeUpdate(sql);

ResultSet rs = stmt.executeQuery("select * from test where id=1");

if (rs.next()) {

Blob blob = rs.getBlob("BINARYCONTENT");

OutputStream out = ((oracle.sql.BLOB) blob).setBinaryStream(0);// 从0开始,否则写出的文件有差错

int bufferSize = ((oracle.sql.BLOB) blob).getBufferSize();

System.out.println("bufferSize :" + bufferSize);

BufferedInputStream in = new BufferedInputStream(

new FileInputStream("d:/360安全卫士定.exe"), bufferSize);

byte[] b = new byte[bufferSize];

int count = in.read(b, 0, bufferSize);

int amount = 0;

while (count != -1) {

out.write(b, 0, count);

amount += count;

System.out.println("处理了 " + amount + " 字节");

count = in.read(b, 0, bufferSize);

System.out.println("处理了 " + amount + " 字节,成功");

}

out.close();

out = null;

in.close();

conn.commit();

}

} catch (ClassNotFoundException e) {

System.out.println(e.getLocalizedMessage());

} catch (SQLException e) {

try {

conn.rollback();

} catch (SQLException e1) {

System.out.println(e1.getLocalizedMessage());

}

System.out.println(e.getLocalizedMessage());

} catch (IOException e) {

System.out.println(e.getLocalizedMessage());

} finally {

try {

if (conn != null)

conn.close();

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

}

}

}

/**

* 读大文本

*

*/

private void readClob() {

Connection conn = null;

try {

conn = getConnection();

Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery("select * from test where id=2");

String tempStr = null;

if (rs.next()) {

Clob clob = rs.getClob("CLOBCONTENT");

if (clob != null) {

Reader in = clob.getCharacterStream();

BufferedReader br = new BufferedReader(in);

System.out.println("开始读....");

while ((tempStr = br.readLine()) != null) {

System.out.println(tempStr);

}

System.out.println("读完成....");

in.close();

}

rs.close();

stmt.close();

}

} catch (ClassNotFoundException e) {

System.out.println(e.getLocalizedMessage());

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

} catch (IOException e) {

System.out.println(e.getLocalizedMessage());

} finally {

try {

if (conn != null)

conn.close();

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

}

}

}

/**

* 写大文本

*

*/

private void writeClob() {

Connection conn = null;

try {

conn = getConnection();

conn.setAutoCommit(false);

String sql = null;

Statement stmt = conn.createStatement();

sql = "delete from test where id=2";

stmt.executeUpdate(sql);

sql = "insert into test values(2,empty_blob(),empty_clob())";

stmt.executeUpdate(sql);

ResultSet rs = stmt.executeQuery("select * from test where id=2");

if (rs.next()) {

Clob clob = rs.getClob("CLOBCONTENT");

PrintWriter out = new PrintWriter(new BufferedWriter(

((oracle.sql.CLOB) clob).setCharacterStream(0)));

BufferedReader in = new BufferedReader(new InputStreamReader(

new FileInputStream(

"d:/在北大校园BBS引起轰动的一篇文章请热爱祖国的人转发！！！！.mht")));

String str = null;

System.out.println("开始写...");

while ((str = in.readLine()) != null) {

out.println(str);

System.out.println(str);

}

in.close();

out.close();

rs.close();

conn.commit();

}

} catch (ClassNotFoundException e) {

System.out.println(e.getLocalizedMessage());

} catch (SQLException e) {

try {

conn.rollback();

} catch (SQLException e1) {

System.out.println(e1.getLocalizedMessage());

}

System.out.println(e.getLocalizedMessage());

} catch (IOException e) {

System.out.println(e.getLocalizedMessage());

} finally {

try {

if (conn != null)

conn.close();

} catch (SQLException e) {

System.out.println(e.getLocalizedMessage());

}

}

}

private Connection getConnection() throws ClassNotFoundException,

SQLException {

Class.forName(driver);

return DriverManager.getConnection(url, user, pwd);

}

/**

*

* @param rs

* @throws SQLException

*/

private void displayResultSet(ResultSet rs) throws SQLException {

ResultSetMetaData rsmd = rs.getMetaData();

int colnum = rsmd.getColumnCount();

while (rs.next()) {

for (int i = 0; i < colnum; i++) {

if (i == colnum - 1)

System.out.print(rsmd.getColumnLabel(i + 1) + ": "

+ rs.getObject(i + 1));

else

System.out.print(rsmd.getColumnLabel(i + 1) + ": "

+ rs.getObject(i + 1) + " , ");

}

System.out.println();

}

}

}