Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
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 tableCOFFEES
, which we describe in more detail later, is shown here:
COF_NAME
Colombian
French_Roast
Espresso
Colombian_Decaf
French_Roast_Decaf
The column storing the coffee name is
COF_NAME,
and it holds values with an SQL type ofVARCHAR
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, namedSUP_ID
, will hold a number that identifies the coffee supplier; this number will be of SQL typeINTEGER
. The third column, calledPRICE,
stores values with an SQL type ofFLOAT
because it needs to hold values with decimal points. (Note that money values would normally be stored in an SQL typeDECIMAL
orNUMERIC
, but because of differences among DBMSs and to avoid incompatibility with older versions of JDBC, we are using the more standard typeFLOAT
for this tutorial.) The column namedSALES
stores values of SQL typeINTEGER
and indicates the number of pounds of coffee sold during the current week. The final column,TOTAL
, contains an SQLINTEGER
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
The tables
COFFEES
andSUPPLIERS
both contain the columnSUP_ID
, which means that these two tables can be used inSELECT
statements to get data based on the information in both tables. The columnSUP_ID
is the primary key in the tableSUPPLIERS
, and as such, it uniquely identifies each of the coffee suppliers. In the tableCOFFEES
,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 eachSUP_ID
number appears only once in theSUPPLIERS
table; this is required for it to be a primary key. In theCOFFEES
table, where it is a foreign key, however, it is perfectly all right for there to be duplicateSUP_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 aSELECT
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 typeVARCHAR
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 followingSELECT
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 asCOFFEES
andSUPPLIERS
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 variablecreateTableCoffees
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, aString
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 classjava.sql.Types
. DBMSs generally use these standard types, so when the time comes to try out some JDBC applications, you can just use the applicationCreateCoffees.java
, which uses theCREATE
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.
A
Statement
object is what sends your SQL statement to the DBMS. You simply create aStatement
object and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For aSELECT
statement, the method to use isexecuteQuery
. For statements that create or modify tables, the method to use isexecuteUpdate
.It takes an instance of an active connection to create a
Statement
object. In the following example, we use ourConnection
objectcon
to create theStatement
objectstmt
: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 executestmt
. For example, in the following code fragment, we supplyexecuteUpdate
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
);
We used the method
executeUpdate
because the SQL statement contained increateTableCoffees
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 methodexecuteUpdate
. As you might expect from its name, the methodexecuteUpdate
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 executeSELECT
statements, 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
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 columnCOF_NAME
,101
inSUP_ID
,7.99
inPRICE
,0
inSALES
, and0
inTOTAL
. (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 tableCOFFEES
, we will create aStatement
object and then execute it using the methodexecuteUpdate
.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
andVALUES
. This space must be within the quotation marks and may be afterCOFFEES
or beforeVALUES
; without a space, the SQL statement will erroneously be read as "INSERT INTO COFFEESVALUES . .
." and the DBMS will look for the tableCOFFEESVALUES
. 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 theStatement
objectstmt
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)");
Now that the table
COFFEES
has values in it, we can write aSELECT
statement to access those values. The star (*) in the following SQL statement indicates that all columns should be selected. Since there is noWHERE
clause to narrow down the rows from which to select, the following SQL statement selects the whole table:SELECT * FROM COFFEESThe 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 0The 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 COFFEESThe 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.99The
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.00The 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
Feedback Form |
Copyright 1995-2005 Sun Microsystems, Inc. All rights reserved.