oracle如何导出Blob和clob大字段

需要一个ExportBlobJianrong .java和config.xml,文件在下面提供：

package thinkinjava;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Arrays;


import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;


import org.w3c.dom.Document;
import org.w3c.dom.NodeList;


public class ExportBlobJianrong {


private static String CONNSTRING = "jdbc:oracle:thin:@198.167.14.13:1593:kkt";

private static String USERNAME = "user";

private static String PASSWORD = "oracle";

private static String TABLE = "t_new";

private static String WHERE = "1=1 and id in ('nihao')";

private static String ID = "ID";

private static String OUTPUT = "kkt.sql"; 

static {
try {
File file = new File("config.xml");
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder buider = factory.newDocumentBuilder();
Document parse = buider.parse(file);
NodeList root = parse.getElementsByTagName("resource");
if(root != null & root.getLength() >0){
CONNSTRING = parse.getElementsByTagName("connectstr").item(0).getFirstChild().getNodeValue();
USERNAME = parse.getElementsByTagName("username").item(0).getFirstChild().getNodeValue();
PASSWORD = parse.getElementsByTagName("password").item(0).getFirstChild().getNodeValue();
TABLE = parse.getElementsByTagName("table").item(0).getFirstChild().getNodeValue();
WHERE = parse.getElementsByTagName("filtersql").item(0).getFirstChild().getNodeValue();
ID = parse.getElementsByTagName("id").item(0).getFirstChild().getNodeValue();
OUTPUT = parse.getElementsByTagName("output").item(0).getFirstChild().getNodeValue();
}
} catch (Exception e) {
e.printStackTrace();
}
}

public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(CONNSTRING, USERNAME, PASSWORD);
String sql = "select * from " + TABLE + " t where " + WHERE;
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
StringBuffer dump = new StringBuffer("-- export from sql\r\n-- ").append(sql).append("\r\n");
dump.append("declare\r\n");
dump.append("v_blob blob;\r\n");
dump.append("v_clob clob;\r\n");
dump.append("v_lang varchar(50);\r\n");
dump.append("v_terr varchar(50);\r\n");
dump.append("v_schar varchar(50);\r\n");
dump.append("v_dchar varchar(50);\r\n");
dump.append("begin\r\n");
dump.append("delete from "+TABLE+" t where "+ WHERE+";\r\n");
dump.append("v_schar := 'AMERICAN_AMERICA.AL16UTF16';\r\n");
dump.append("select value into v_lang from nls_database_parameters where Parameter in ('NLS_LANGUAGE');\r\n");
dump.append("select value into v_terr from nls_database_parameters where Parameter in ('NLS_TERRITORY');\r\n");
dump.append("select value into v_dchar from nls_database_parameters where Parameter in ('NLS_NCHAR_CHARACTERSET');\r\n");
dump.append("v_dchar := v_lang || '_' || v_terr || '.' || v_dchar;\r\n");
while(rs.next()) {
String insert = "insert into " + TABLE;
String cols = " (";
String vals = " values (";
ResultSetMetaData md = rs.getMetaData();
String idString[] = ID.split(",");
StringBuffer lobDump = new StringBuffer();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
for(int i = 0; i < md.getColumnCount(); i ++) {
String c = md.getColumnName(i + 1);
Object v = rs.getObject(c);
String v1 = null;
if(v instanceof java.sql.Blob) {
v1 = "empty_blob()";
lobDump.append(createBlobDump(TABLE, WHERE, idString,rs, c, (Blob)v));
}
else if (v instanceof java.sql.Date){
v1 = v == null ? "null" : "to_date('"+sdf.format(v)+"','yyyy-MM-dd hh24:mi:ss')";
}
else if(v instanceof java.sql.Clob) {
v1 = "empty_clob()";
lobDump.append(createClobDump(TABLE, WHERE,  idString,rs, c, (Clob)v));
} else {
v1 = v == null ? "null" : "'" + v.toString() + "'";
}
cols += c + (i + 1 < md.getColumnCount() ? ", " : ")");
vals += v1 + (i + 1 < md.getColumnCount() ? ", " : ")");
}
insert += cols + vals + ";";
dump.append(insert).append("\r\n");
dump.append(lobDump).append("\r\n");
}
dump.append("commit;\r\nend;\r\n/\r\n");
System.out.println(dump);

File out = new File(OUTPUT);
if(!out.exists()){
out.createNewFile();
}
BufferedWriter bw = new BufferedWriter(new FileWriter(out));
bw.write(dump.toString());
bw.close();
} catch (Exception e) {
e.printStackTrace();
}


}




