oracle如何导出Blob和clob大字段

秒速五厘米 2022-06-16 00:21 327阅读 0赞

需要一个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会写在那个文件下面。