Skip to Content Sun Java Solaris Communities My SDN Account Join SDN

SDN Home > Products & Technologies > Java Technology > J2EE > JDBC > Learning > Tutorial and Code Camps > jGuru: JDBC 2.0 Fundamentals > Exercise Outline > Creating and Populating a Table >

Tutorials & Code Camps

jGuru: Help: Creating and Populating a Table

Print-friendly Version

[Exercise | API Docs | Short Course| Exercises]

Help is available for each task.

Task 1

Import the java.sql package, which is required for the JDBC API.

import java.sql.*;

Task 2

Load the JDBC driver class.

    String sDriver = 
      "COM.cloudscape.core.RmiJdbcDriver";
    ...

    try   // Attempt to load the JDBC driver 
    {     // with newInstance
        Class.forName( sDriver ).newInstance();
    }
    catch( Exception e )  // error
    {
      System.err.println(
        "Failed to load current driver.");
      return;
    } // end catch

As mentioned in the text, the form of the code used above has the best chance of working on all platforms. If you aren't concerned with portability and plan to always use the same DBMS ( these are famous last words, ) and know that Class.forName() alone will work, feel free to use that form.

Task 3

Get a Connection and create a Statement, using jdbc:cloudscape:rmi:jGuru;create=true as the DatabaseURL for Cloudscape.

    try
    {

      con = DriverManager.getConnection ( sURL,
                                          sUsername,
                                          sPassword);
      stmt = con.createStatement();
    }
    catch ( Exception e)
    {
      System.err.println( "problems connecting to " +
                           sURL + ":" );
      System.err.println( e.getMessage() );

      if( con != null)
      {
        try { con.close(); }
        catch( Exception e2 ) {}
      }

      return;
    } // end catch

Notice that programs should always close Connections and Statements when their work has been done and certainly before exiting the program.

Task 4

If a previous copy of JJJJData exists, remove it from the database.

    // to allow the program to be run more than once,
    // attempt to remove the table from the database
    try
    {
      stmt.executeUpdate( "DROP TABLE JJJJData" );
      System.out.println(
        "Table JJJJData was removed.");
    }
    catch ( Exception e ) { /* don't care */ }

This aspect was not mentioned in the text, but if the program was rerun without removing the existing table, severe problems would be encountered when executing the CREATE TABLE statement. Be sure you understand that DROP TABLE completely removes the table from the database with no questions asked. The assumption here is that any exception encountered arises because the table does not exist. This is not a good assumption for a production quality program.

Task 5

Create the JJJJData table and insert the data.

    // execute SQL commands 
    // to create table and insert data
    try
    {
      stmt.executeUpdate( "CREATE TABLE JJJJData (" +
             "Entry      INTEGER      NOT NULL, "   +
             "Customer   VARCHAR (20) NOT NULL, "   +
             "DOW        VARCHAR (3)  NOT NULL, "   +
             "Cups       INTEGER      NOT NULL, "   +
             "Type       VARCHAR (10) NOT NULL,"    +
             "PRIMARY KEY( Entry )"                 +
                                          ")" );

The CREATE TABLE SQL statement is used to define the column names and types, to define a primary key, and to create the JJJJData table.

      for (int i = 0; i < SQLData.length; i++)
      {
        iRowCount += 
        stmt.executeUpdate( 
          "INSERT INTO JJJJData VALUES " + 
           SQLData[i] );
      }

      System.out.println( iRowCount + 
         " Rows inserted into JJJJData.");

    }

The data, complete with enclosing parentheses, is defined in an array named SQLData. At this point, the array is walked and the columns are inserted for each row using executeUpdate(). Note that the Statement object can be reused for multiple SQL statements and requests, and that it, in this form, is perfectly dynamic. executeUpdate() returns a count for rows affected, and we use this to accumulate and print the total number of rows inserted.

    catch ( Exception e )
    {
      System.err.println(
        "problem with SQL sent to " + sURL + ":" );
      System.err.println( e.getMessage() );
    }
    finally
    {
      try { stmt.close(); }
      catch( Exception e ) {}

      try { con.close(); }
      catch( Exception e ) {}
    } // end finally clause

A typical try, catch, finally block is used to ensure that the Statement and the Connection get closed. This ensures that database resources aren't locked up. Be sure to understand the order, it's like a sandwich: get Connection, create Statement, ... close Statement, close Connection.


	About Sun \| About This Site \| Newsletters \| Contact Us \| Employment How to Buy \| Licensing \| Terms of Use \| Privacy \| Trademarks Copyright Sun Microsystems, Inc.		A Sun Developer Network Site Unless otherwise licensed, code in all technical manuals herein (including articles, FAQs, samples) is provided under this License. Sun Developer RSS Feeds