Getting started with JDBC (III)

Author: axeon create a table first, we create one of the tables in our sample database COFFEES, containing the necessary information about the coffee sold in the coffee shop, including the coffee name, their prices, the number of LBS sold this week and the quantity sold so far. The COFFEES table will be described in detail later, as follows: COF_NAMESUP_IDPRICESALESTOTALColomb JDBC

Author: axeon
Create a table
First, we create a COFFEES table in our sample database, which contains the necessary information about the coffee sold in the coffee shop, including the coffee name, their prices, the number of LBS sold this week and the quantity sold so far. The COFFEES table will be described in detail later, as follows:

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 that stores the coffee name is COF_NAME, and its SQL data type is VARCHAR. The maximum length is 32 characters. Because each type of coffee we sell uses different names, names can be used as unique identifiers for identifying coffee, so they can be used as primary keys. The second column is SUP_ID, which is used to save the coffee supplier id. Its SQL data type is INTEGER. The 3rd column is called PRICE because it needs to save the decimal number, so its SQL type is FLOAT. (Note: The SQL type of money is usually DECIMAL or NUMERIC, but there are differences between different DBMSs, to avoid the incompatibility of old versions of JDBC, we use a more standard FLOAT type in this tutorial.) the SQL type of the SALES column is INTEGER, and its value is the pound of the coffee sold in the week. In the last column, the total SQL type is INTEGER, saving the TOTAL pounds of coffee sold so far.


The second table SUPPLIERS in the database stores the information of each vendor:

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

COFFEES and SUPPLIERS both contain the SUP_ID column, which means that you can use the SELECT statement to obtain relevant information from the two tables. The column SUP_ID is the primary key of the SUPPLIERS table and is used to uniquely identify each coffee supplier. In the COFFEES table, the SUP_ID column is called a foreign key. Note that each SUP_ID value appears only once in the SUPPLIERS Table. this is required for primary keys. In the COFFEES table, as a foreign key, it can obviously have duplicate SUP_ID values, because the same supplier can provide many types of coffee. At the end of this section, you will see an example of how to use the primary key and foreign key in the SELECT statement.

The following SQL statement is used to create a COFFEES table. A column is composed of column names, spaces, and SQL types. Separate columns (including column names and their SQL types) with commas. VARCHAR type creation defines the maximum length, so it requires a parameter to indicate the maximum length. The parameter must be enclosed in brackets after the type. The SQL statement is as follows. the length of the column COF_NAME is limited to 32 characters:

CREATE TABLE COFFEES
(COF_NAME VARCHAR (32 ),
SUP_ID INTEGER,
Price float,
Sales integer,
Total integer)

These codes do not contain the DBMS statement terminator, because each DBMS may be different. For example, Oracle uses a semicolon (;) as the end of the statement, while Sybase uses go. The driver you are using automatically provides the appropriate statement terminator, so you do not need to include it in your JDBC code.

In addition, we should point out the SQL statement format. In the create table statement, the keywords use uppercase characters, and each project starts with another line. SQL does not have this requirement; it is only for easier reading. The SQL standard is case-insensitive. Therefore, the SELECT statement in the following example can be written in multiple ways. Therefore, the following two statements are the same for SQL.

SELECT First_Name, Last_Name
FROM Employees
WHERE Last_Name LIKE "Washington"

Select First_Name, Last_Name from Employees where