|Start of Tutorial > Start of Trail > Start of Lesson||
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:
The column storing the coffee name is
COF_NAME,and it holds values with an SQL type of
VARCHARand 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
FLOATbecause it needs to hold values with decimal points. (Note that money values would normally be stored in an SQL type
NUMERIC, but because of differences among DBMSs and to avoid incompatibility with older versions of JDBC, we are using the more standard type
FLOATfor this tutorial.) The column named
SALESstores values of SQL type
INTEGERand indicates the number of pounds of coffee sold during the current week. The final column,
TOTAL, contains an SQL
INTEGERwhich 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:
SUPPLIERSboth contain the column
SUP_ID, which means that these two tables can be used in
SELECTstatements to get data based on the information in both tables. The column
SUP_IDis the primary key in the table
SUPPLIERS, and as such, it uniquely identifies each of the coffee suppliers. In the table
SUP_IDis 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_IDnumber appears only once in the
SUPPLIERStable; this is required for it to be a primary key. In the
COFFEEStable, where it is a foreign key, however, it is perfectly all right for there to be duplicate
SUP_IDnumbers 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
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
VARCHARis 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
TABLEstatement, 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
SELECTstatement 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 "
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
TABLEstatement, while others do not. To be safe, we will use all uppercase for identifiers such as
SUPPLIERSbecause 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
createTableCoffeesso 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
Stringobject 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
TABLEstatement 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
TABLEstatement. 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.
Statementobject is what sends your SQL statement to the DBMS. You simply create a
Statementobject and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For a
SELECTstatement, the method to use is
executeQuery. For statements that create or modify tables, the method to use is
It takes an instance of an active connection to create a
Statementobject. In the following example, we use our
conto create the
stmt:Statement stmt = con.createStatement();
At this point
stmtexists, 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
executeUpdatewith 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(
We used the method
executeUpdatebecause the SQL statement contained in
createTableCoffeesis 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
executeUpdateis also used to execute SQL statements that update a table. In practice,
executeUpdateis 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
SELECTstatements, which comprise the vast majority of SQL statements. You will see how to use this method shortly.
We have shown how to create the table
COFFEESby 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
Colombianin the column
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
Statementobject and then execute it using the method
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
VALUES. This space must be within the quotation marks and may be after
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
stmtrather 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)");
Now that the table
COFFEEShas values in it, we can write a
SELECTstatement to access those values. The star (*) in the following SQL statement indicates that all columns should be selected. Since there is no
WHEREclause 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
SELECTstatement; 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
SELECTstatement 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||
Copyright 1995-2005 Sun Microsystems, Inc. All rights reserved.