For the following examples, you need to create a few tables in your database. The first table, COFFEES, contains the essential information about the coffees sold at The Coffee Break, including the coffee names, their prices, the number of pounds sold the current week, and the number of pounds sold to date. The table COFFEES, which is describe in more detail later, is shown here:

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 Tables Used in the Example Database

The column storing the coffee name is COF_NAME, and it holds values with an SQL type of VARCHAR and a maximum length of 32 characters. Since the names are different for each type of coffee sold, the name uniquely identifies a particular coffee and serves as the primary key. The second column, named SUP_ID, holds a number identifying the coffee supplier; this number is of SQL type INTEGER. The third column, called PRICE, stores values with an SQL type of FLOAT because it needs to hold values with decimal points. (Note that money values would normally be stored in an SQL type DECIMAL or NUMERIC, but because of differences among DBMSs and to avoid incompatibility with older versions of JDBC, you are using the more standard type FLOAT for this tutorial.) The column named SALES stores values of SQL type INTEGER and indicates the number of pounds of coffee sold during the current week. The final column, TOTAL, contains an SQL INTEGER, which gives the total number of pounds of coffee sold to date.

SUPPLIERS, the second table in our database, gives 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 tables COFFEES and SUPPLIERS both contain the column SUP_ID. These two tables can be used in SELECT statements to get data based on the information in both tables. The column SUP_ID is the primary key in the table SUPPLIERS, and as such, it uniquely identifies each of the coffee suppliers. In the table COFFEES, SUP_ID is called a foreign key. (You can think of a foreign key as being foreign in the sense that it is imported from another table.) Note that each SUP_ID number appears only once in the SUPPLIERS table; this is required for it to be a primary key. In the COFFEES table, where it is a foreign key, it is all right for there to be duplicate SUP_ID numbers because one supplier may sell many types of coffee. Later in this chapter, you see an example of how to use primary and foreign keys in a SELECT statement.

Creating Tables

You can create your tables in at least three ways:

• Using the database or IDE graphical interface


• Using ANT


• Using JDBC SQL written into an application

Creating a Tables Using the NetBeans IDE

Most database vendors provide a graphical user interface (GUI) that makes creating tables in your database easy. In addition, many IDEs, such as the NetBeans IDE, also provides an easy-to-use GUI for table creation.

For example, in the NetBeans™ 5.0 IDE, creating tables in the Java DB™ database is easy. After starting the database, either through the pull-down menu, or by right-clicking Databases, you add tables simply by right-clicking the Tables icon and selecting BBBCreate Table. You get a dialog box that looks like the one below:

Fill in the dialog box fields and the NetBeans IDE creates the table. Adding or deleting rows is just as easy. Right-click the table to want to edit.

Creating Tables Using ANT

First, you can use a properties file to feed ANT, which has a simple sql target. Create a simple properties file, such as the one below:

 # Database properties are in derby.properties
 db.delimiter=;
 db.root=${javaee.home}/derby
 db.driver=org.apache.derby.jdbc.ClientDriver
 db.datasource=org.apache.derby.jdbc.ClientDataSource
 db.host=localhost
 db.port=1527
 db.sid=sun-appserv-samples
 db.url=jdbc:derby://${db.host}:${db.port}/${db.sid};create=true;
 db.user=APP
 db.pwd=APP 
 

Next, create a build.xml file that includes targets for compiling the Java code as well as a target similar to the following:

 <target name="create-db_common" depends="init,start-db,delete-db" 
               description="Create database tables and populate database.">
 -
    <sql driver="${db.driver}" url="${db.url}" userid="${db.user}" 
         password="${db.password}" classpathref="db.classpath" 
                 delimiter="${db.delimiter}" autocommit="false" onerror="abort">
 <transaction src="
          {javaee.tutorial.home}/examples/common/sql/${db.vendor}/tutorial.sql"/>
 </sql>
 </target>

The tutorial.sql file can contain the commands to create the tables and, possibly, populate the tables if need be, such as the example below:

 create table COFFEES(cof_name varchar32, price int, sup_id int,
 price float, sales int, total int);
 
 

The ';' matches the db.delimiter property.

NetBeans contains everything you need, including the Java DB and ANT. Download NetBeans.

Using SQL in an Application

Finally, you can create tables from within the application code itself. The following SQL statement creates the table named COFFEES. The entries within the outer pair of parentheses consist of the name of a column followed by a space and the SQL type to be stored in that column. A comma separates the entry for one column (consisting of column name and SQL type) from the next one. To be sure you are using the correct syntax, see your DBMS documentation. For the Java DB, read the current documentation.

This code does not end with a DBMS statement terminator, which can vary from DBMS to DBMS. For example, Oracle uses a semicolon (;) to indicate the end of a statement, and Sybase uses the word go. The driver you are using will automatically supply the appropriate statement terminator, and you will not need to include it in your JDBC code.

Note: In the CREATE TABLE statement, key words are printed in all capital letters, and each item is on a separate line. SQL does not require either; these conventions simply make statements easier to read. The standard in SQL is that keywords are not case sensitive, so, for example, the following SELECT statement can be written various ways. As an example, these two versions below are equivalent as far as SQL is concerned:

SELECT SUP_NAME, CITY
FROM Suppliers
WHERE SUP_NAME LIKE "Acme, Inc"
select SUP_Name, CITY from Suppliers where
SUP_NAME like "Acme, Inc;

Quoted material, however, is case sensitive: in the name " Washington," W must be capitalized, and the rest of the letters must be lowercase.

Requirements can vary from one DBMS to another when it comes to identifier names.