8.5 Connecting to the Repository
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
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.
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 |
