The Java TM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC Database Access
Lesson: 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

| Start of Tutorial > Start of Trail > Start of Lesson | Search