8.5 Connecting to the Repository

  Previous section   Next section

In this section, we will describe how to connect to the XML repository.

8.5.1 The xmlrepDB Class

First we will create a class (xmlrepDB.java) that will handle the connection to the database and provide methods to execute SQL commands, handle long strings that need splitting into leaves, and handle any exceptions that may arise (see Listing 8.19).

Listing 8.19 xmlrepDB Class
// Import core Java classes
import java.lang.*;
import java.sql.*;
// The xmlrepDB class
public class xmlrepDB {
   // Login ID and password
   private String username = "xmlrep_user";
   private String password = "fishcakes";
   // Other variables
   private Connection con;
   public Statement stmt;
   public int leafSize;
   public int elementNameLength;
   public int nsPrefixLength;
   public int entityRefLength;

In Listing 8.20, we include a main() method for command-line testing.

Listing 8.20 Main Method for Command-Line Testing
public static void main(String args[]) {
  // Instantiate the class
  xmlrepDB dbTest = new xmlrepDB();
  // Connect to the repository and attempt a query
  System.out.println("Connecting to the repository...");
  dbTest.connect();
  System.out.println("Attempting some queries...");
  System.out.println("Leaf size = " + dbTest.leafSize);
  System.out.println("Element name length = " +
     dbTest.elementNameLength);
  System.out.println("Namespace prefix length = " +
     dbTest.nsPrefixLength);
  System.out.println("Entity reference length = " +
     dbTest.entityRefLength);
  System.out.println("Disconnecting from the repository...");
  dbTest.disconnect();
  System.out.println("Done");
}

The connect() method (see Listing 8.21) initializes the connection to the repository using an ODBC data source called XMLREP, creates a SQL statement object, and retrieves the leaf size and the maximum element name, namespace prefix, and entity name lengths (which we will use for error trapping later).

Listing 8.21 Connect Method
public void connect() {
  try {
     // Load the jdbc-odbc bridge
     Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
     // Connect to the database (and disable AutoCommit mode)
     String url = "jdbc:odbc:XMLREP";
     con = DriverManager.getConnection(url, username, password);
     con.setAutoCommit(false);
     // Create a SQL query object
     stmt = con.createStatement();
     // Determine the maximum text leaf size etc.
     leafSize = intExecSQL("rep_leafSize;");
     elementNameLength = intExecSQL("rep_elementNameLength;");
     nsPrefixLength = intExecSQL("rep_nsPrefixLength;");
     entityRefLength = intExecSQL("rep_entityRefLength;");
   } catch (java.sql.SQLException ex)
     {sqlEx (ex, "[End of SQLException]");
   } catch (java.lang.Exception ex) {javaEx(ex);}
}

The disconnect() method closes the statement, commits the current transaction, and closes the connection to the repository. This is shown in Listing 8.22.

Listing 8.22 Disconnect Method
public void disconnect() {
  try {
     // Close the statement
     stmt.close();
     // Commit the transaction
     commitTran();
     // Close the connection
     con.close();
   } catch (java.sql.SQLException ex)
     {sqlEx (ex, "[End of SQLException]");
   } catch (java.lang.Exception ex) {javaEx(ex);}
}

The voidExecSQL() method will execute any SQL statement that does not return a result set (such as an insert, an update, or a delete statement), as shown in Listing 8.23.

Listing 8.23 voidExecSQL Method
public int voidExecSQL(String sqlCmd) {
   try {stmt.executeUpdate(sqlCmd);
   } catch (java.sql.SQLException ex) {sqlEx (ex, sqlCmd);
   } catch (java.lang.Exception   ex) {javaEx(ex);}
}

The intExecSQL() method will execute any SQL statement that returns a result set with an integer value column, as shown in Listing 8.24.

Listing 8.24 intExecSQL Method
public int intExecSQL(String sqlCmd) {
   // Return variable
   int resInt = -1;
   try {
      // Execute the query
      ResultSet rs = stmt.executeQuery(sqlCmd);
      // Loop through the records
      while (rs.next()) {
         // Read the value field
         resInt = rs.getInt("value");
      }
      // Close the result set
      rs.close();
   } catch (java.sql.SQLException ex) {sqlEx (ex, sqlCmd);
   } catch (java.lang.Exception   ex) {javaEx(ex);}
   // Return the result
   return resInt;
}

The replace() method (see Listing 8.25) comes in handy for replacing occurrences of a given string within another longer string; for example, we need to watch out for characters in text content that will confuse the SQL expressions we will be building (specifically, we will need to escape quotes, as quotes are delimiters for string content in SQL statements).

Listing 8.25 replace Method
public String replace(String theStr, String findStr, String replaceStr) {
   // Variables
   boolean anotherHit;
   int lastIndex = 0;
   int thisIndex;
   // Check there is a string to search for
   if (findStr == "") {return theStr;}
   // First occurrence
   thisIndex = theStr.indexOf(findStr, lastIndex);
   anotherHit = (thisIndex > 0);
   // Loop over occurrences
   while (anotherHit == true) {
      // Replace this occurrence
      theStr = theStr.substring(0, thisIndex) + replaceStr +
               theStr.substring(thisIndex + 1, theStr.length());
      lastIndex = thisIndex + replaceStr.length();
      // Next occurrence
      thisIndex = theStr.indexOf(findStr, lastIndex);
      anotherHit = (thisIndex > 0);
   }
   // Done
   return theStr;
}

The insertValue() method (see Listing 8.26) will cut a string into leaves, if it exceeds the maximum length parameter.

Listing 8.26 insertValue Method
[View full width]
public void insertValue(boolean leavesAllowed, String beforeCountSQL, String longString, 
graphics/ccc.gifint offset) {
   // Variables
   int startPage;
   int endPage;
   int offsetI;
   // Replace each single quote with 2 * single quotes
   // N.B. check requirements for other RDBMS
   longString = replace(longString, "'", "''");
   // How many leaves?
   int numPages = (longString.length() / leafSize) + 1;
   // Are leaves allowed?
   if (leavesAllowed == false && numPages > 1) {
       trapError("[Error] : '" + longString
                 + "' exceeds the maximum length.");
   }
   // Loop over pages
   for (int i = 1; i <= numPages; i++) {
      startPage = (i - 1) * leafSize;
      endPage = (i * leafSize) - 1;
      if (endPage > longString.length()) {endPage = longString.length();}
      offsetI = i + offset;
      if (leavesAllowed) {
         voidExecSQL(beforeCountSQL + ", " + offsetI + ", '"
             + longString.substring(startPage, endPage) + "'");
      } else {
         voidExecSQL(beforeCountSQL + ", '"
             + longString.substring(startPage, endPage) + "'");
      }
   }
}
// * Handle long strings (start with leaf_id = 1)
public void insertValue(boolean leavesAllowed, String beforeCountSQL,
                        String longString) {
    insertValue(leavesAllowed, beforeCountSQL, longString, 0);
}

Methods for handling exceptions are shown in Listing 8.27.

Listing 8.27 Exception-Handling Methods
// * SQL exception handler
public void sqlEx(java.sql.SQLException ex, String sqlString) {
   // Print SQLException details
   System.out.println("SQL exception(s)");
   while (ex != null) {
      System.out.println("SQLCommand: " + sqlString);
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("Message: " + ex.getMessage());
      System.out.println("Vendor: " + ex.getErrorCode());
      ex = ex.getNextException ();
   }
   gracefulExit();
}
// * Java exception handler
public void javaEx(java.lang.Exception ex) {
   // Print the exception
   System.out.println("[Java error]");
   ex.printStackTrace();
   gracefulExit();
}
// * Errors we detect in the code
public void trapError(String msg) {
  System.out.println("[Error] " + msg);
  gracefulExit();
}
// * Graceful exit
public void gracefulExit() {
   System.out.println("[Attempting a graceful exit...]");
   // Rollback the transaction
   rollbackTran();
   // Close the connection
   try {
      con.close();
   } catch (java.sql.SQLException ex) {
      System.out.println("Error occurred whilst terminating connection.");}
   // Stop further processing
   System.exit(1);
}

Finally, methods that will roll back or commit the current transaction are shown in Listing 8.28.

Listing 8.28 Transaction-Handling Methods
   // -- SQL transaction methods
   // * Rollback transaction
   private void rollbackTran() {
      try {con.rollback();}
      catch (java.sql.SQLException ex)
        {sqlEx (ex, "ROLLBACK TRANSACTION");}
   }
   // * Commit transaction
   private void commitTran() {
      try {con.commit();}
      catch (java.sql.SQLException ex)
        {sqlEx (ex, "COMMIT TRANSACTION");}
   }
}

The class connects to the database through the JDBC-ODBC Bridge, so we need to create an ODBC data source called XMLREP. With this in place, and with xmlrepDB compiled ("javac xmlrepDB.java"), we can now test the connection, as shown in Listing 8.29.

Listing 8.29 Test Database Connection
C:\xmlrep>java xmlrepDB
Connecting to the repository...
Attempting a query...
Leaf size               = 255
Element name length     = 50
Namespace prefix length = 25
Entity reference length = 50
Disconnecting from the repository...
Done

Top

Part IV: Applications of XML