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