Up: sqlprocessor Previous: A Simple JDBC Example Next: A Second SQLProcessor Example Contents

The same functionality using the SQLProcessor

I'll demonstrate the SQLProcessor by making incremental changes to the Java code so you can see the benefits of each step.

package com.devdaily.sqlprocessortests;

import java.sql.*;
import com.missiondata.oss.sqlprocessor.SQLProcessor;

/**
 * This class shows a simple (but not good) way of using the SQLProcessor.
 * This class just helps you get warmed up with the syntax.
 */
public class SQLProcessorDemo1
{
  Connection conn;

  public static void main(String[] args)
  {
    new SQLProcessorDemo1();
  }

  public SQLProcessorDemo1()
  {
    try
    {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      String url = "jdbc:mysql://localhost/coffeebreak";
      conn = DriverManager.getConnection(url, "username", "password");
      doTests();
      conn.close();
    }
    catch (ClassNotFoundException ex) {System.err.println(ex.getMessage());}
    catch (IllegalAccessException ex) {System.err.println(ex.getMessage());}
    catch (InstantiationException ex) {System.err.println(ex.getMessage());}
    catch (SQLException ex)           {System.err.println(ex.getMessage());}
  }

  private void doTests()
  {
    doSelectTest();

    doInsertTest();  doSelectTest();
    doUpdateTest();  doSelectTest();
    doDeleteTest();  doSelectTest();
  }

  /**
   * note the automatic looping through the ResultSet;
   * no "while" loop is necessary, it is implied.
   */
  private void doSelectTest()
  {
    System.out.println("[OUTPUT FROM SELECT]");
    String query = "SELECT COF_NAME, PRICE FROM COFFEES";
    SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break SELECT Test", query)
    {
      protected void process(ResultSet rs) throws SQLException
      {
        String s = rs.getString("COF_NAME");
        float n = rs.getFloat("PRICE");
        System.out.println(s + "   " + n);
      }
    };
    sqlProcessor.execute(conn);
  }

  private void doInsertTest()
  {
    System.out.print("\n[Performing INSERT] ... ");
    SQLProcessor sqlProcessor = new SQLProcessor("The Coffee Break INSERT Test",
                "INSERT INTO COFFEES VALUES ('BREAKFAST BLEND', 200, 7.99, 0, 0)");
    sqlProcessor.execute(conn);
  }

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

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

Discussion

The SQLProcessorDemo1 class represents the first step in the conversion to using the SQLProcessor. In this code I've just copied and pasted the SQL strings from our base JDBC example into the SQLProcessor constructor. This isn't very powerful, but it's a simple first step.

As you can see I've kept the methods in the class the same as the JDBC example, but I've changed the contents of each method. Let's dig right in and start looking at each method.

The doSelectTest method

The doSelectTest method has been changed considerably. As you can see from the code the following changes have been made:

The try/catch code is gone.
The Statement and ResultSet code is gone.
The while loop that was used to iterate through the ResultSet is gone.
All of these items have been replaced by a call to the SQLProcessor constructor, and a process method that is defined as an anonymous class.
The SQLProcessor lets you add a descriptive phrase ("Coffee Break SELECT Test") to your SQL command.
The SQLProcessor adds an execute method to invoke the SQL command.

The doInsertTest , doUpdateTest, and doDeleteTest methods

The changes to the doInsertTest, doUpdateTest, and doDeleteTest methods are a little less dramatic than the doSelectTest method changes, so I've combined the discussion for all of these into this section. The changes are less dramatic, but they are still significant:

The try/catch code is gone.
The Statement code is gone.
The SQLProcessor constructor is called, and passed a descriptive phrase ("Coffee Break SELECT Test") as well as the SQL command.
The SQLProcessor adds an execute method to actually invoke the SQL command.

With these basic changes in place, let's kick it up a little by taking advantage of what the SQLProcessor can really do for you.

Up: sqlprocessor Previous: A Simple JDBC Example Next: A Second SQLProcessor Example Contents