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();
}
}
}