Basic JDBC Application Steps

There are several steps you need to take in order to create a Java program using the JDBC API. JDBC is the Java extension of the SQL API and will show some similarities in program layout (Such as creating a handle, preparing and then executing each statements. Although these are referenced as objects or classes in Java). Refer to the example HelloWorldJDBC for reference. Some code snippets will be provided.

  1. Set up and initialize your application's use of the JDBC SQL API as follows.
    1. Using the DriverManager class, create your connection using the Raima connection URL and the following DriverManager method:

      Conn = DriverManager.getConnection("jdbc:raima:rdm://local");

      The string passed to the getConnection() method is a URL which designates which driver to use, how to get to the database, what database to connect to, and connection parameters. See RDMDriver.connect() for more information.

      *Note for better de-allocation practices, create a new try block after each object has been allocated. See full code for clarification.
    2. Create your Statement object by calling the following Connection method:

      Connection Conn = DriverManager.getConnection("jdbc:raima:rdm://local/<databasename>");
      Statement Stmt = Conn.createStatement();
  2. Next create a Statement, PreparedStatement, or CallableStatement. If you would like to use parametrized statements, use a PreparedStatement. A CallableStatement adds the ability to use named parameters.

    1. Create all needed PreparedStatement objects or a CallableStatement objects to prepare your application for execution by calling methods such as:

      PreparedStatement prepStmt = 
          Conn.prepareStatement("INSERT INTO hello_table (f00) VALUES (?)");

      or

      CallableStatement callStmt = 
          Conn.prepareCall("INSERT INTO hello_table (f00) VALUES ( :infoName )");
    2. Call the setter methods to bind the parameters in your prepared statements, such as:

      prepStmt.setString(1, "Hello World!");

      or

      callStmt.setString("infoName", "Hello World!");
  3. Once you have all the required parameters supplied call execute() to execute you a prepared (or callable) statement. If no parameters are needed a standard statement can be used.

    1. Call prepStmt.execute() method to execute all of your prepared statements. You can also use your Statement object to execute statements with no preparation. This was used above when you opened the database.

      ResultSet rs = Stmt.execute("SELECT * FROM hello_table");

      or

      ResultSet rs = prepStmt.execute();

      or

      ResultSet rs = callStmt.execute();
    2. Create a while loop checking for the next value in your result set to be null or your ResultSet.next() method returns false meaning no more data. This will traverse through your table after a SELECT statement has been performed and stored into a ResultSet. Inside of your loop call a getter method such as ResultSet.getString(1) to receive the data from your table in the current row.

      while (rs.next())
      {
          String name = rs.getString(1); // retrieve the first column as a string.
          ...
      }
      
  4. When your application is ready to terminate there are several steps you must take to insure nothing is left behind.
    1. For every nested try block, you must have a finally block that closes the corresponding Object by performing the Class.close() method. For example :

      ...
      } finally {
      rs.close();
      }

      See complete code below for a more complete example

    2. The last try block must include a catch block to handle the SQLExceptions that could be thrown. For example:

      ...
      } catch (SQLException exception) {
          System.err.println("SQLException : "
              + exception.toString()); // Displays error to standard out
      }
      

Hello World!

If the steps above are followed you should be able to access a simple database, store in some data and read the data back out. You can see an example of this in the common "Hello World!" example given below. The database schema for this sample is helloWorld.sdl.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * helloWorldJDBC: This example program shows the basic steps of creating
 * a JDBC application. This class performs the following.  Inserts data into 
 * the database, retrieves the data using a ResultSet object, which is then 
 * displayed out to the screen.This example uses the JNI connection URL. In order 
 * to use JDBC you must specify your connection URL as "jdbc:raima:rdm://localhost" 
 * instead of the JNI which is "jdbc:raima:rdm://local". The comments are javadoc
 * style for consistency.
 * @author Kevin Hooks
 * Date : 2-2-2012
 */
public class HelloWorld_JDBCTutorial_main {

	/**
	 * main: This method connects to the driver by using the connection URL
     * through JNI (TCP/IP). The database is then opened and cleaned up. 
     * The string "Hello World!" is then inserted into the database. Every 
     * object is initialized to null so that it can be used inside of each
     * block (try, catch, finally).
     * @param args
     * @throws SQLException 
     */
    public static void main(String[] args) throws SQLException {
        // Connection object used for connecting to driver
        Connection Conn = null; 

        // Statement object used for executing SQL Statements
        Statement Stmt = null;

        // Prepared Statement object used for preparing SQL Statements
        PreparedStatement prepStmt = null;

        // Result Set object used for storing queried values
		ResultSet rs = null;

        // The try block contains the main portion of the code. The code is contained
        // inside here so that if it fails the exception will be caught inside the
        // catch blocks.
        try
        {
            // Connects to driver using connection URL and passed into Connection
            // Object
            Conn = DriverManager.getConnection("jdbc:raima:rdm://local");

            // Creates the Statement object to be used for future SQL Statement
            // executions
            Stmt = Conn.createStatement();

            // Opens database
            Stmt.executeUpdate("USE \"hello_worldJDBC\"");

            //Used to clear table if there is previous data
            Stmt.executeUpdate("DELETE FROM hello_table");

            // Prepares the insert statement for execution
            prepStmt = Conn.prepareStatement("INSERT INTO hello_table (f00) VALUES (?)");

            // Performs a setter method for a string setting the value of the
            // parameter above
            prepStmt.setString(1, "Hello World!");

            //Executes prepared Statement
            prepStmt.execute();

            // Commits all changes
            Conn.commit();

            // Executes a select statement and stores queried results in the result
            // set
            rs = Stmt.executeQuery("SELECT * FROM hello_table");

            // Loops through the result set, checking for when the next value is
            // null. We only expect 1 iteration. The contents of helloString are
            // then received from the getter method. The results are then printed
            // to the screen
            while (rs.next() != false)
            {
                String helloString = rs.getString(1);
                System.out.println(helloString);
            }
        }

		// The catch block is used to catch an exceptions that are thrown above and 
		// display them to the screen. Currently the only type of exception we can
		// expect would be a SQLException otherwise we would have to have another
		// catch block for each possible exception 
        catch (SQLException exception)
        {
            System.err.println("An exception has occured : " + exception.toString());
            System.exit (1);
        }

        // The finally block is used to execute after the try block has been
        // performed. It is also executed if an exception is thrown, this way you
        // can clean up any open objects without missing the .close() on a failure.
        // Every object must be closed as you would a handle in ODBC. 
        finally
        {
            if (rs != null)
            {
                rs.close();
            }

            if (prepStmt != null)
            {
                prepStmt.close();
            }

            if (Stmt != null)
            {
                Stmt.close();
            }

            if (Conn != null)
            {
                Conn.close();
            }
        }
    }
}