Setting Up Tables

This page describes all the tables used in the JDBC tutorial and how to create them:本页介绍JDBC教程中使用的所有表以及如何创建它们：

• 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:COFFEES表存储咖啡休息时可供出售的咖啡的相关信息：

The following describes each of the columns in the COFFEES table:以下描述了COFFEES表中的每一列：

• COF_NAME: Stores the coffee name. ：存储咖啡名称。Holds values with a SQL type of VARCHAR with a maximum length of 32 characters. 保存SQL类型为VARCHAR的值，最大长度为32个字符。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. 保存SQL类型为INTEGER的值。It is defined as a foreign key that references the column SUP_ID in the SUPPLIERS table. 它被定义为引用SUPPLIERS表中SUP_ID列的外键。Consequently, the DBMS will enforce that each value in this column matches one of the values in the corresponding column in the SUPPLIERS table.因此，DBMS将强制此列中的每个值与SUPPLIERS表中相应列中的一个值匹配。
• 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. ：存储每磅咖啡的成本。使用SQL类型的浮点值保存值，因为它需要使用小数点保存值。(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.)（请注意，货币值通常存储在SQL类型的DECIMAL或NUMERIC中，但由于DBMS之间存在差异，为了避免与早期版本的JDBC不兼容，本教程使用了更标准的类型FLOAT。）
• SALES: Stores the number of pounds of coffee sold during the current week. ：存储本周售出的咖啡磅数。Holds values with a SQL type of INTEGER.保存SQL类型为INTEGER的值。
• TOTAL: Stores the number of pounds of coffee sold to date. Holds values with a SQL type of INTEGER.：存储迄今为止售出的咖啡磅数。保存SQL类型为INTEGER的值。

SUPPLIERS Table表

The SUPPLIERS stores information about each of the suppliers:SUPPLIERS表存储关于每个供应商的信息：

The following describes each of the columns in the SUPPLIERS table:以下描述了SUPPLIERS表中的每一列：

• 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.保存SQL类型为INTEGER的值。它是此表中的主键。
• SUP_NAME: Stores the name of the coffee supplier.：存储咖啡供应商的名称。
• STREET, CITY, STATE, and ZIP: These columns store the address of the coffee supplier.STREET、CITY、STATE和ZIP：这些列存储咖啡供应商的地址。

COF_INVENTORY Table表

The table COF_INVENTORY stores information about the amount of coffee stored in each warehouse:COF_INVENTORY表存储关于每个仓库中存储的咖啡量的信息：

The following describes each of the columns in the COF_INVENTORY table:以下描述了COF_INVENTORY表中的每一列：

• 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 TableMERCH_库存表

The table MERCH_INVENTORY stores information about the amount of non-coffee merchandise in stock:MERCH_INVENTORY表存储关于库存中非咖啡商品数量的信息：

The following describes each of the columns in the MERCH_INVENTORY table:以下描述了MERCH_INVENTORY表中的每一列：

• 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:COFFEE_HOUSES表存储咖啡馆的位置：

The following describes each of the columns in the COFFEE_HOUSES table:以下描述了COFFEE_HOUSES表中的每一列：

• 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. 例如，以10开头的值表示该州为加利福尼亚州。STORE_ID values beginning with 32 indicate Oregon, and those beginning with 33 indicate the state of Washington.STORE_ID值以32开头表示俄勒冈州，以33开头表示华盛顿州。
• 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. table DATA_存储库存储引用文档和咖啡休息时间感兴趣的其他数据的URL。The script populate_tables.sql does not add any data to this table. 脚本populates_tables.sql不会向该表添加任何数据。The following describes each of the columns in this table:下表介绍了该表中的每一列：

• DOCUMENT_NAME: Stores a string that identifies the URL.：存储标识URL的字符串。
• URL: Stores a URL.：存储URL。

Creating Tables创建表

You can create tables with Apache Ant or JDBC API.您可以使用ApacheAnt或JDBCAPI创建表。

Creating Tables with Apache Ant使用ApacheAnt创建表

To create the tables used with the tutorial sample code, run the following command in the directory <JDBC tutorial directory>:要创建与教程示例代码一起使用的表，请在目录<JDBC tutorial directory>中运行以下命令：

ant setup

This command runs several Ant targets, including the following, build-tables (from the build.xml file):此命令运行多个Ant目标，包括以下build-tables（来自build.xml文件）：

<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:此示例指定以下sql Ant任务参数的值：

The sample stores the values of these parameters in a separate file. 该示例将这些参数的值存储在单独的文件中。The build file build.xml retrieves these values with the import task:生成文件build.xml通过import任务检索以下值：

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

