Documentation

Setting Up Tables
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics

Setting Up Tables

This page describes all the tables used in the JDBC tutorial and how to create them:

COFFEES Table

The COFFEES table stores information about the coffees available for sale at The Coffee Break:

COF_NAME SUP_ID PRICE SALES TOTAL
Colombian 101 7.99 0 0
French_Roast 49 8.99 0 0
Espresso 150 9.99 0 0
Colombian_Decaf 101 8.99 0 0
French_Roast_Decaf 49 9.99 0 0

The following describes each of the columns in the COFFEES table:

SUPPLIERS Table

The SUPPLIERS stores information about each of the suppliers:

SUP_ID SUP_NAME STREET CITY STATE ZIP
101 Acme, Inc. 99 Market Street Groundsville CA 95199
49 Superior Coffee 1 Party Place Mendocino CA 95460
150 The High Ground 100 Coffee Lane Meadows CA 93966

The following describes each of the columns in the SUPPLIERS table:

COF_INVENTORY Table

The table COF_INVENTORY stores information about the amount of coffee stored in each warehouse:

WAREHOUSE_ID COF_NAME SUP_ID QUAN DATE_VAL
1234 House_Blend 49 0 2006_04_01
1234 House_Blend_Decaf 49 0 2006_04_01
1234 Colombian 101 0 2006_04_01
1234 French_Roast 49 0 2006_04_01
1234 Espresso 150 0 2006_04_01
1234 Colombian_Decaf 101 0 2006_04_01

The following describes each of the columns in the COF_INVENTORY table:

MERCH_INVENTORY Table

The table MERCH_INVENTORY stores information about the amount of non-coffee merchandise in stock:

ITEM_ID ITEM_NAME SUP_ID QUAN DATE
00001234 Cup_Large 00456 28 2006_04_01
00001235 Cup_Small 00456 36 2006_04_01
00001236 Saucer 00456 64 2006_04_01
00001287 Carafe 00456 12 2006_04_01
00006931 Carafe 00927 3 2006_04_01
00006935 PotHolder 00927 88 2006_04_01
00006977 Napkin 00927 108 2006_04_01
00006979 Towel 00927 24 2006_04_01
00004488 CofMaker 08732 5 2006_04_01
00004490 CofGrinder 08732 9 2006_04_01
00004495 EspMaker 08732 4 2006_04_01
00006914 Cookbook 00927 12 2006_04_01

The following describes each of the columns in the MERCH_INVENTORY table:

COFFEE_HOUSES Table

The table COFFEE_HOUSES stores locations of coffee houses:

STORE_ID CITY COFFEE MERCH TOTAL
10023 Mendocino 3450 2005 5455
33002 Seattle 4699 3109 7808
10040 SF 5386 2841 8227
32001 Portland 3147 3579 6726
10042 SF 2863 1874 4710
10024 Sacramento 1987 2341 4328
10039 Carmel 2691 1121 3812
10041 LA 1533 1007 2540
33005 Olympia 2733 1550 4283
33010 Seattle 3210 2177 5387
10035 SF 1922 1056 2978
10037 LA 2143 1876 4019
10034 San_Jose 1234 1032 2266
32004 Eugene 1356 1112 2468

The following describes each of the columns in the COFFEE_HOUSES table:

DATA_REPOSITORY Table

The table DATA_REPOSITORY stores URLs that reference documents and other data of interest to The Coffee Break. The script populate_tables.sql does not add any data to this table. The following describes each of the columns in this table:

Creating Tables

You can create tables with Apache Ant or JDBC API.

Creating Tables with Apache Ant

To create the tables used with the tutorial sample code, run the following command in the directory <JDBC tutorial directory>:

ant setup

This command runs several Ant targets, including the following, build-tables (from the build.xml file):

<target name="build-tables"
  description="Create database tables">
  <sql
    driver="${DB.DRIVER}"
    url="${DB.URL}"
    userid="${DB.USER}"
    password="${DB.PASSWORD}"
    classpathref="CLASSPATH"
    delimiter="${DB.DELIMITER}"
    autocommit="false" onerror="abort">
    <transaction src=
  "./sql/${DB.VENDOR}/create-tables.sql"/>
  </sql>
</target>

The sample specifies values for the following sql Ant task parameters:

Parameter Description
driver Fully qualified class name of your JDBC driver. This sample uses org.apache.derby.jdbc.EmbeddedDriver for Java DB and com.mysql.jdbc.Driver for MySQL Connector/J.
url Database connection URL that your DBMS JDBC driver uses to connect to a database.
userid Name of a valid user in your DBMS.
password Password of the user specified in userid
classpathref Full path name of the JAR file that contains the class specified in driver
delimiter String or character that separates SQL statements. This sample uses the semicolon (;).
autocommit Boolean value; if set to false, all SQL statements are executed as one transaction.
onerror Action to perform when a statement fails; possible values are continue, stop, and abort. The value abort specifies that if an error occurs, the transaction is aborted.

The sample stores the values of these parameters in a separate file. The build file build.xml retrieves these values with the import task:

<import file="${ANTPROPERTIES}"/>

The transaction element specifies a file that contains SQL statements to execute. The file create-tables.sql contains SQL statements that create all the tables described on this page. For example, the following excerpt from this file creates the tables SUPPLIERS and COFFEES:

create table SUPPLIERS
    (SUP_ID integer NOT NULL,
    SUP_NAME varchar(40) NOT NULL,
    STREET varchar(40) NOT NULL,
    CITY varchar(20) NOT NULL,
    STATE char(2) NOT NULL,
    ZIP char(5),
    PRIMARY KEY (SUP_ID));

