最近根据需要 写了一个将oracle Blob数据迁移到 另外一个oracle 中的小工具，下面是代码，测试通过。
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Properties;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.BLOB;
public class OraToOra {
public static final int REPORT_FILE_TYPE_HTML = 1;
public static final int REPORT_FILE_TYPE_EXCEL = 2;
public static final int REPORT_FILE_TYPE_PDF = 3;
public static final int REPORT_FILE_TYPE_XML = 4;
public static final int REPORT_FILE_TYPE_CSV = 5;
public Connection connectionSrc;
public Connection connectionDesc;
public OraToOra() {
}
/**
* Connect database
* @param jdbc
* @param user
* @param password
* @return
* @throws SQLException
*/
public Connection connect(String jdbc, String user, String password)
throws SQLException {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(jdbc, user, password);
return conn;
}
public static void main(String[] args) {
OraToOra oto = new OraToOra();
BLOB blob = null;
String name="";
String scheduleId="";
int newScheduleId=0;
if(args.length<3)
{
usage();
}else
{
name=args[0];
scheduleId=args[1];
newScheduleId=new Integer(args[2]).intValue();
PreparedStatement pstmt = null;
try {
InputStream in = new BufferedInputStream(new FileInputStream(name));
Properties p = new Properties();
p.load(in);
String jdbcOld =p.getProperty("jdbcOld");
String userOld = p.getProperty("userOld");
String passwordOld =p.getProperty("passwordOld");
//Get trm1.0 old database connection
Connection connOld = oto.connect(jdbcOld, userOld, passwordOld);
String jdbcNew = p.getProperty("jdbcNew");
String userNew = p.getProperty("userNew");
String passwordNew = p.getProperty("passwordNew");
//Get trm1.5 new database connection
Connection connNew = oto.connect(jdbcNew, userNew, passwordNew);
connOld.setAutoCommit(false);
connNew.setAutoCommit(false);
//inset a empty blob
///////////////////////////////
Statement st = connOld.createStatement();
String query = "select * from report where schedule_id ="+scheduleId;
ResultSet rs =st.executeQuery(query);
while (rs.next()) {
///////////////////
//insert empty blob
/////////////////
pstmt = connNew.prepareStatement("insert into Report(Report_ID,Generated_Time,FileType,Schedule_ID,Html_Report_File,Xls_Report_File,Pdf_Report_File,Xml_Report_File,Csv_Report_File) values(?,?,?,?,empty_blob(),empty_blob(),empty_blob(),empty_blob(),empty_blob())");
int report_id = rs.getInt("Report_ID");
System.out.println("report_id " + report_id);
pstmt.setInt(1, report_id);
Timestamp tt = rs.getTimestamp("Generated_Time");
pstmt.setTimestamp(2, tt);
int fileType = rs.getInt("FileType");
pstmt.setString(3, String.valueOf(fileType));
System.out.println("fileType " + fileType);
int schedule_id = rs.getInt("Schedule_ID");
pstmt.setInt(4, newScheduleId);
System.out.println("schedule_id " + schedule_id);
pstmt.executeUpdate();
pstmt.close();
String select = "";
switch (fileType) {
case REPORT_FILE_TYPE_HTML:
select = "Html_Report_File";
break;
case REPORT_FILE_TYPE_EXCEL:
select = "Xls_Report_File";
break;
case REPORT_FILE_TYPE_PDF:
select = "Pdf_Report_File";
break;
case REPORT_FILE_TYPE_XML:
select = "Xml_Report_File";
break;
case REPORT_FILE_TYPE_CSV:
select = "Csv_Report_File";
break;
default:
break;
}
////////////////////////
//select point
////////////////////////
String s_sql = "select " + select
+ " from report where Report_ID= ? for update";
System.out.println(s_sql);
pstmt = connNew.prepareStatement(s_sql);
pstmt.setInt(1, report_id);
ResultSet rset = pstmt.executeQuery();
if (rset.next())
blob = (BLOB) rset.getBlob(1);
pstmt =connNew.prepareStatement("update report set " + select
+ "=? where Report_ID=?");
BLOB oldBlob = ((OracleResultSet)rs).getBLOB("Report_File");
System.out.println(">>>>byte length: "+oldBlob.length());
InputStream fin=oldBlob.getBinaryStream();
OutputStream out = blob.getBinaryOutputStream();
int size = blob.getBufferSize();
byte[] buffer = new byte[size];
int length = -1;
while ((length = fin.read(buffer)) != -1)
out.write(buffer, 0, length);
fin.close();
out.close();
pstmt.setBlob(1, blob);
pstmt.setInt(2, report_id);
pstmt.executeUpdate();
pstmt.close();
}
connNew.commit();
rs
（本文已被浏览 次） | | |
相关评论
