Get off to a fast start with DB2 V9 pureXML, Part 2: Create and populate a DB2 XML database developerWorks

Return to article

A simple Java example

As mentioned in the main article, we won't have time to discuss application development topics in detail here. But in case you're wondering how a Java application might insert XML file data into a DB2 table, included is a code excerpt below. This excerpt shows how to insert a record for John Smith into the "clients" table. As you'll recall, the main article showed how to accomplish the same task interactively.

If you're familiar with JDBC, you should find this example easy to understand. After declaring and defining necessary variables, establish a database connection following the standard DB2 process. Next, we prepare the INSERT statement. If you inspect the string containing this statement (defined in the variable "sqls"), you'll see that it looks like any other INSERT statement that you might write.

You also set each of the parameters. Again, the first three should be easy to understand -- they set integer and string values for SQL columns based on integer and varying length character strings. The fourth parameter targets your XML column ("contactinfo"). Here, you create a FileInputStream, passing it the name of your XML file and its computed length. This stream is passed to the setBinaryStream() method of your prepared statement object, effectively setting the target value of your "contactinfo" column to the contents of your XML file.

Listing 1. Java code excerpt for inserting XML data from a file into a DB2 XML column
. . . 
// connection variable 	 
Connection conn = null; 

// input variables 
int id = 77;
String name = "John Smith";
String status = "Gold";
String fn = "Client77.xml";    // this is our XML file 

// SQL INSERT string 
// we'll be inserting XML and non-XML data into the clients table  
String sqls = 
   "insert into clients (id, name, status, contactinfo) values (?, ?, ? ,?)";

// Here's a slight variation of the above INSERT string, 
// which we'd use if we wanted to validate the XML document against a schema
String sqlv = "INSERT INTO clients (id, name, status, contactinfo) " +
	"VALUES (?, ?, ?, xmlvalidate(? according to xmlschema id user1.mysample))";

// get a database connection 
// following standard DB2 procedure 
. . . 

// prepare and execute the INSERT statement
PreparedStatement insertStmt =  conn.prepareStatement(sqls);
insertStmt.setInt(1, id);
insertStmt.setString(2, name);
insertStmt.setString(3, status);
File file = new File(fn);
insertStmt.setBinaryStream(4, new FileInputStream(file), (int)file.length());
if (insertStmt.executeUpdate() != 1) {
      System.out.println("insertBinStream:: No record inserted.");
     . . . 
}
else { . . . }
. . . 

// close the connection following the standard DB2 process
. . .

This programming example can also help you learn how to validate XML data during an INSERT operation. Review the definition of variable "sqlv." Its SQL string looks like a standard INSERT statement, except that it invokes the XMLVALIDATE function against the fourth parameter specified in the VALUES clause. If you prepared and executed this statement, DB2 would try to validate the input XML document against the user1.mysample schema. If DB2 determined that the input document didn't comply with the XML schema, it would return an error, rather than inserting the row into the table.

Return to article