JDBC:Setting Up Tables

Setting Up Tables

The sample CoffeesTable.java creates the following table and data:

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:

• 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, the second table in the database, 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:

• 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.

Creating Tables

You can create tables with Apache Ant or JDBC API.

Creating Tables with Apache Ant

To create the SUPPLIERS and COFFEES tables 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:

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 two SQL statements that create the SUPPLIERS and COFFEES tables:

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 SUPPLIERS and COFFEES tables; run this target 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 {
      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 {
      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 an 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 SUPPLIERS and COFFEES tables with the tutorial sample code, run the following command (at a command prompt) in the directory <JDBC tutorial directory>: ant populate-tables
This Ant target executes the SQL statements in the file populate-tables.sql:

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 {
      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 {
        stmt.close();
      }