The transaction element specifies a file that contains SQL statements to execute. transaction元素指定包含要执行的SQL语句的文件。The file create-tables.sql contains SQL statements that create all the tables described on this page. 文件create-tables.sql包含创建此页上描述的所有表的sql语句。For example, the following excerpt from this file creates the tables SUPPLIERS and COFFEES:例如，此文件的以下摘录创建了SUPPLIERS和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. ：文件build.xml包含另一个名为drop-tables的目标，该目标删除教程使用的表。The setup target runs drop-tables before running the build-tables target.setup目标在运行drop-tables目标之前运行drop-tables。

Creating Tables with JDBC API使用JDBC API创建表

The following method, SuppliersTable.createTable, creates the SUPPLIERS table:以下方法SuppliersTable.createTable创建SUPPLIERS表：

public void createTable() throws SQLException {
    String createString =
      "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))";
    
    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

The following method, CoffeesTable.createTable, creates the COFFEES table:以下方法CoffeesTable.createTable创建COFFEES表：

public void createTable() throws SQLException {
    String createString =
      "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))";
    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate(createString);
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

In both methods, con is a Connection object and dbName is the name of the database in which you are creating the table.在这两种方法中，con都是连接对象，dbName是创建表的数据库的名称。

To execute the SQL query, such as those specified by the String createString, use a Statement object. 要执行SQL查询，例如由字符串createString指定的查询，请使用Statement对象。To create a Statement object, call the method Connection.createStatement from an existing Connection object. 要创建Statement对象，请从现有Connection对象调用Connection.createStatement方法。To execute a SQL query, call the method Statement.executeUpdate.要执行SQL查询，请调用方法Statement.executeUpdate。

All Statement objects are closed when the connection that created them is closed. 创建Statement对象的连接关闭时，所有语句对象都将关闭。However, it is good coding practice to explicitly close Statement objects as soon as you are finished with them. 但是，一完成Statement对象，就显式关闭它们是一种很好的编码实践。This allows any external resources that the statement is using to be released immediately. 这允许立即释放语句使用的任何外部资源。Close a statement by calling the method Statement.close. 通过调用方法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.将此语句放在finally中，以确保即使正常程序流因引发异常（如SQLException）而中断，它也会关闭。

Note: You must create the SUPPLIERS table before the COFFEES because COFFEES contains a foreign key, SUP_ID that references SUPPLIERS.：您必须在COFFEES之前创建SUPPLIERS表，因为COFFEES包含引用COFFEES的外键SUP_ID。

Populating Tables填充表

Similarly, you can insert data into tables with Apache Ant or JDBC API.类似地，您可以使用ApacheAnt或JDBCAPI将数据插入表中。

Populating Tables with Apache Ant使用Apache Ant填充表

In addition to creating the tables used by this tutorial, the command ant setup also populates these tables. 除了创建本教程使用的表外，命令ant setup还填充这些表。This command runs the Ant target populate-tables, which runs the SQL script populate-tables.sql.此命令运行Ant目标populate-tables，该表运行SQL脚本populate-tables.sql。

The following is an excerpt from populate-tables.sql that populates the tables SUPPLIERS and COFFEES:以下是从populate-tables.sql中摘录的内容，用于填充SUPPLIERS和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用JDBCAPI填充表

The following method, SuppliersTable.populateTable, inserts data into the table:以下方法SuppliersTable.populateTable将数据插入表中：

public void populateTable() throws SQLException {
    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate("insert into SUPPLIERS " +
                         "values(49, 'Superior Coffee', '1 Party Place', " +
                         "'Mendocino', 'CA', '95460')");
      stmt.executeUpdate("insert into SUPPLIERS " +
                         "values(101, 'Acme, Inc.', '99 Market Street', " +
                         "'Groundsville', 'CA', '95199')");
      stmt.executeUpdate("insert into SUPPLIERS " +
                         "values(150, 'The High Ground', '100 Coffee Lane', " +
                         "'Meadows', 'CA', '93966')");
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }

The following method, CoffeesTable.populateTable, inserts data into the table:以下方法CoffeesTable.populateTable将数据插入表中：

public void populateTable() throws SQLException {
    try (Statement stmt = con.createStatement()) {
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian', 00101, 7.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast', 00049, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Espresso', 00150, 9.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian_Decaf', 00101, 8.99, 0, 0)");
      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");
    } catch (SQLException e) {
      JDBCTutorialUtilities.printSQLException(e);
    }
  }