create table COFFEES
    (COF_NAME varchar(32) NOT NULL,
    SUP_ID int NOT NULL,
    PRICE numeric(10,2) NOT NULL,
    SALES integer NOT NULL,
    TOTAL integer NOT NULL,
    PRIMARY KEY (COF_NAME),
    FOREIGN KEY (SUP_ID)
        REFERENCES SUPPLIERS (SUP_ID));

Note: The file build.xml contains another target named drop-tables that deletes the tables used by the tutorial. The setup target runs drop-tables before running the build-tables target.

Creating Tables with JDBC API

The following method, SuppliersTable.createTable, creates the SUPPLIERS table:

public void createTable() throws SQLException {
    String createString =
        "create table " + dbName +
        ".SUPPLIERS " +
        "(SUP_ID integer NOT NULL, " +
        "SUP_NAME varchar(40) NOT NULL, " +
        "STREET varchar(40) NOT NULL, " +
        "CITY varchar(20) NOT NULL, " +
        "STATE char(2) NOT NULL, " +
        "ZIP char(5), " +
        "PRIMARY KEY (SUP_ID))";

    Statement stmt = null;
    try {
        stmt = con.createStatement();
        stmt.executeUpdate(createString);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

The following method, CoffeesTable.createTable, creates the COFFEES table:

  public void createTable() throws SQLException {
    String createString =
        "create table " + dbName +
        ".COFFEES " +
        "(COF_NAME varchar(32) NOT NULL, " +
        "SUP_ID int NOT NULL, " +
        "PRICE float NOT NULL, " +
        "SALES integer NOT NULL, " +
        "TOTAL integer NOT NULL, " +
        "PRIMARY KEY (COF_NAME), " +
        "FOREIGN KEY (SUP_ID) REFERENCES " +
        dbName + ".SUPPLIERS (SUP_ID))";

    Statement stmt = null;
    try {
        stmt = con.createStatement();
        stmt.executeUpdate(createString);
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

In both methods, con is a Connection object and dbName is the name of the database in which you are creating the table.

To execute the SQL query, such as those specified by the String createString, use a Statement object. To create a Statement object, call the method Connection.createStatement from an existing Connection object. To execute a SQL query, call the method Statement.executeUpdate.

All Statement objects are closed when the connection that created them is closed. However, it is good coding practice to explicitly close Statement objects as soon as you are finished with them. This allows any external resources that the statement is using to be released immediately. Close a statement by calling the method Statement.close. Place this statement in a finally to ensure that it closes even if the normal program flow is interrupted because an exception (such as SQLException) is thrown.

Note: You must create the SUPPLIERS table before the COFFEES because COFFEES contains a foreign key, SUP_ID that references SUPPLIERS.

Populating Tables

Similarly, you can insert data into tables with Apache Ant or JDBC API.

Populating Tables with Apache Ant

In addition to creating the tables used by this tutorial, the command ant setup also populates these tables. This command runs the Ant target populate-tables, which runs the SQL script populate-tables.sql.

The following is an excerpt from populate-tables.sql that populates the tables SUPPLIERS and COFFEES:

insert into SUPPLIERS values(
    49, 'Superior Coffee', '1 Party Place',
    'Mendocino', 'CA', '95460');
insert into SUPPLIERS values(
    101, 'Acme, Inc.', '99 Market Street',
    'Groundsville', 'CA', '95199');
insert into SUPPLIERS values(
    150, 'The High Ground',
    '100 Coffee Lane', 'Meadows', 'CA', '93966');
insert into COFFEES values(
    'Colombian', 00101, 7.99, 0, 0);
insert into COFFEES values(
    'French_Roast', 00049, 8.99, 0, 0);
insert into COFFEES values(
    'Espresso', 00150, 9.99, 0, 0);
insert into COFFEES values(
    'Colombian_Decaf', 00101, 8.99, 0, 0);
insert into COFFEES values(
    'French_Roast_Decaf', 00049, 9.99, 0, 0);

Populating Tables with JDBC API

The following method, SuppliersTable.populateTable, inserts data into the table:

public void populateTable() throws SQLException {

    Statement stmt = null;
    try {
        stmt = con.createStatement();
        stmt.executeUpdate(
            "insert into " + dbName +
            ".SUPPLIERS " +
            "values(49, 'Superior Coffee', " +
            "'1 Party Place', " +
            "'Mendocino', 'CA', '95460')");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".SUPPLIERS " +
            "values(101, 'Acme, Inc.', " +
            "'99 Market Street', " +
            "'Groundsville', 'CA', '95199')");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".SUPPLIERS " +
            "values(150, " +
            "'The High Ground', " +
            "'100 Coffee Lane', " +
            "'Meadows', 'CA', '93966')");
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

The following method, CoffeesTable.populateTable, inserts data into the table:

public void populateTable() throws SQLException {

    Statement stmt = null;
    try {
        stmt = con.createStatement();
        stmt.executeUpdate(
            "insert into " + dbName +
            ".COFFEES " +
            "values('Colombian', 00101, " +
            "7.99, 0, 0)");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".COFFEES " +
            "values('French_Roast', " +
            "00049, 8.99, 0, 0)");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".COFFEES " +
            "values('Espresso', 00150, 9.99, 0, 0)");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".COFFEES " +
            "values('Colombian_Decaf', " +
            "00101, 8.99, 0, 0)");

        stmt.executeUpdate(
            "insert into " + dbName +
            ".COFFEES " +
            "values('French_Roast_Decaf', " +
            "00049, 9.99, 0, 0)");
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) {
          stmt.close();
        }
    }
}

Previous page: Handling SQLExceptions
Next page: Retrieving and Modifying Values from Result Sets