Extract Database Table to XML in Java Program

In this new series of XML tutorials, we will see a very simple example in Java, that will explain how to extract XML data from Oracle database table without much coding effort.

This example can be extended to support any database and is not specific to Oracle. But, you need to make sure that you use the right database drivers so that it works with your DBMS.Note that this is not the only approach to pull XML data from a table. I find this approach to be elegant as this is not dependent on any third party Java libraries.

Summary of Tutorial Contents:

• Approach
• Fully working Java Code example
• Common warnings / exception that you can possibly get
• Output example
• Ideas and upcoming posts

Approach:

We will be using WebRowSet interface in javax.sql.rowset package for this tutorial. The core API has an implementation called WebRowSetImpl, using which you can easily convert a query's ResultSet into XML format. I will be providing the complete Java code below. The code is fully commented so you can use the code itself as a step by step guide on this example.

Code:

The full Java code is provided below:

 1 import java.sql.*; /* imported for basic JDBC connectivity */
 2 import com.sun.rowset.WebRowSetImpl; /* This class is used to obtain XML data directly from the SQL query */
 3 class Conn {
 4   public static void main (String[] args) throws Exception
 5   {
 6    Class.forName ("oracle.jdbc.OracleDriver"); /* The driver used to connect to the database */
 7 
 8    Connection conn = DriverManager.getConnection
 9      ("jdbc:oracle:thin:@//localhost:1521/orcl", "scott", "tiger");
10     /* You should change this to point to your database suitably. Otherwise, this example will not work */
11    try {
12      Statement stmt = conn.createStatement();
13      try {
14        ResultSet query_set = stmt.executeQuery("select * from departments");
15        /* Pull out all the data from departments table through a database query */
16        try {
17          WebRowSetImpl my_xml_data = new WebRowSetImpl();
18          my_xml_data.populate(query_set);
19          /* Populates the result of the query into an XML directly into the rowset object */
20          my_xml_data.writeXml(System.out);
21          /* Prints the output of the query to the screen as XML */
22        } 
23        finally {
24           try { query_set.close(); } catch (Exception ignore) {}
25        }
26      } 
27      finally {
28        try { stmt.close(); } catch (Exception ignore) {}
29      }
30    } 
31    finally {
32      try { conn.close(); } catch (Exception ignore) {}
33    }
34   }
35 }

Warning / Exceptions:
When you compile this code, you may get the following exceptions

Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Check your database connection settings.

warning: com.sun.rowset.WebRowSetImpl is Sun proprietary API and may be removed in a future release

This is warning and you can ignore.

