Eliminate tedious programming: Recover data with XML and Reflection

Automate ResultSet parsing using XML and Reflection

Enterprise data consists of various types of functional information, such as product specifications, vendor details, invoices, and sales orders. Whether this data is critical or not, its persistence should not be compromised in any enterprise application. Because of their robustness and proven history tackling persistence, relational databases often persist enterprise data. Thus, data retrieval from a relational database is an integral task for any middleware application. Java's Enterprise JavaBeans architecture is fast becoming the most obvious choice for developing robust middleware applications. The JDBC API facilitates an application layer that performs the data retrieval. This data-access layer translates, or maps, the data in the database entities -- rows, columns, and tables -- into instances of Java classes. In this article, I will demonstrate how to establish the mapping between the database entities and Java classes through XML. I will also use XML to show you how you can rid yourself of certain mundane steps involved in data retrieval.

Typical data retrieval

Simple value-holder classes -- popularly called value classes -- encapsulate functional data in Java. Typically, such classes are made up of one or more private fields and their get() and set() accessor methods. Each field maps to one of the data entities in the database. A simple data retrieval will involve:

  1. Writing the appropriate fetch statement (in SQL)
  2. Executing the fetch statement on an open database connection
  3. Parsing the returned ResultSet
  4. Creating value objects for each row retrieved and adding to a collection that is returned

Although the JDBC API provides powerful support for each of the above steps, it cannot completely eliminate them. Hence, developers end up writing repetitious and functionally similar pieces of code; writing the SQL is the only imaginative part of data retrieval. By purging this task's mundane code, object-relational mapping tools can keep programming somewhat interesting. But, for simple data retrieval, the same benefits can be realized using XML and Reflection.

Sample implementation

Let's assume this standard example: Our employee database features columns such as EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME, EMPLOYEE_NUMBER, and so on. An EmployeeInfo class with the corresponding fields below encapsulates the employee information:

      public class EmployeeInfo
      {
         private String employeeNumber;
         private String employeeFirstName;
         private String employeeLastName;
         private int    employeeExperience;
         private Timestamp employeeDOB;
         private String employeeEmail;
         private Timestamp employeeDOJ;
         public String getEmployeeNumber()
         {
            return this.employeeNumber;
         }
         .
         .
         public void setEmployeeNumber(String employeeNumber)
         {
            this.employeeNumber=employeeNumber;
         }
         .
         .
         .
         .
      }

Now, let's assume that some business functionality requires employee objects to be fetched from the relational database. Typical code that creates the EmployeeInfo objects from the data in the database would look like this:

     public Vector getActiveEmployees() throws SQLException
     {
        String sQuery = "select employee_first_name, "+
                        " employee_last_name, "+
                        " employee_dob,"+
                        " employee_experience,"+
                        " employee_DOJ, "+
                        " employee_email "+
                        "from employee";
        Statement querySmt = null;
        ResultSet rs = null;
        Vector vecEmployees = null;
        try
        {
           querySmt = databaseConnect.createStatement();
           rs = querySmt.executeQuery(sQuery);
           //LOOP
           while(rs.next())
           {
              EmployeeInfo empInfo = new EmployeInfo();
              empInfo.setEmployeeFirstName(rs.getString("EMPLOYEE_FIRST_NAME"));
              empInfo.setEmployeeLastName(rs.getString("EMPLOYEE_LAST_NAME"));
              empInfo.setEmployeeNumber(rs.getString("EMPLOYEE_NUMBER"));
              empInfo.setEmployeeDOB(rs.getTimestamp("EMPLOYEE_DOB"));
              empInfo.setEmployeeDOJ(rs.getTimestamp("EMPLOYEE_DOJ"));
              empInfo.setEmployeeEmail(rs.getString("EMPLOYEE_EMAIL"));
              empInfo.setEmployeeExperience(rs.getInt("EMPLOYEE_EXPERIENCE"));
              vecEmployees.addElement(empInfo);
           }//END LOOP
        }
        finally
        {
           try
           {
              if(smt != null) smt.close();
              if(rs != null) rs.close();
              }
           catch(SQLException sqlE)
           {
           }
        }
     }
        

The code regions between LOOP and END LOOP denote the most unimaginative and repetitious portion of this assignment. A developer would have to complete similar processing in other database fetches. Most programmers, whether lazy or not, would fail to enthusiastically embrace this particular challenge. The combination of XML and Reflection provides an approach that helps developers circumvent this type of tedious processing.

Let's add the class ResultSetParser with the getCollection() method, which returns the value objects' collection and takes the ResultSet as one of the arguments. The ResultSetParser class diminishes the above code to:

     public Vector getActiveEmployees() throws SQLException
     {
        String sQuery = "select employee_first_name, "+
                        " employee_last_name, "+
                        " employee_dob,"+
                        " employee_experience,"+
                        " employee_DOJ, "+
                        " employee_email "+
                        "from employee";
        Statement querySmt = null;
        ResultSet rs = null;
        Vector vecEmployees = null;
        try
        {
           querySmt = databaseConnect.createStatement();
           rs = querySmt.executeQuery(sQuery);
           ResultSetParser rsp = new ResultSetParser(new File(Constants.LOAD_XML_PATH+
                                                     "LOAD_EMPLOYEE.XML"));
           vecEmployees = rsp.getCollection(rs);
        }
        finally
        {
           try
           {
              if(querySmt != null) querySmt.close();
              if(rs != null) rs.close();
           }
           catch(SQLException sqlE)
           {
           }
        }
        return vecEmployees;
     }

