Blob field imported to DB2 in Oracle

1. Background

In the project, BLOB fields are used to store packets. Recently, some customers need to use the DB2 database to deploy applications. All users have to import the Oracle initialization script to DB2 and create the DB2 initialization script.

2. Problem

How can I import a table containing BLOB fields in Oracle to DB2? I did not find it on the Internet and decided to write a program to solve the problem.

3. Solve the Problem

Use the JDBC connection to first find the table in Oracle and then insert it into DB2. Create a new Java project, and then add the Oracle and DB2 driver packages, such:

Source code of db2connectionfactory is:

Package COM. ylink. export; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. sqlexception; public class db2connectionfactory {final static string driver = "com. IBM. db2.jcc. db2driver "; Final Static string constr =" JDBC: DB2: // 172.166.212: 50000/TPS "; Final Static string username =" db2admin "; Final Static string userpass =" db2admin "; /*** obtain the database connection * @ return */public static connection getconnection () {connection con = NULL; try {class. forname (driver); con = drivermanager. getconnection (constr, username, userpass); con. setautocommit (false); // set not to automatically commit transactions} catch (sqlexception e) {system. out. println ("SQL statement error" + E. getmessage ();} catch (classnotfoundexception e) {system. out. println (E. getmessage () ;}return con ;}}

Ioperationcore Interface source code:

/*** Common database operation encapsulation */package COM. ylink. export; import Java. lang. reflect. invocationtargetexception; import Java. SQL. resultset; import Java. SQL. sqlexception; import Java. util. list;/** Class Name: ioperationcore <br> ** function: this interface encapsulates most methods for Database Operations <br> **/public interface ioperationcore {/** SQL update statement ** @ Param querystring query statement * @ return returns a <code> resultset </code> result set ** @ exception sqlexception */resultset executequery (string querystring) throws sqlexception;/*** SQL update statement ** @ Param updatestring database update statement * @ return: updates the number of affected database rows ** @ exception sqlexception */INT executeupdate (string updatestring) throws sqlexception; @ suppresswarnings ("unchecked") Public <t> List <t> queryforlist (string SQL, class <t> clazz, object... params) throws sqlexception, instantiationexception, illegalaccessexception, invocationtargetexception, classnotfoundexception; /*** release system connection resources ** @ exception sqlexception if the function fails to be disabled, <code> sqlexception </code> */void dispose () throws sqlexception ;}

Operationcoreimpl source code:

Package COM. ylink. export; import Java. lang. reflect. invocationtargetexception; import Java. lang. reflect. method; import Java. SQL. connection; import Java. SQL. preparedstatement; import Java. SQL. resultset; import Java. SQL. resultsetmetadata; import Java. SQL. sqlexception; import Java. SQL. statement; import Java. util. arraylist; import Java. util. list;/*** Class Name: operationcoreimplements <br> ** function: This class implements all the ioperationcore interfaces. Method <br> */public class operationcoreimpl implements ioperationcore {protected connection aconnection = NULL; protected statement PS = NULL; protected resultset rs = NULL; protected resultsetmetadata rsmd = NULL; protected static operationcoreimpl m_instance = NULL;/*** Singleton (Single Instance (state) mode, method used to generate a unique object instance ** @ return operationcoreimplements an instance * @ throws exception */public static operationcoreimpl create Factory () throws exception {If (m_instance = NULL) m_instance = new operationcoreimpl (); Return m_instance;}/** @ exception */Public operationcoreimpl () throws exception {Init ();} private void Init () throws exception {aconnection = oracleconnectionfactory. getconnection ();}/*** release system connection resource */Public void dispose () {try {If (RS! = NULL) Rs. Close ();} catch (sqlexception e) {e. printstacktrace ();} Try {If (PS! = NULL) PS. Close ();} catch (sqlexception e) {e. printstacktrace ();} Try {If (aconnection! = NULL) aconnection. close ();} catch (sqlexception e) {e. printstacktrace ();}} /*** returns the resultset object ** @ Param querystring * query statement * @ return returns a <code> resultset </code> result set ** @ exception sqlexception */Public resultset executequery (string querystring) {try {PS = aconnection. createstatement (); RS = ps.exe cutequery (querystring);} catch (sqlexception e) {rs = NULL; E. printstacktrace ();} Return Rs;} // returns list generic JD BC direct connection @ suppresswarnings ("unchecked") Public <t> List <t> queryforlist (string SQL, class <t> clazz, object... params) throws sqlexception, instantiationexception, illegalaccessexception, invocationtargetexception, classnotfoundexception {If (clazz = NULL) {Throw new partition ("clazz is null");} resultset rs = NULL; preparedstatement PS = NULL; try {list <t> resultlist = new arraylist <t> (); PS = Aconnection. preparestatement (SQL); If (Params! = NULL) {for (INT I = 0; I <Params. length; I ++) {ps. setobject (I + 1, Params [I]) ;}} rs = ps.exe cutequery (); t = NULL; method [] allmethod = clazz. getmethods (); List <method> settermethodlist = new arraylist <method> (); For (method M: allmethod) {If (M. getname (). startswith ("set") {settermethodlist. add (m) ;}} string columnname = NULL; Class parametertype = NULL; If (RS! = NULL) {While (RS. next () {T = clazz. newinstance (); For (method M: settermethodlist) {columnname = m. getname (). substring (3, 4 ). tolowercase () + M. getname (). substring (4, M. getname (). length (); parametertype = m. getparametertypes () [0]; If (parametertype. isprimitive () {If (parametertype = Boolean. type) {M. invoke (T, Rs. getboolean (columnname);} else if (parametertype = byte. type) {M. invoke (T, Rs. getbyt E (columnname);} else if (parametertype = short. type) {M. invoke (T, Rs. getshort (columnname);} else if (parametertype = character. type) {M. invoke (T, Rs. getstring (columnname ). charat (0);} else if (parametertype = integer. type) {M. invoke (T, Rs. getint (columnname);} else if (parametertype = long. type) {M. invoke (T, Rs. getlong (columnname);} else if (parametertype = float. type) {M. invoke (T, Rs. getf Loat (columnname);} else if (parametertype = double. type) {M. invoke (T, Rs. getdouble (columnname) ;}} else {M. invoke (T, Rs. getObject (columnname) ;}} resultlist. add (t) ;}} return resultlist;} finally {dispose ();}} /*** add, delete, and modify operations ** @ Param updatestring * database update statement * @ return updates the number of affected rows of the database ** @ exception sqlexception */Public int executeupdate (string updatestring) {int deletedrows = 0; try {aconnection. setautoco MMIT (false); PS = aconnection. createstatement (); deletedrows = ps.exe cuteupdate (updatestring); aconnection. commit ();} catch (sqlexception ex) {system. out. println ("database write operation failed! "); If (aconnection! = NULL) {try {aconnection. rollback (); system. out. println ("JDBC transaction rollback succeeded");} catch (sqlexception e) {system. out. println ("JDBC transaction rollback failed"); E. printstacktrace () ;}}return effectedrows ;}}

Oracleconnectionfactory source code:

Package COM. ylink. export; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. sqlexception; public class oracleconnectionfactory {final static string driver = "oracle. JDBC. driver. oracledriver "; Final Static string constr =" JDBC: oracle: thin: @ 172.169.70: 1521: TPS "; Final Static string username =" TPS "; final Static string userpass = "TPS";/*** get database connection * @ return */public static connection getconnection () {connection con = NULL; try {class. forname (driver); con = drivermanager. getconnection (constr, username, userpass); con. setautocommit (false); // set not to automatically commit transactions} catch (sqlexception e) {system. out. println ("SQL statement error" + E. getmessage ();} catch (classnotfoundexception e) {system. out. println (E. getmessage () ;}return con ;}}

Run export to import data from Oracle tables to DB2.