|
|
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 ofVARCHARand 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 ofFLOATbecause it needs to hold values with decimal points. (Note that money values would normally be stored in an SQL typeDECIMALorNUMERIC, but because of differences among DBMSs and to avoid incompatibility with older versions of JDBC, we are using the more standard typeFLOATfor this tutorial.) The column namedSALESstores values of SQL typeINTEGERand indicates the number of pounds of coffee sold during the current week. The final column,TOTAL, contains an SQLINTEGERwhich 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
COFFEESandSUPPLIERSboth contain the columnSUP_ID, which means that these two tables can be used inSELECTstatements to get data based on the information in both tables. The columnSUP_IDis the primary key in the tableSUPPLIERS, and as such, it uniquely identifies each of the coffee suppliers. In the tableCOFFEES,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 eachSUP_IDnumber appears only once in theSUPPLIERStable; this is required for it to be a primary key. In theCOFFEEStable, where it is a foreign key, however, it is perfectly all right for there to be duplicateSUP_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 aSELECTstatement.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 typeVARCHARis 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
CREATETABLEstatement, 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 followingSELECTstatement 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
CREATETABLEstatement, while others do not. To be safe, we will use all uppercase for identifiers such asCOFFEESandSUPPLIERSbecause 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 variablecreateTableCoffeesso 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, aStringobject 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
CREATETABLEstatement 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 theCREATETABLEstatement. 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
Statementobject is what sends your SQL statement to the DBMS. You simply create aStatementobject and then execute it, supplying the appropriate execute method with the SQL statement you want to send. For aSELECTstatement, 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
Statementobject. In the following example, we use ourConnectionobjectconto create theStatementobjectstmt: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 executestmt. For example, in the following code fragment, we supplyexecuteUpdatewith 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
executeUpdatebecause the SQL statement contained increateTableCoffeesis 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 methodexecuteUpdateis 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 executeSELECTstatements, 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 columnCOF_NAME,101inSUP_ID,7.99inPRICE,0inSALES, and0inTOTAL. (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 aStatementobject 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
COFFEESandVALUES. This space must be within the quotation marks and may be afterCOFFEESor 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 theStatementobjectstmtrather 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 aSELECTstatement to access those values. The star (*) in the following SQL statement indicates that all columns should be selected. Since there is noWHEREclause 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
SELECTstatement; 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
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.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-2004 Sun Microsystems, Inc. All rights reserved.