private static String createClobDump(String table, String where,String[] idString,ResultSet rs, String col, Clob v) throws SQLException, IOException {
StringBuffer sb = new StringBuffer();
String tiaojian ="1=1 ";
String kg ;
for(String ik:idString){
kg = rs.getString(ik);
if(kg==null){
tiaojian = tiaojian +" and "+ik+" is null";
}else{
tiaojian =tiaojian +" and "+ ik+"='"+kg+"'";
}
}
String updateSql = "select " + col + " into v_clob from " + table + " where "+tiaojian +" for update;";
sb.append(updateSql).append("\r\n");
Reader r = v.getCharacterStream();
char[] cs = new char[50];
int len = 0;
while((len = r.read(cs)) > 0) {
String str = new String(Arrays.copyOf(cs, len));
sb.append("dbms_lob.append(v_clob, utl_raw.cast_to_nvarchar2(utl_raw.convert('");
for (int i = 0; i < str.length(); i ++) {
String ncs = Integer.toHexString(str.codePointAt(i) & 0xffff);
for(int j = ncs.length(); j < 4; j ++) {
ncs = "0" + ncs;
}
sb.append(ncs);
}
sb.append("', v_dchar, v_schar)));\r\n");
}

return sb.toString();
}

private static String createBlobDump(String table, String where, String[] idString,ResultSet rs, String col, Blob v) throws SQLException, IOException {
StringBuffer sb = new StringBuffer();
String tiaojian ="1=1";
String kg ;
for(String ik:idString){
kg = rs.getString(ik);
if(kg==null){
tiaojian = tiaojian +" and "+ik+" is null";
}else{
tiaojian =tiaojian +" and "+ ik+"='"+kg+"'";
}
}
String vId = rs.getString(ID);
String []vidStrings = vId.split(",");
String updateSql = "select " + col + " into v_blob from " + table + " where "+tiaojian +" for update;";
sb.append(updateSql).append("\r\n");
InputStream r = v.getBinaryStream();
byte[] bs = new byte[50];
int len = 0;
while((len = r.read(bs)) > 0) {
sb.append("dbms_lob.append(v_blob, utl_raw.cast_to_raw(utl_raw.cast_to_varchar2('");
for (int i = 0; i < len; i ++) {
byte b = bs[i];
String hex = Integer.toHexString(b & 0xFF);
if (hex.length() == 1) {
hex = '0' + hex;
} 
sb.append(hex);
}
sb.append("')));\r\n");
}

return sb.toString();
}

}

config.xml

<?xml version="1.0" encoding="UTF-8"?>
<resource>
<connectstr>jdbc:oracle:thin:@198.167.14.13:1593:kkt</connectstr>
<username>user</username>
<password>oracle</password>
<table>t_new</table>
<filtersql>1=1 and id in ('kk')</filtersql>
<id>ID,TwoId</id>
<output>a.sql</output>
</resource>

config.xml文件配置说明：

1.connectstr配置数据源

2.username数据库用户名

3.password数据库密码

4.table要导出的表名

5.filtersql过滤的条件

6.id导出表的主键，如果要联合主键的话，要将两个主键都加上去

7.output导出的sql会写在那个文件下面。