A Simple JDBC Example

I'll begin by creating a Java class using plain old JDBC to interact with a database. After this I'll demonstrate incremental improvements to this approach by adding SQLProcessor features to the code. I think you'll see that the SQLProcessor is a significant improvement over JDBC.

The database I'll be using for this demonstration is the "Coffee Break" database defined in Sun's JDBC Tutorial. For my purposes I've implemented this in a MySQL database.

The JDBC Program

To get started I've created a simple BasicJDBCDemo Java class that shows how JDBC code is typically written. This class is shown here:

package com.devdaily.sqlprocessortests;

import java.sql.*;

public class BasicJDBCDemo
{
  Connection conn;

  public static void main(String[] args)
  {
    new BasicJDBCDemo();
  }
 
  public BasicJDBCDemo()
  {
    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();
  }

  private void doSelectTest()
  {
    System.out.println("[OUTPUT FROM SELECT]");
    String query = "SELECT COF_NAME, PRICE FROM COFFEES";
    try
    {
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery(query);
      while (rs.next())
      {
        String s = rs.getString("COF_NAME");
        float n = rs.getFloat("PRICE");
        System.out.println(s + "   " + n);
      }
    }
    catch (SQLException ex)
    {
      System.err.println(ex.getMessage());
    }
  }

  private void doInsertTest()
  {
    System.out.print("\n[Performing INSERT] ... ");
    try
    {
      Statement st = conn.createStatement();
      st.executeUpdate("INSERT INTO COFFEES " +
                       "VALUES ('BREAKFAST BLEND', 200, 7.99, 0, 0)");
    }
    catch (SQLException ex)
    {
      System.err.println(ex.getMessage());
    }
  }

  private void doUpdateTest()
  {
    System.out.print("\n[Performing UPDATE] ... ");
    try
    {
      Statement st = conn.createStatement();
      st.executeUpdate("UPDATE COFFEES SET PRICE=4.99 WHERE COF_NAME='BREAKFAST BLEND'");
    }
    catch (SQLException ex)
    {
      System.err.println(ex.getMessage());
    }
  }

  private void doDeleteTest()
  {
    System.out.print("\n[Performing DELETE] ... ");
    try
    {
      Statement st = conn.createStatement();
      st.executeUpdate("DELETE FROM COFFEES WHERE COF_NAME='BREAKFAST BLEND'");
    }
    catch (SQLException ex)
    {
      System.err.println(ex.getMessage());
    }
  }
}

Discussion of the BasicJDBCDemo class

For my SQLProcessor examples, this is the "reference implementation" of my class, a basic database-interaction class written using standard JDBC programming techniques.

The program begins in the main method, which creates a new instance of the class by invoking its constructor. The constructor establishes the database connection, calls a method named doTests(), and then closes the connection, at which point program execution stops.

The doTests method

The doTests method first runs the doSelectTest method. This runs a SQL SELECT query, selecting the COF_NAME and PRICE fields from the COFFEES table. It loops through the JDBC ResultSet, printing the output from each record as it goes along. The output from the doSelectTest method looks like this:

[OUTPUT FROM SELECT]
Colombian   7.99
French_Roast   8.99
Espresso   9.99
Colombian_Decaf   8.99
French_Roast_Decaf   9.99

The doTests method next calls a method named doInsertTest. This method invokes a SQL INSERT command using a JDBC Statement. The doTests method calls the doSelectTest again so you can see the output after the INSERT has been run. That's all the tests really do - they perform an action, and then run the SELECT statement so you can see the changes to the database.

In the same manner the doInsertTest is followed by the doUpdateTest and doDeleteTest method calls, with a doSelectTest call in between each to show the output from the SELECT statement.

This application shows straightforward JDBC program, written in the cleanest manner possible for this demonstration. How can it be made better? Let's start to look at the SQLProcessor.

Up: sqlprocessor Previous: Motivation Next: The same functionality using   Contents