Example Output:
A sample output for this code is provided below

  1 <?xml version="1.0"?>
  2 <webRowSet xmlns="http://java.sun.com/xml/ns/jdbc" xmlns:xsi="http://www.w3.org/
  3 2001/XMLSchema-instance"
  4 xsi:schemaLocation="http://java.sun.com/xml/ns/jdbc http://java.sun.com/xml/ns/j
  5 dbc/webrowset.xsd">
  6   <properties>
  7     <command><null/></command>
  8     <concurrency>1008</concurrency>
  9     <datasource><null/></datasource>
 10     <escape-processing>true</escape-processing>
 11     <fetch-direction>1000</fetch-direction>
 12     <fetch-size>0</fetch-size>
 13     <isolation-level>2</isolation-level>
 14     <key-columns>
 15     </key-columns>
 16     <map>
 17     </map>
 18     <max-field-size>0</max-field-size>
 19     <max-rows>0</max-rows>
 20     <query-timeout>0</query-timeout>
 21     <read-only>true</read-only>
 22     <rowset-type>ResultSet.TYPE_SCROLL_INSENSITIVE</rowset-type>
 23     <show-deleted>false</show-deleted>
 24     <table-name><null/></table-name>
 25     <url><null/></url>
 26     <sync-provider>
 27       <sync-provider-name>com.sun.rowset.providers.RIOptimisticProvider</sync-pr
 28 ovider-name>
 29       <sync-provider-vendor>Sun Microsystems Inc.</sync-provider-vendor>
 30       <sync-provider-version>1.0</sync-provider-version>
 31       <sync-provider-grade>2</sync-provider-grade>
 32       <data-source-lock>1</data-source-lock>
 33     </sync-provider>
 34   </properties>
 35   <metadata>
 36     <column-count>4</column-count>
 37     <column-definition>
 38       <column-index>1</column-index>
 39       <auto-increment>false</auto-increment>
 40       <case-sensitive>false</case-sensitive>
 41       <currency>true</currency>
 42       <nullable>0</nullable>
 43       <signed>true</signed>
 44       <searchable>true</searchable>
 45       <column-display-size>22</column-display-size>
 46       <column-label>DEPARTMENT_ID</column-label>
 47       <column-name>DEPARTMENT_ID</column-name>
 48       <schema-name></schema-name>
 49       <column-precision>4</column-precision>
 50       <column-scale>0</column-scale>
 51       <table-name></table-name>
 52       <catalog-name></catalog-name>
 53       <column-type>2</column-type>
 54       <column-type-name>NUMBER</column-type-name>
 55     </column-definition>
 56     <column-definition>
 57       <column-index>2</column-index>
 58       <auto-increment>false</auto-increment>
 59       <case-sensitive>true</case-sensitive>
 60       <currency>false</currency>
 61       <nullable>0</nullable>
 62       <signed>true</signed>
 63       <searchable>true</searchable>
 64       <column-display-size>30</column-display-size>
 65       <column-label>DEPARTMENT_NAME</column-label>
 66       <column-name>DEPARTMENT_NAME</column-name>
 67       <schema-name></schema-name>
 68       <column-precision>30</column-precision>
 69       <column-scale>0</column-scale>
 70       <table-name></table-name>
 71       <catalog-name></catalog-name>
 72       <column-type>12</column-type>
 73       <column-type-name>VARCHAR2</column-type-name>
 74     </column-definition>
 75     <column-definition>
 76       <column-index>3</column-index>
 77       <auto-increment>false</auto-increment>
 78       <case-sensitive>false</case-sensitive>
 79       <currency>true</currency>
 80       <nullable>1</nullable>
 81       <signed>true</signed>
 82       <searchable>true</searchable>
 83       <column-display-size>22</column-display-size>
 84       <column-label>MANAGER_ID</column-label>
 85       <column-name>MANAGER_ID</column-name>
 86       <schema-name></schema-name>
 87       <column-precision>6</column-precision>
 88       <column-scale>0</column-scale>
 89       <table-name></table-name>
 90       <catalog-name></catalog-name>
 91       <column-type>2</column-type>
 92       <column-type-name>NUMBER</column-type-name>
 93     </column-definition>
 94     <column-definition>
 95       <column-index>4</column-index>
 96       <auto-increment>false</auto-increment>
 97       <case-sensitive>false</case-sensitive>
 98       <currency>true</currency>
 99       <nullable>1</nullable>
100       <signed>true</signed>
101       <searchable>true</searchable>
102       <column-display-size>22</column-display-size>
103       <column-label>LOCATION_ID</column-label>
104       <column-name>LOCATION_ID</column-name>
105       <schema-name></schema-name>
106       <column-precision>4</column-precision>
107       <column-scale>0</column-scale>
108       <table-name></table-name>
109       <catalog-name></catalog-name>
110       <column-type>2</column-type>
111       <column-type-name>NUMBER</column-type-name>
112     </column-definition>
113   </metadata>
114   <data>
115     <currentRow>
116       <columnValue>10</columnValue>
117       <columnValue>Administration</columnValue>
118       <columnValue>200</columnValue>
119       <columnValue>1700</columnValue>
120     </currentRow>
121     <currentRow>
122       <columnValue>20</columnValue>
123       <columnValue>Marketing</columnValue>
124       <columnValue>201</columnValue>
125       <columnValue>1800</columnValue>
126     </currentRow>
127     <currentRow>
128       <columnValue>30</columnValue>
129       <columnValue>Purchasing</columnValue>
130       <columnValue>114</columnValue>
131       <columnValue>1700</columnValue>
132     </currentRow>
133   </data>
134 </webRowSet>

Upcoming posts:

You can see that this output gives  neat XML representation of the data in the table. It also gives some metadata information about this table which is very useful. In the next post, we will see how to get only the data we want from the table. (and ignore the metadata). We will also see some other approaches to pull data in XML format. I will also write a code to print table data into PDF using Java. Stay Connected.