Exchange data in different databases using XML

XML has become the mainstream in different languages and data sources. In these DRP projects, Wang Yong also mentioned the use of XML to exchange data in different databases for security and flexibility. However, he just mentioned that there is such a method that does part of the work. I think this technology is very practical. I will complete this process here.

 

Development Environment:

SQL Server 2008 database, Oracle database, PL/SQL, and myeclipse development environment.

Export the database in a table of the SQL database to an XML file, read the XML file through dom4j, and write the XML data to the Oracle database.

1. Create a target database table in the analysis table structure.

Since it is a different database, but the database table results should be the same, in actual projects, it is generally the first analysis table structure. Use an existing XML file or database table to create a corresponding table in the target database. The table structure of the original database and the target database is consistent.

 

2. export data from the SQL Server database to an XML file.

Generally, for the sake of security, the database is not open and only the data in the database can be exported to XML. SQL Server exports data to XML as follows:

Use SQL query statements

select * from classInfo for xml path('my'),root('myRoot')

 

Here, classinfo is the table name, and for XML Path is the key statement, indicating that the query results are output in XML format. Brackets and my in them can be omitted. It represents a row directory of the XML file. Each my represents a record of the database. My is a node name named by myself and can be any name. Root ('myroot') indicates that the root directory of the XML file is myroot. You can also name it yourself.

The record in the database is:

The generated XML file is:

<Myroot> <my> <classno> 07 </classno> <classes> 07 </classes> <Grade> 2008 </grade> <classsize> 90 </classsize> <collegeno> 01 </collegeno> <specialtyno> 01001 </specialtyno> <assistant> </Assistant> <remark> none </remark> <isvalid tive> Yes </isvalid tive> </My> <my> <classno> 09 </classno> <classes> music </classes> <Grade> example: 2008 </grade> <classsize> 90 </classsize> <collegeno> 06 </collegeno> <specialtyno> 09 </specialtyno> <assistant> </Assistant> <remark> none </remark> <isvalid tive> Yes </isvalid tive> </My> <my> <classno> 123 </classno> <classes> FAF </classes> <Grade> example: 2008 </grade> <classsize> 22 </classsize> <collegeno> 01 </collegeno> <specialtyno> 234r </specialtyno> <assistant> 234 </Assistant> <remark> AF </remark> <isvalid tive> Yes </isvalid tive> </My> <my> <classno> 323 </classno> <classes> 33 </classes> <Grade> 20093 </grade> <classsize> 33 </classsize> <collegeno> 01 </collegeno> <specialtyno> and </specialtyno> <assistant> 234 </Assistant> <remark> 333 </remark> <isvalid tive> Yes </isvalid tive> </My> <my> <classno> 999 </classno> <classes> Information Class 2 </classes> <Grade> 2008 </grade> <classsize> 80 </classsize> <collegeno> 01 </collegeno> <specialtyno> 03 </specialtyno> <assistant> 234 </Assistant> <remark> good </remark> <isvalid tive> Yes </isvalid tive> </My> </myroot>

3. Configure the development environment and write statements for reading XML files and writing data to the database.

This article describes how to read XML files. Here, we use dom4j to read the configuration file and read the data in XML.

Database Assistant class, responsible for connecting and closing the database

Package COM. lsh. testxml; import Java. SQL. connection; import Java. SQL. drivermanager; import Java. SQL. preparedstatement; import Java. SQL. sqlexception;/*** database tool class * @ author Li shouhong **/public class dbutil {/*** get database connection * @ return */public static connection getconnection () {connection conn = NULL; try {class. forname ("oracle. JDBC. driver. oracledriver "); string dburl =" JDBC: oracle: thin: @ 127.0.0.1: 1521: orcl "; strin G username = "DRP"; string Password = "DRP"; Conn = drivermanager. getconnection (dburl, username, password);} catch (classnotfoundexception e) {e. printstacktrace ();} catch (sqlexception e) {e. printstacktrace ();} return conn;} // close preparedstatementpublic static void close (preparedstatement pstmt) {If (pstmt! = NULL) {try {pstmt. close ();} catch (sqlexception e) {e. printstacktrace () ;}}// disable connectionpublic static void close (connection conn) {If (Conn! = NULL) {try {conn. Close () ;}catch (sqlexception e) {e. printstacktrace ();}}}}

Execute the program for reading XML and writing data to the mongoel database:

Package COM. lsh. testxml; import Java. SQL. connection; import Java. SQL. preparedstatement; import Java. util. iterator; import Java. util. list; import Org. dom4j. document; import Org. dom4j. extends entexception; import Org. dom4j. element; import Org. dom4j. io. saxreader;/*** use dom4j to read XML, then write it into the mongoel database * @ author Li shouhong -- seventh Information Technology Improvement Course * @ creation date: 2012-2-18 */public class test {/*** @ Param ARGs */public static void main (string [] ARGs) {Try {// my. XML is located in the classpath path and can also be placed in other paths. Here, you only need to specify the path for document DOC = new saxreader (). read (thread. currentthread (). getcontextclassloader (). getresourceasstream ("My. XML "); xmltodb (Doc. selectnodes ("/myroot/my");} catch (incluentexception e) {// todo auto-generated catch blocke. printstacktrace () ;}} Private Static void xmltodb (list itemlist) {string SQL = "insert into classinfo (classno, classes, grade, classsize, Collegeno, specialtyno, assistant, remark, isvalid tive) "+" values (?,?,?,?,?,?,?,?,?) "; Connection conn = NULL; preparedstatement pstmt = NULL; try {conn = dbutil. getconnection (); pstmt = Conn. preparestatement (SQL); // iteratively obtains the values of each XML node and regards the data read from XML as a listfor (iterator iter = itemlist. iterator (); ITER. hasnext ();) {element ELT = (element) ITER. next (); string classno = ELT. elementtext ("classno"); string classes = ELT. elementtext ("classes"); string grade = ELT. elementtext ("Grade"); string classsize = ELT. elementtext ("classsize"); string specialtyno = ELT. elementtext ("specialtyno"); string collegeno = ELT. elementtext ("collegeno"); string assistant = ELT. elementtext ("assistant"); string remark = ELT. elementtext ("remark"); string isequaltive = ELT. elementtext ("isvalid tive"); pstmt. setstring (1, classno); pstmt. setstring (2, classes); pstmt. setstring (3, grade); pstmt. setstring (4, classsize); pstmt. setstring (5, specialtyno); pstmt. setstring (6, collegeno); pstmt. setstring (7, assistant); pstmt. setstring (8, remark); pstmt. setstring (9, is%tive%%pstmt.addbatch(%%%%%pstmt.exe cutebatch (); system. out. println ("OK");} catch (exception e) {e. printstacktrace ();} finally {dbutil. close (conn); dbutil. close (pstmt );}}}

PS: note that SQL statements are case-insensitive and XML statements are case-sensitive.