Setting Up Tables

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

• COFFEES Table
• SUPPLIERS Table
• COF_INVENTORY Table
• MERCH_INVENTORY Table
• COFFEE_HOUSES Table
• DATA_REPOSITORY Table
• Creating Tables
• Populating Tables

COFFEES Table

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

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 pet 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:

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:

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 coffee 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:

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:

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 coffee 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 create-tables

This command runs the following Ant target (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:

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; run this target first before running create-tables if you want to re-create these tables.

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

To populate the tables used by the tutorial sample code, run the following command (at a command prompt) in the directory <JDBC tutorial directory>: ant populate-tables

For example, the following excerpt from populate-tables.sql 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(); }
      }
  }