Download the JDK
Search the Tutorials
Hide the TOC
Trail: JDBC(TM) Database Access
Lesson: JDBC Basics
JDBC Basics
Getting Started
Setting Up a Database
Establishing a Connection
Setting Up Tables
Retrieving Values from Result Sets
Updating Tables
Milestone: The Basics of JDBC
Using Prepared Statements
Using Joins
Using Transactions
Stored Procedures
SQL Statements for Creating a Stored Procedure
Creating Complete JDBC Applications
Running the Sample Applications
Creating an Applet from an Application
Home Page > JDBC(TM) Database Access > JDBC Basics
Setting Up Tables

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

Previous page: Establishing a Connection
Next page: Retrieving Values from Result Sets