Posted on 2013-05-28 13:33
zolly 阅读(1942)
评论(1) 编辑 收藏
导入导出Oracle BLOB字段不能使用SQL，可以使用EXP或者EXPDP
EXP导出示例：
exp name/password@orcl file=E:/exp.dump log=E:/exp.log table=(tb_emp) query="""where UserName='FN'"""
EXPDP导出示例：
Sample.bat
expdp name/password@orcl parfile=E:/expdp.par
expdp.par
DIRECTORY=dir_dump
DUMPFILE=backup.dump
LOGFILE=backup.log
TABLES=pr_data_admin
QUERY=pr_data_admin:"where UserName='FN'"
以上方法因为我电脑上的Oracle版本对不上，总是报错，后来采用java的方法，本文主要介绍如何使用java读写Oracle的BLOB字段，达到复制转移BLOB数据的目的。代码如下，加入ojdbc.jar，复制代码可以直接运行：
DBConnection.java
package com.zolly.blob;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBConnection {
public static Connection getDBConnectionFrom() throws Exception {
Connection con = null;
String driver = "oracle.jdbc.driver.OracleDriver";// database driver
String url = "jdbc:oracle:thin:@192.168.1.2:1545:ORCL";// database URL
String user = "user1"; // database name
String password = "password1"; // database Password
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
return con;
}
public static Connection getDBConnectionTo() throws Exception {
Connection con = null;
String driver = "oracle.jdbc.driver.OracleDriver";// database driver
String url = "jdbc:oracle:thin:@192.168.1.3:1521:ORCL";// database URL
String user = "user2"; // database name
String password = "password2"; // database Password
Class.forName(driver);
con = DriverManager.getConnection(url, user, password);
return con;
}
}
BLOBUtil.java
package com.zolly.blob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import oracle.sql.BLOB;
public class BLOBUtil {
@SuppressWarnings("deprecation")
public static void writeBLOB() {
Connection con = null;
long start = System.currentTimeMillis(); // count runtime
InputStream fin = null;
OutputStream outStream = null;
String path = "E:\\requestor.txt";
File file = new File(path);
try {
con = DBConnection.getDBConnectionTo();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
ResultSet rs = stmt
.executeQuery("select pzPVStream from pr_data_admin where pxInsName='PEGA!BROWSER' 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("pzPVStream");
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();
con.close();
}
catch (Exception e) {
e.printStackTrace();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
public static void readBLOB() {
Connection con = null;
long start = System.currentTimeMillis(); // count runtime
String path = "E:\\requestor.txt";
File file = new File(path);
try {
con = DBConnection.getDBConnectionFrom();
con.setAutoCommit(false);
Statement stmt = con.createStatement();
ResultSet rs = stmt
.executeQuery("select pzPVStream from pr_data_admin where pxInsName='PEGA!BROWSER'");
// get blob form your table
if (rs.next()) {
BLOB blob = (BLOB) rs.getBlob("pzPVStream");
// 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");
}
con.close();
} catch (Exception e) {
e.printStackTrace();
}
long end = System.currentTimeMillis();
System.out.println(end - start);
}
public static void main(String[] args) {
BLOBUtil.readBLOB();
BLOBUtil.writeBLOB();
}
}