Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Developing JDBC Applications with DB2's pureXML : Page 2

Combining XML's features with JDBC provides powerful choices for manipulating data within your Java applications. Find out which new DB2 features simplify this integration.


WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

XML in a JDBC App

Now that you know the basics of using XQuery to transverse through pureXML data, you're ready to develop Java applications that query through native XML data. You will walk through a step-by-step approach to developing a typical JDBC application that selects and inserts XML data. This tutorial uses DB2 Developer's WorkBench IDE, which is new in the DB2 Version 9 release and is built upon the Eclipse platform (You can download the Workbench for free), but DB2 Version 9 also integrates quite nicely with other IDEs. For example, if you are a PHP developer, you can use the open source Zend Core for IBM IDE.

Once you have the Developer WorkBench installed, open it up, open up the Java perspective, and then click the icon beside the Data icon in the top right-hand corner: DWB-> Other-> Java (see Figure 2).

Figure 2. Java Perspective

Create a new Java project: File-> New-> Project-> Java Project (see Figure 3).

Click to enlarge

Figure 3. Creating a New Java Project

The new project will be called Toronto, so fill that in for the Project Name and then click Next.

Now you can create Java classes along with the main method. You can insert your code into the project in a number of ways. For example, suppose you have a working Java program called Toronto.Java. You can simply cut and paste your example into the DWB as needed.

First, associate the JCC type 4 Java driver with this project to make sure it is included in the project properties. Right click on the Toronto package you just created and choose Properties (see Figure 4) -> Java Build Path -> Add External Jars. Select the db2jcc.jar and db2cc_license_cisuz.jar files and add them to your build path (see Figure 5). The *.jar files are usually located in your $DB2PATH/sqllib/java directory.

Click to enlarge

Figure 4. Adding *.jar Files to the Project

Click to enlarge

Figure 5. Location of *.jar Files

Create a new class in which to paste all of your code: File -> New -> Class. You should include a main method as well. Click on public static void main(String[] args) in the window that is displayed (see Figure 6, in which the example class created is called Toronto).

Click to enlarge

Figure 6. Java Code with Newly Created Class View

Key Parts of the Java Code and Gotchas
The following code snippet loads up the JCC driver in the Java code. Pay attention to the connection string:

/** * Connect to the database. * Fill in your own URI and password. */ static Connection getConnection() throws Exception { Connection connection = null; String url = "jdbc:db2://localhost:50000/ANSON"; String user = "user"; String pass = "pass"; com.ibm.db2.jcc.DB2Driver.class.newInstance(); connection = DriverManager.getConnection(url, user, pass); return connection; }

You saw how to load the Java driver before, but you can use this snippet by just adding your own userID and password.

Now look at an example of an actual XQuery of XML data. The following code snippet searches all the U.S. governors for a particular state during the past 50 years and returns all the relevant data in a GovernorList:

try{ // database connection Connection conn = Conn.getConn(); // We are using xquery and FLWOR expression. // hardcode values into our Java application. String query = "xquery for $y in db2-fn:xmlcolumn" + "('ANSONK.STATEINFO')/State " + "where $y/Country/city=\"Tallahassee\" and $y/Country/state=\"FL\" " + "return <GovernorList> { $y/Governor } </GovernorList>"; PreparedStatement selectStmt = conn.prepareStatement(query); ResultSet rs = selectStmt.executeQuery(); // go through all the items and print. while(rs.next() ){ System.out.println(rs.getString(1)); } // release all resources . . . // catch and handle any exceptions . . . }

This is just an example of what you can do with the Java project. At this point, you actually can add queries that are pertinent to your organization and run the Java program as per below.

Once you are finished writing all the source code, you are ready to run the application: Run-> Run-> Select Java application-> click New button -> leave the defaults as is -> click RUN.

To view the output of the project, right click on the Project and select Refresh. You will see all the newly listed files as part of the project.

Fit to Your Needs

Building upon the above examples, you can extend this application to fit the needs of your organization and work with XML data in the most resourceful fashion.

Anson Kokkat works as a software development team lead in the DB2 UDB Advanced Support team. His areas of specialization include WebSphere and DB2 integration, JDBC, stored procedures, and .NET. He has been working at IBM since 1999.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date