The code reduction in the above example may not seem very significant, but using ResultSetParser does make a difference for value classes with numerous state variables. The other advantage to this approach is that any ResultSet can be parsed.

ResultSetParser's generic method uses XML and Reflection to parse the ResultSet and return the collection. The following code is the XML used for the data access above:

     < VALUES CLASS="myproj.employee.model.EmployeeInfo" >
        < COLUMNS NAME="EMPLOYEE_FIRST_NAME"    METHOD="setEmployeeFirstName"
/>
        < COLUMNS NAME="EMPLOYEE_LAST_NAME"     METHOD="setEmployeeLastName"
/>
        < COLUMNS NAME="EMPLOYEE_EXPERIENCE"   
METHOD="setEmployeeExperience" />
        < COLUMNS NAME="EMPLOYEE_DOB"           METHOD="setEmployeeDOB" />
        < COLUMNS NAME="EMPLOYEE_DOJ"           METHOD="setEmployeeDOJ" />
        < COLUMNS NAME="EMPLOYEE_EMAIL"         METHOD="setEmployeeEmail"
/>
     < /VALUES>

The VALUES element holds the class name as an attribute. The child elements map each database column to the corresponding set() methods in the VALUES CLASS, which are used to set a state variable in the valueClass.

A simple SAX parser can now parse this XML and store the mapping in a hashtable. The following code excerpt completes this task:

      .
      .
      .
      private class PropertyParser extends HandlerBase
      {
         //mappingTable is a hashtable in the Outer Class
         public void startElement(String strElement,
                                  AttributeList attrList)
         {
            if(strElement.equals("VALUES"))
            {
               valueClassName = attrList.getValue("CLASS");
            }
            else if(strElement.equals("COLUMNS"))
            {
               String columnName = attrList.getValue("NAME");
               String methodName = attrList.getValue("METHOD");
               mappingTable.put(columnName,methodName);
            }
         }
         public void endElement(String strElement)
         {
         }
         public void parsePropertyFile(File xmlFile)
         {
            try
            {
                SAXParser saxParser = SAXParserFactory
                                      .newInstance()
                                      .newSAXParser();
                saxParser.parse(xmlFile,this);
            }
            catch(Exception e)
            {
               System.out.println(""+e);
            }
         }
      }

An inner class called PropertyParser is used for parsing the XML document. An instance of SAXParser completes the actual parsing of the XML document and fires different parsing events, which are handled by the PropertyParser. We implement only one of the events via startElement() to obtain the mapping between the method name and the database column name.

Retrieving data from the ResultSet will require a call to its appropriate get() method. ResultSet implementations support a wide variety of ways to obtain data; the method used depends on the database's data type. For example, ResultSet's getString() method can retrieve numeric data type from the database as a String. There are two ways to ensure that the most appropriate get() method is called on the ResultSet:

  1. Obtain the data type of the column whose data is being retrieved. Use the get() method that returns the equivalent data type in Java. For example, to fetch data that stores as VARCHAR(2) in Oracle, you would call getString() on the ResultSet.
  2. Obtain the argument type of the valueClass's corresponding set() method, as defined by the XML mapping. Call the get() method on the ResultSet that returns the compatible data type. For example, if the retrieved data originates from a column called EMPLOYEE_DOB, then getTimestamp() is used on the ResultSet, since the corresponding set() method -- setEmployeeDOB() -- takes java.sql.Timestamp as an argument.

Option 1 may not be broad enough to support different data types across different database types. Option 2 is simpler to realize using Reflection. Since Reflection is necessary for instantiating the value classes (known only at runtime), option 2 would be the most obvious choice.

Next, you must obtain the arguments for each of the set() methods indicated in the XML. The code excerpt below attempts to accomplish this:

      private void getMethodArguments() throws ClassNotFoundException
      {
         Class c = Class.forName(valueClassName);
         Method[] valueClassMethods = c.getMethods();
         for(int i=0;i<valueClassMethods.length;i++)
         {
            String methodName = valueClassMethods[i].getName();
            Class[] parameter = valueClassMethods[i]
                                .getParameterTypes();
            if(parameter.length > 0)
               methodArguments.put(methodName,parameter[0].getName());
         }
      }

The methodArguments is a hashtable in the outer class. The code above uses Reflection to obtain the argument type for each method in the ValueClass. For each class, the Java Runtime Environment maintains an immutable Class object that contains the information about the class. The call to the static method forName() obtains the Class object corresponding to the valueClass, whose name is known only at runtime. getMethods() is an instance method of Class that returns an array of Method objects for the class. Method objects encapsulate the method information -- such as the method's name, return type, parameter types, set of modifiers, and set of throwable exceptions -- in a class. In the code above, we obtain the method name and its parameter type for each method in the valueClass and store them in a Hashtable instance called methodArguments. Since the valueClass has a set() method for every field, we assume that it takes only one argument.

ResultSetMetaData encapsulates the column's information -- such as the number of columns, their names, and the column data type and its precision -- contained in a ResultSet. The getColumnName() of the ResultSetMetaData object returns the column's name in the ResultSet:

             .
             .
             .
             ResultSetMetaData rsmd = rs.getMetaData();
             int columnCount = rsmd.getColumnCount();
             columnNames = new String[columnCount+1];
             for(int i=1;i<=columnCount;i++)
                     columnNames[i]=rsmd.getColumnName(i);
                     
             .
             .

The code above obtains all the information needed about the ResultSet. The next step is to retrieve data from the ResultSet and create a collection of value classes.

Obtaining the collection of the value class instances requires:

  1. Instantiating the class
  2. Setting the state variables to the data retrieved in the ResultSet
1 2 Page
Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more