Getting started with JDBC (III)

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
Last_Name like "Washington"

However, the content in quotation marks is case-sensitive: In the name "Washington", "W" must be capitalized, and the remaining characters must be lowercase.

For identifiers, different DBMS have different requirements. For example, some DBMSs require that the column names and table names must be the same as those at creation, and some do not. For security, we use uppercase identifiers such as COFFEES and SUPPLIERS, because they are defined in that way.

At the end of this period, we wrote the SQL statement for creating the COFFEES table. Now we add quotation marks (making it a string) to it, and assign the string value to the variable createTableCoffees. We can use this variable in future JDBC code. As you can see, DBMS does not care about the branch, but for Java, the String object branch cannot be compiled. Therefore, we can use the plus sign (+) to connect the strings in each row.

String createTableCoffees = "create table coffees" +
"(COF_NAME VARCHAR (32), SUP_ID INTEGER, price float," +
"Sales integer, total integer )";

The data Types we use in the create table statement are general SQL Types (also known as JDBC Type) which are defined in the java. SQL. Types class. DBMSs usually uses these standard types. Therefore, when you try some JDBC applications, you can directly use the CreateCoffees. java application, which uses the create table statement. If your DBMS uses its own local type name, we will provide you with other applications, which will be explained in detail later.

Before using any application, of course, we will let you understand the basis of JDBC.

Create a JDBC Statements object
The Statement object is used to send SQL statements to DBMS. You only need to create a Statement object and execute it, and use the appropriate method to execute the SQL Statement you sent. ExecuteQuery can be used for SELECT statements. The statement used to create or modify a table is executeUpdate.

An active connection is required to create an instance of the Statement object. In the following example, we use our Connection object con to create the Statement object stmt:

Statement stmt = con. createStatement ();

Stmt already exists, but it has not passed SQL statements to DBMS. We need to provide the Statement Method for SQL statements as parameters. For example, in the following code segment, we use the SQL statement in the preceding example as the executeUpdate parameter:

Stmt.exe cuteUpdate ("create table coffees" +
"(COF_NAME VARCHAR (32), SUP_ID INTEGER, price float," +
"Sales integer, total integer )");

Because the SQL statement has been assigned to the createTableCoffees variable, we can write the code as follows:

Stmt.exe cuteUpdate (createTableCoffees );

Execution statement
We use the executeUpdate method because the SQL statement in createTableCoffees is a DDL (Data Definition Language) statement. The example of creating, changing, and deleting a table is a DDL statement. It must be executed using the executeUpdate method. You can also see from its name that the executeUpdate method is also used to execute the SQL statement for updating the table. In fact, compared to creating a table, executeUpdate takes more time to update the table, because the table only needs to be created once, but is often updated.

The most commonly used method for executing SQL statements is executeQuery. This method is used to execute SELECT statements, which are almost the most commonly used SQL statements. Now you will see how to use this method.

Input data in the table
We have shown how to create the table COFFEES by specifying the column name and data type, but this only creates the table structure. The table does not have any data. We input a row of data to the table and provide information for each column. Note that the display sequence of the inserted data is the same as that of the table when it is created. This is the default order.

The following code inserts a row of data. The COF_NAME value is Colombian, SUP_ID is 101, PRICE is 7.99, SALES 0, TOTAL 0. Just like creating a COFFEES table, we create a Statement object and execute the executeUpdate method.

Because the SQL statement does not show any rows, we divide it into two rows and connect them with the plus sign (+. Note that there must be spaces between COFFEES and VALUES. This space must be enclosed in quotation marks and between COFFEES and VALUES; without this space, the SQL statement will be incorrectly read as "INSERT INTO COFFEESVALUES... ", and the DBMS will look for the table COFFEESVALUES. Note that we use single quotes on coffee name.

Statement stmt = con. createStatement ();
Stmt.exe cuteUpdate (
"Insert into coffees" +
"VALUES ('canonicalbian ', 101, 7.99, 0, 0 )");

The following code inserts the second row into the COFFEES table. We can use the Statement object without creating a new one for each execution.

Stmt.exe cuteUpdate ("insert into coffees" +
"VALUES ('French _ Roast ', 49, 8.99, 0, 0 )");

The data of the remaining row is as follows:

Stmt.exe cuteUpdate ("insert into coffees" +
"VALUES ('Espresso ', 150, 9.99, 0, 0 )");
Stmt.exe cuteUpdate ("insert into coffees" +
"VALUES ('pipeline_decaf ', 101, 8.99, 0, 0 )");
Stmt.exe cuteUpdate ("insert into coffees" +
"VALUES ('French _ Roast_Decaf ', 49, 9.99, 0, 0 )");

Retrieve data from the table
Since data already exists in the COFFEES table, we can write a SELECT statement to obtain these values. In the following SQL statement, the star number (*) indicates that all columns are selected. Because the WHERE clause is not used to limit the selected rows, the following SQL statement selects the entire table.

SELECT * FROM COFFEES

The result is the data of the entire table, 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

If you enter the SQL query statement directly in the database system, you will see the above result on your terminal. When we access a database through a Java application, as we will do right away, we need to retrieve the results so that we can use them. You will see how to implement it in the next section.

This is another example of the SELECT statement, which will get a list of coffee and its respective unit prices per pound.

SELECT COF_NAME, PRICE FROM COFFEES

The query result set will take the following form:

COF_NAME PRICE
-----------------------
Colombian 7.99
French_Roast 8.99
Espresso 9.99
Colormbian_decaf 8.99
French_Roast_Decaf 9.99

The preceding SELECT statement obtains the names and prices of all coffee. The following SELECT statement limits those coffee whose price per pound is less than $9.00 to be selected.
SELECT COF_NAME, PRICE
FROM COFFEES
Where price: <9.00

The result set will take the following form:

COF_NAME PRICE
--------------------
Colombian 7.99
French_Roast 8.99
Colombian Decaf 8.99