JDBC at 2009 PASS Summit Unite
Several JDBC team members attended the 2009 PASS Summit Unite conference November 2-5, 2009 in Seattle. During the “Developing Java Applications Against SQL Server 2008” session, I showed a demo illustrating some of the features of our 2.0 release (driver auto-loading, integrated authentication, sending & retrieving xml data using SQLXML, and tracing). I’ve provided snippets of the demo code for the updating and retrieving of data using SQLXML and SAX (Simple API for XML) here on the blog for your reference.
The demo used Microsoft SQL Server JDBC Driver 2.0 with SQL Server 2008 and the AdventureWorks2008 database. The table used is called Person.Person and represented customer information for clients of AdventureWorks. One of the columns in this table is called Demographics and stores survey results in xml format.
The first part of the demonstration illustrated updating the Demographics column using SAXResult object with SQLXML and xml data stored in a file. You can assume that the helper method takeSurvey() returns the name of an xml file containing xml data representing survey results.
Connection conn = null;
PreparedStatement pstmt = null;
//Generate survey data for the customer
//and store it in an xml file
String surveyFileName = takeSurvey();
String firstName = "Sarah";
String lastName = "Marshall";
try
{
conn = DriverManager.getConnection(url);
//Create prepared statement that will update
//the xml data
pstmt = conn.prepareStatement(
"UPDATE [Person].[Person] " +
"SET [Demographics] = ? " +
"WHERE [FirstName] = ? AND [LastName] = ?");
//Create SQLXML object from the connection
SQLXML sqlxml = conn.createSQLXML();
//Set the SAX Result in the SQLXML
SAXResult sxResult =
sqlxml.setResult(SAXResult.class);
//Create a transformer to send the File contents
//to the SAX Result
SAXTransformerFactory stFactory =
(SAXTransformerFactory)
TransformerFactory.newInstance();
Transformer transformer =
stFactory.newTransformer();
//Send the File contents to the SAX Result
transformer.transform(new StreamSource(
new File(surveyFileName)), sxResult);
//Call the PreparedStatement setSQLXML API
//method to set the value
pstmt.setSQLXML(1, sqlxml);
pstmt.setString(2, firstName);
pstmt.setString(3, lastName);
//Execute prepared statement
pstmt.executeUpdate();
}
finally
{
//Clean up
if(pstmt != null)
{
try { pstmt.close(); }
catch(SQLException e) {}
}
if(conn != null)
{
try { conn.close(); }
catch(SQLException e) {}
}
}
The second part of the demo showed how to retrieve xml results from the server and parse those results using SAX. In order to use SAX to parse xml data, you need to implement a ContentHandler. Here is some sample code for a SurveyContentHandler used in this demo. It references a Survey object, which encapsulates the survey result xml data. I’ve included that code as well.
Survey class:
public class Survey
{
private String gender;
private Date birthDate;
private boolean isEmpty = false;
public Survey()
{
}
public String getGender()
{
return this.gender;
}
public Date getBirthDate()
{
return this.birthDate;
}
public void setGender(String s)
{
this.gender = s;
}
public void setBirthDate(Date d)
{
this.birthDate = d;
}
}
SurveyContentHandler class:
//By extending DefaultHandler we do not have to implement all the
//methods of ContentHandler interface,
//we can just implement the ones that surround our pertinent data
//(startElement, characters)
public class SurveyContentHandler extends DefaultHandler
{
//data members
private Survey survey = null;
//keep track of element while parsing
private String currentElement = null;
public SurveyContentHandler()
{
}
//Survey accessor method
public Survey getSurvey()
{
return this.survey;
}
//Called when SAX parser encounters an open element tag.
//Store the name of the element so
//when characters() is called, we know where in the XML
//we are and what data we need to store
@Override
public void startElement(String uri, String localName,
String qName, Attributes atts) throws SAXException
{
if((localName != null
&& localName.equals("IndividualSurvey"))
|| (qName != null
&& qName.equals("IndividualSurvey")))
{
this.survey = new Survey();
currentElement = "";
}
else if((localName != null
&& localName.equals("Gender"))
|| (qName != null
&& qName.equals("Gender")))
{
currentElement = "Gender";
}
else if((localName != null
&& localName.equals("BirthDate"))
|| (qName != null
&& qName.equals("BirthDate")))
{
currentElement = "BirthDate";
}
else if((localName != null
&& localName.equals("TotalPurchaseYTD"))
|| (qName != null
&& qName.equals("TotalPurchaseYTD")))
{
currentElement = "TotalPurchaseYTD";
}
else
{
currentElement = "";
}
if(!currentElement.equals(""))
System.out.println("About to process tag: " + currentElement);
}
//Called when SAX parser encounters an data between element
//tags. Store the data based on the element,
//set by startElement()
@Override
public void characters(char[] ch, int start, int length)
throws SAXException
{
String value = new String(ch, start, length);
if(currentElement != null
&& currentElement.equals("Gender"))
{
survey.setGender(value);
}
else if(currentElement != null
&& currentElement.equals("BirthDate"))
{
Date date = null;
try
{
//Use a SimpleDateFormat object to parse
//the value (formatter object
//not included in code snippets)
date = (Date)formatter.parse(
value.substring(0, length-1));
}
catch(ParseException pe)
{
System.err.println(
"Problem parsing BirthDate"
+ pe.getMessage());
}
survey.setBirthDate(date);
}
else if(currentElement != null
&& !currentElement.equals(""))
{
System.out.println("Processing " +
currentElement + ", data: " +
value);
}
}
}
Now that we have the utilities we need, here is the code sample for parsing the survey results for 50 customers in the Person.Person table.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
conn = DriverManager.getConnection(url);
//make execution faster for demo purposes
String selectQuery = "SELECT TOP(50) * FROM " +
"[Person].[Person] " +
"ORDER BY [LastName]";
//Create a statement
stmt = conn.createStatement();
//Generate a ResultSet by executing the query
rs = stmt.executeQuery(selectQuery);
//Store the survey objects returned for each customer
//in an ArrayList
ArrayList<Survey> results = new ArrayList<Survey>();
//Iterate through the rows returned in the result set
while(rs.next())
{
ResultSetMetaData rsmd = rs.getMetaData();
if(rsmd != null)
{
int numColumns = rsmd.getColumnCount();
for(int col = 1; col <= numColumns; col++)
{
String columnName =
rsmd.getColumnName(col);
String columnType =
rsmd.getColumnTypeName(col);
if(columnType.equals("xml") &&
columnName.equals(
"Demographics"))
{
//Get the xml data
//representing a survey
SQLXML custSurvey =
rs.getSQLXML(columnName);
//Create instance of
//custom content handler
SurveyContentHandler
scHandler =
new SurveyContentHandler();
//Get the SAXSource from
//SQLXML and then XMLReader
SAXSource sxSource =
custSurvey.getSource(
SAXSource.class);
XMLReader xmlReader =
sxSource.getXMLReader();
//Set the content handler
xmlReader.setContentHandler(
scHandler);
//Parse the XML – calls into
//the methods of the
//ContentHandler
xmlReader.parse(
sxSource.
getInputSource());
//Extract the survey details
//we collected while parsing
Survey survey =
scHandler.getSurvey();
//Add the customer to our list
//of customers
results.add(survey);
}
}
}
}
}
finally
{
//Clean up
if(rs!= null)
{
try { rs.close(); }
catch(SQLException se) {}
}
if(stmt != null)
{
try { stmt.close(); }
catch(SQLException se) {}
}
if(conn != null)
{
try { conn.close(); }
catch(SQLException se) {}
}
}
With an ArrayList of Survey objects, you could now process that list and generate some interesting statistics (left as an exercise for the reader).
--Erin Hardiman [SQL Server]
This post is provided “AS IS” and confers no express or implied warranties or rights.