Partager via


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.