JDBC Basics

Setting Up Tables

Creating a Table

First, we will create one of the tables in our example database. This 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 we 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 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 we will use different names for each type of coffee sold, the name will uniquely identify a particular coffee and can therefore serve as the primary key. The second column, named SUP_ID , will hold a number that identifies the coffee supplier; this number will be 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, we 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 , which means that 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, however, it is perfectly all right for there to be duplicate SUP_ID numbers because one supplier may sell many types of coffee. Later in this chapter, you will see an example of how to use primary and foreign keys in a SELECT statement.

The following SQL statement creates the table 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. The type VARCHAR is created with a maximum length, so it takes a parameter indicating that maximum length. The parameter must be in parentheses following the type. The SQL statement shown here, for example, specifies that the name in column COF_NAME may be up to 32 characters long:

CREATE TABLE COFFEES
	(COF_NAME VARCHAR(32),
 SUP_ID INTEGER,
 PRICE FLOAT,
 SALES INTEGER,
 TOTAL INTEGER)

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.

Another thing we should point out about SQL statements is their form. 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 First_Name, Last_Name
FROM Employees
WHERE Last_Name LIKE "Washington"
select First_Name, Last_Name from Employees where
Last_Name like "Washington"

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. For example, some DBMSs require that column and table names be given exactly as they were created in the CREATE TABLE statement, while others do not. To be safe, we will use all uppercase for identifiers such as COFFEES and SUPPLIERS because that is how we defined them.

So far we have written the SQL statement that creates the table COFFEES . Now let's put quotation marks around it (making it a string) and assign that string to the variable createTableCoffees so that we can use the variable in our JDBC code later. As just shown, the DBMS does not care about where lines are divided, but in the Java programming language, a String object that extends beyond one line will not compile. Consequently, when you are giving strings, you need to enclose each line in quotation marks and use a plus sign (+) to concatenate them:

String createTableCoffees = "CREATE TABLE COFFEES " +
		"(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
		"SALES INTEGER, TOTAL INTEGER)";

The data types we used in our CREATE TABLE statement are the generic SQL types (also called JDBC types) that are defined in the class java.sql.Types . DBMSs generally use these standard types, so when the time comes to try out some JDBC applications, you can just use the application CreateCoffees.java , which uses the CREATE TABLE statement. If your DBMS uses its own local type names, we supply another application for you, which we will explain fully later.

Before running any applications, however, we are going to walk you through the basics of JDBC.

Creating JDBC Statements

A Statement object is what sends your SQL statement to the DBMS. You simply create a Statement object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a SELECT statement, the method to use is executeQuery . For statements that create or modify tables, the method to use is executeUpdate .

It takes an instance of an active connection to create a Statement object. In the following example, we use our Connection object con to create the Statement object stmt :

Statement stmt = con.createStatement();

At this point stmt exists, but it does not have an SQL statement to pass on to the DBMS. We need to supply that to the method we use to execute stmt . For example, in the following code fragment, we supply executeUpdate with the SQL statement from the example above:

stmt.executeUpdate("CREATE TABLE COFFEES " +
		"(COF_NAME VARCHAR(32), SUP_ID INTEGER, PRICE FLOAT, " +
		"SALES INTEGER, TOTAL INTEGER)");

Since we made a string out of the SQL statement and assigned it to the variable createTableCoffees , we could have written the code in this alternate form:

stmt.executeUpdate(createTableCoffees);

Executing Statements

We used the method executeUpdate because the SQL statement contained in createTableCoffees is a DDL (data definition language) statement. Statements that create a table, alter a table, or drop a table are all examples of DDL statements and are executed with the method executeUpdate . As you might expect from its name, the method executeUpdate is also used to execute SQL statements that update a table. In practice, executeUpdate is used far more often to update tables than it is to create them because a table is created once but may be updated many times.

The method used most often for executing SQL statements is executeQuery . This method is used to execute SELECT statements, which comprise the vast majority of SQL statements. You will see how to use this method shortly.

Entering Data into a Table

We have shown how to create the table COFFEES by specifying the names of the columns and the data types to be stored in those columns, but this only sets up the structure of the table. The table does not yet contain any data. We will enter our data into the table one row at a time, supplying the information to be stored in each column of that row. Note that the values to be inserted into the columns are listed in the same order that the columns were declared when the table was created, which is the default order.

The following code inserts one row of data, with Colombian in the column COF_NAME , 101 in SUP_ID , 7.99 in PRICE , 0 in SALES , and 0 in TOTAL . (Since The Coffee Break has just started out, the amount sold during the week and the total to date are zero for all the coffees to start with.) Just as we did in the code that created the table COFFEES , we will create a Statement object and then execute it using the method executeUpdate .

Since the SQL statement will not quite fit on one line on the page, we have split it into two strings concatenated by a plus sign (+) so that it will compile. Pay special attention to the need for a space between COFFEES and VALUES . This space must be within the quotation marks and may be after COFFEES or before VALUES ; without a space, the SQL statement will erroneously be read as " INSERT INTO COFFEESVALUES . . ." and the DBMS will look for the table COFFEESVALUES . Also note that we use single quotation marks around the coffee name because it is nested within double quotation marks. For most DBMSs, the general rule is to alternate double quotation marks and single quotation marks to indicate nesting.

Statement stmt = con.createStatement();
stmt.executeUpdate(
    "INSERT INTO COFFEES " + 
    "VALUES ('Colombian', 101, 7.99, 0, 0)");

The code that follows inserts a second row into the table COFFEES . Note that we can just reuse the Statement object stmt rather than having to create a new one for each execution.

stmt.executeUpdate("INSERT INTO COFFEES " +
    "VALUES ('French_Roast', 49, 8.99, 0, 0)");

Values for the remaining rows can be inserted as follows:

stmt.executeUpdate("INSERT INTO COFFEES " +
    "VALUES ('Espresso', 150, 9.99, 0, 0)");
stmt.executeUpdate("INSERT INTO COFFEES " +
    "VALUES ('Colombian_Decaf', 101, 8.99, 0, 0)");
stmt.executeUpdate("INSERT INTO COFFEES " +
    "VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0)");

Getting Data from a Table

Now that the table COFFEES has values in it, we can write a SELECT statement to access those values. The star (*) in the following SQL statement indicates that all columns should be selected. Since there is no WHERE clause to narrow down the rows from which to select, the following SQL statement selects the whole table:

SELECT * FROM COFFEES

The result, which is the entire table, will look similar to the following:

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 result above is what you would see on your terminal if you entered the SQL query directly to the database system. When we access a database through a Java application, as we will be doing shortly, we will need to retrieve the results so that we can use them. You will see how to do this in the next section.

Here is another example of a SELECT statement; this one will get a list of coffees and their respective prices per pound:

SELECT COF_NAME, PRICE FROM COFFEES

The results of this query will look something like this:

 
COF_NAME				PRICE
--------		----------   	-----
Colombian				7.99
French_Roast				8.99
Espresso				9.99
Colombian_Decaf	  			8.99
French_Roast_Decaf			9.99

The SELECT statement above generates the names and prices of all of the coffees in the table. The following SQL statement limits the coffees selected to just those that cost less than $9.00 per pound:

 
SELECT COF_NAME, PRICE
FROM COFFEES
WHERE PRICE < 9.00

The results would look similar to this:

 
COF_NAME				PRICE
--------		-------		-----
Colombian				7.99
French_Roast				8.99
Colombian Decaf	 	 		8.99

JDBC Basics