In this section, we will describe how to connect to the XML repository.
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).
// 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.
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).
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.
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.
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.
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).
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.
public void insertValue(boolean leavesAllowed, String beforeCountSQL, String longString, int 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.
// * 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.
// -- 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.
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 |