import java.sql.*;
import java.io.*;
import java.util.*;
/**
* @author semovy 测试向oracle 读,写文件Blob 读,写大文本Clob
*/
public class OracleBlobTest {
public static void main(String[] args) {
if (args.length<2)
{
System.out.println("usage: java OracleBlobTest [tablename][columname]");
System.exit(1);
}
String table_name=args[0];
String column_name=args[1];
OracleBlobTest obt = new OracleBlobTest();
// obt.writeBlob("blob_content","id");
//写出数据库内容
obt.readBlob(table_name,column_name);
//写入数据库内容
//obt.writeBlob("E:\\img\\2011\\DSC01333.JPG");
//obt.readClob();
}
/**
* * 从数据库里读取blob字段写出到文件
*/
private void readBlob(String tablename,String columname) {
Connection conn = null;
try {
conn = getConnection();
String sql = "select * from "+tablename+" where "+columname+"=?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,"1");
ResultSet rs = stmt.executeQuery();
int tempLen = 0;
int amount = 0;
if (rs.next()) {
String colVal=rs.getString("file_name");
colVal=colVal.replaceAll("\\\\","\\\\\\\\");
colVal=colVal.replaceAll(" ","");
System.out.println(colVal);
File file=new File(colVal);
byte[] buffer = new byte[1024];
OutputStream out = new FileOutputStream(file);
Blob blob = rs.getBlob("blob_column");
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(String filename) {
Connection conn = null;
try {
conn = getConnection();
conn.setAutoCommit(false);
String sql = "insert into blob_content values(?,?,?)";
PreparedStatement stmt = conn.prepareStatement(sql);
System.out.println(filename);
stmt.setString(1,filename);
File file=new File(filename);
FileInputStream fis=new FileInputStream(file);
stmt.setBinaryStream(2,fis,fis.available());
stmt.setString(3,"11");
stmt.executeUpdate();
conn.commit();
/*
if (rs.next()) {
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(colVal), 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();
}
*/
stmt.close();
} 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 clob_content ";
stmt.executeUpdate(sql);
sql = "insert into clob_content values('test',empty_clob())";
stmt.executeUpdate(sql);
ResultSet rs = stmt.executeQuery("select * from clob_content");
if (rs.next()) {
Clob clob = rs.getClob("CLOB_COLUMN");
PrintWriter out = new PrintWriter(new BufferedWriter(
((oracle.sql.CLOB) clob).setCharacterStream(0)));
BufferedReader in = new BufferedReader(new InputStreamReader(
new FileInputStream(
"d:\\javasource\\db\\textcontent.txt")));
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());
}
}
}
public Connection getConnection() throws ClassNotFoundException,IOException,
SQLException {
Properties ps=new Properties();
FileInputStream fis= new FileInputStream("mydb.properties");
ps.load(fis);
fis.close();
String url=ps.getProperty("db_url");
String user=ps.getProperty("username");
String pwd=ps.getProperty("password");
String driver = "oracle.jdbc.driver.OracleDriver";
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();
}
}
}
mydb.properties文件
db_url=jdbc:oracle:thin:@127.0.0.1:1521:ccxe
username=scott
password=test