Up: sqlprocessor Previous: The same functionality using Next: Summary/Conclusion Contents

A Second SQLProcessor Example

We'll look at the power of the SQLProcessor by first looking at the doInsertTest method, which is shown here:

/**
 * This is our first use of the SQLProcessor "set" methods.
 * This is much better than using JDBC statements, as the underlying 
 * PreparedStatements keep you from having to worry about SQL Injection hacks.
 * And the syntax is much more obvious, and less prone to errors, than
 * the JDBC PreparedStatement syntax.
 */
private void doInsertTest()
{
  System.out.print("\n[Performing INSERT] ... ");
  SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break INSERT Test",
              "INSERT INTO COFFEES VALUES (|coffeeName|,|supplierID|,|price|,|sales|,|total|)");
  sqlProcessor.set("coffeeName","BREAKFAST BLEND");
  sqlProcessor.set("supplierID",200);
  sqlProcessor.set("price",7.99);
  sqlProcessor.set("sales",0);
  sqlProcessor.set("total",0);
  sqlProcessor.execute(conn);
}

I've changed the doInsertTest method to take advantage of the SQLProcessor's named parameters. Looking at this code you can now see the advantage of the SQLProcessor approach in a "real world" example. Here I construct my SQL statement in a String, but instead of the craziness of Statements, or the "?" fields of PreparedStatements, the SQLProcessor uses named parameters.

As you can see with this approach, adding variables to your SQL commands is a simple two-step process. First, create the named parameters in your INSERT statement, as shown. Then, set the value of each named parameter in a SQLProcessor set method, such as:

sqlProcessor.set("coffeeName","BREAKFAST BLEND");

This approach is simple, readable (hence maintainable), and powerful, putting the field name right next to the desired value. And a great thing about this approach is that the SQLProcessor can help you debug problems much more powerfully than PreparedStatements.

How the SQLProcessor Helps You Debug Problems

To show how the SQLProcessor helps you debug problems, assume that you made a mistake and "accidentally" commented-out one of your set calls, like this:

//sqlProcessor.set("coffeeName","BREAKFAST BLEND"); 
sqlProcessor.set("supplierID",200); 
sqlProcessor.set("price",7.99); 
sqlProcessor.set("sales",0); 
sqlProcessor.set("total",0);

If you try to run the program now you'll obviously get an error, but let's look at the error you get from the SQLProcessor:

java.lang.IllegalArgumentException: The parameter |coffeeName| was not set
SQL Description: The Coffee Break INSERT Test
SQL: INSERT INTO COFFEES VALUES (|coffeeName|,|supplierID|,|price|,|sales|,|total|)
at com.missiondata.oss.sqlprocessor.AbstractSQLProcessorBase.prepareStatement(Unknown Source)
at com.missiondata.oss.sqlprocessor.AbstractSQLProcessorBase.execute(Unknown Source)

Now that's what I call an error message. It's hard to be more descriptive than "The parameter |coffeeName| was not set" in the SQL code you created with the description "The Coffee Break INSERT Test". That's an error message that will help you solve a problem, and as you can see from the code, it's very easy to set up and use.

The doUpdateTest and doDeleteTest methods

Once you understand the doInsertTest method, the other methods in the new class are easy to understand. The next code example shows the doUpdateTest method is modified to use named parameters:

private void doUpdateTest()
{
  System.out.print("\n[Performing UPDATE] ... ");
  SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break UPDATE Test",
              "UPDATE COFFEES SET PRICE=|price| WHERE COF_NAME=|coffeeName|");
  sqlProcessor.set("price", 4.99);
  sqlProcessor.set("coffeeName", "BREAKFAST BLEND");
  sqlProcessor.execute(conn);
}

and the following code shows how the doDeleteTest method is modified:

private void doDeleteTest()
{
  System.out.print("\n[Performing DELETE] ... ");
  SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break DELETE Test",
              "DELETE FROM COFFEES WHERE COF_NAME=|coffeeName|");
  sqlProcessor.set("coffeeName", "BREAKFAST BLEND");
  sqlProcessor.execute(conn);
}

The doDeleteTest method is also modified to use the SQLProcessor's named parameters. As you can see from these code samples, the named parameter approach works just as well for UPDATE and DELETE statements as it did for the INSERT statement. All of the same benefits apply, including readability, maintainability, and debug support.

The doSelectTest method

You may have noticed that I skipped the doSelectTest method. That was because nothing changed from our previous version of the code. That's primarily due to the fact that this code is too simple. Had it been a much more difficult test, with a significant query including WHERE clauses and other powerful constructs, the SQLProcessor named parameters would again prove to be an excellent feature.

Up: sqlprocessor Previous: The same functionality using Next: Summary/Conclusion Contents