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_NAMESUP_IDPRICESALESTOTAL
Colombian1017.9900
French_Roast498.9900
Espresso1509.9900
Colombian_Decaf1018.9900
French_Roast_Decaf499.9900

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

  • COF_NAME : Stores the coffee name. Holds values with a SQL type of VARCHAR with a maximum length of 32 characters. Because the names are different for each type of coffee sold, the name uniquely identifies a particular coffee and serves as the primary key.

  • SUP_ID : Stores a number identifying the coffee supplier. Holds values with a SQL type of INTEGER . It is defined as a foreign key that references the column SUP_ID in the SUPPLIERS table. Consequently, the DBMS will enforce that each value in this column matches one of the values in the corresponding column in the SUPPLIERS table.

  • PRICE : Stores the cost of the coffee per pound. Holds values with a SQL type of FLOAT because it needs to hold values with decimal points. (Note that money values would typically be stored in a SQL type DECIMAL or NUMERIC , but because of differences among DBMSs and to avoid incompatibility with earlier versions of JDBC, the tutorial uses the more standard type FLOAT .)

  • SALES : Stores the number of pounds of coffee sold during the current week. Holds values with a SQL type of INTEGER .

  • TOTAL : Stores the number of pounds of coffee sold to date. Holds values with a SQL type of INTEGER .

SUPPLIERS Table

The SUPPLIERS stores information about each of the suppliers:

SUP_IDSUP_NAMESTREETCITYSTATEZIP
101Acme, Inc.99 Market StreetGroundsvilleCA95199
49Superior Coffee1 Party PlaceMendocinoCA95460
150The High Ground100 Coffee LaneMeadowsCA93966

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

  • SUP_ID : Stores a number identifying the coffee supplier. Holds values with a SQL type of INTEGER . It is the primary key in this table.

  • SUP_NAME : Stores the name of the coffee supplier.

  • STREET , CITY , STATE , and ZIP : These columns store the address of the coffee supplier.

COF_INVENTORY Table

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

WAREHOUSE_IDCOF_NAMESUP_IDQUANDATE_VAL
1234House_Blend4902006_04_01
1234House_Blend_Decaf4902006_04_01
1234Colombian10102006_04_01
1234French_Roast4902006_04_01
1234Espresso15002006_04_01
1234Colombian_Decaf10102006_04_01

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

  • WAREHOUSE_ID : Stores a number identifying a warehouse.

  • COF_NAME : Stores the name of a particular type of coffee.

  • SUP_ID : Stores a number identifying a supplier.

  • QUAN : Stores a number indicating the amount of merchandise available.

  • DATE : Stores a timestamp value indicating the last time the row was updated.

MERCH_INVENTORY Table

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

ITEM_IDITEM_NAMESUP_IDQUANDATE
00001234Cup_Large00456282006_04_01
00001235Cup_Small00456362006_04_01
00001236Saucer00456642006_04_01
00001287Carafe00456122006_04_01
00006931Carafe0092732006_04_01
00006935PotHolder00927882006_04_01
00006977Napkin009271082006_04_01
00006979Towel00927242006_04_01
00004488CofMaker0873252006_04_01
00004490CofGrinder0873292006_04_01
00004495EspMaker0873242006_04_01
00006914Cookbook00927122006_04_01

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

  • ITEM_ID : Stores a number identifying an item.

  • ITEM_NAME : Stores the name of an item.

  • SUP_ID : Stores a number identifying a supplier.

  • QUAN : Stores a number indicating the amount of that item available.

  • DATE : Stores a timestamp value indicating the last time the row was updated.

COFFEE_HOUSES Table

The table COFFEE_HOUSES stores locations of coffee houses:

STORE_IDCITYCOFFEEMERCHTOTAL
10023Mendocino345020055455
33002Seattle469931097808
10040SF538628418227
32001Portland314735796726
10042SF286318744710
10024Sacramento198723414328
10039Carmel269111213812
10041LA153310072540
33005Olympia273315504283
33010Seattle321021775387
10035SF192210562978
10037LA214318764019
10034San_Jose123410322266
32004Eugene135611122468

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

  • STORE_ID : Stores a number identifying a coffee house. It indicates, among other things, the state in which the coffee house is located. A value beginning with 10, for example, means that the state is California. STORE_ID values beginning with 32 indicate Oregon, and those beginning with 33 indicate the state of Washington.

  • CITY : Stores the name of the city in which the coffee house is located.

  • COFFEE : Stores a number indicating the amount of coffee sold.

  • MERCH : Stores a number indicating the amount of merchandise sold.

  • TOTAL : Stores a number indicating the total amount of coffee and merchandise sold.

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:

  • DOCUMENT_NAME : Stores a string that identifies the URL.

  • URL : Stores a URL.

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:

ParameterDescription
driverFully 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.
urlDatabase connection URL that your DBMS JDBC driver uses to connect to a database.
useridName of a valid user in your DBMS.
passwordPassword of the user specified in userid
classpathrefFull path name of the JAR file that contains the class specified in driver
delimiterString or character that separates SQL statements. This sample uses the semicolon ( ; ).
autocommitBoolean value; if set to false , all SQL statements are executed as one transaction.
onerrorAction 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();
        }
    }
}