Getting Started with JDBC (iii)-Setting up tables

Author: Axeon
Create a table
First, we create one of the tables in our sample database coffees, which contains the necessary information about the coffee sold at the coffee shop, including the name of the coffee, their price, how many pounds they sold this week and the number they have sold so far. About the coffees table we will describe 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, with a maximum length of 32 characters. Because each type of coffee we sell uses a different name, the name can be used as a unique identification of the coffee, so it can be used as a primary key. The second column, called SUP_ID, is used to hold the coffee vendor identification, and its SQL data type is INTEGER. The 3rd column is named Price because it needs to hold decimal digits, so its SQL type is FLOAT. (Note that the SQL type of the usual money is DECIMAL or NUMERIC, but there are differences between the different DBMSs, in order to avoid the incompatibility of the old version of JDBC in this tutorial we adopt a more standard FLOAT type) The SQL type of the SALES column is INTEGER and its value is The number of pounds of coffee sold this week. In the last column, Total has a SQL type of INTEGER that holds the overall number of points of coffee sold so far.

The second table in the database, suppliers, stores information for each vendor:

sup_id Sup_name Street City State ZIP
ACME, Inc. Market Street Groundsville CA 95199
Superior Coffee 1 Party place Mendocino CA 95460
The high Ground Coffee Lane Meadows CA 93966

Coffees and suppliers both contain column sup_id, which means that information can be obtained from both tables using the SELECT statement. The column sup_id is the primary key for the Suppliers table, and is used to uniquely identify each coffee vendor. In the coffees table, the sup_id column is called the foreign key. Note that each sup_id value appears only once in the Suppliers table, which is required for the primary key. In the coffees table, it acts as a foreign key, apparently it can have duplicate sup_id values, because the same supplier can provide many kinds of coffee. At the end of this section, you will see an example of how to use a primary key and a foreign key in a SELECT statement.

The following SQL statement is used to create a coffees table. Columns consist of column names and spaces and SQL types. columns, including column names and their SQL types, are separated by commas. VARCHAR type creation defines the maximum length, so it needs to have a parameter to represent the maximum length. parameter must be in parentheses after the type. The SQL statement is as follows, and the length of the column Cof_name is limited to no more than 32 characters:

CREATE TABLE Coffees
(Cof_name VARCHAR (32),
sup_id INTEGER,
Price FLOAT,
SALES INTEGER,
Total INTEGER)

The code does not have a DBMS statement terminator because each DBMS may be different. For example, Oracle uses a semicolon (;) As the end of the statement, and Sybase uses go. The driver you use will automatically provide the appropriate statement terminator, so you don't need to include it in your JDBC code.

In addition, we should point out the format of the SQL statement. In the CREATE TABLE statement, the keyword takes uppercase characters, and each item is on a different line. SQL does not have this requirement; just to make it easier to read. The SQL standard is case-insensitive for keywords, so the SELECT statement in the following example can be written in several ways. So the following two different sentences 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 contents of the quotes are case-sensitive: in the name "Washington", "W" must be capitalized and the remaining characters must be lowercase.

For identities, different DBMS have different requirements, for example, some DBMSs require that the column names and table names must be the same as when they were created, and some do not. For security reasons, we all use uppercase logos such as coffees, suppliers, because we define them that way.

We wrote the SQL statement to create the coffees table. Now we enclose it in quotes (make it a string), and the string is assigned to the variable createtablecoffees, and we can use this variable in future JDBC code. As you can see, the DBMS does not care about branches, but for the Java language, String object branches are not compiled. Thus, we can connect the strings of each row with a plus sign (+).

String createtablecoffees = "CREATE TABLE coffees" +
"(Cof_name VARCHAR), sup_id INTEGER, Price FLOAT," +
"SALES integer, Total integer";

The data types we use in the CREATE TABLE statement are common SQL types (also called JDBC types) that are defined in the class java.sql.Types. DBMSs typically use these standard types, so when you try some JDBC applications, you can use the Createcoffees.java application directly, which uses the CREATE TABLE statement. If your DBMS uses its own local type name, we supply you with other applications, which we will explain in detail later.

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

Creating a JDBC Statements Object
The Statement object is used to send SQL statements to the DBMS. You simply create a Statement object and then execute it, using the appropriate method to execute the SQL statement you send. For the SELECT statement, you can use ExecuteQuery. To create or modify statements for a table, the method used 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 ();

This stmt already exists, but it has not yet passed the SQL statement to the DBMS. We need to provide SQL statements as parameters to the Statement method we use. For example, in the following code snippet, we use the SQL statement in the example above as a executeupdate parameter:

Stmt.executeupdate ("CREATE TABLE coffees" +
"(Cof_name VARCHAR), sup_id INTEGER, Price FLOAT," +
"SALES integer, Total integer");

Because we've already assigned the SQL statement to the createtablecoffees variable, we can write the code as follows:

Stmt.executeupdate (createtablecoffees);

EXECUTE statement
We use the Executeupdate method because the SQL statement in Createtablecoffees is a DDL (data definition Language) statement. Creating tables, changing tables, and deleting tables are examples of DDL statements that are executed using the Executeupdate method. You can also see from its name that the method executeupdate is also used to execute the Update table SQL statement. In fact, executeupdate is used to update tables more often than creating tables, because tables need to be created once but are frequently updated.

The most used method of executing SQL statements is executequery. This method is used to execute the SELECT statement, which is almost the most frequently used SQL statement. Immediately you will see how to use this method.

Enter data in a table
We have shown how to create table coffees by specifying column names, data types, but this only establishes the structure of the table. The table does not have any data yet. We input a row of data into a table, provide information about each column, and note that the inserted data is displayed in the same order as the table was created, in the default order.

The following code inserts a row of data with a value of cof_name colombian,sup_id of 101,price to 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 appear in one line, we divide it into two lines and are connected by a plus sign (+). It is particularly noteworthy that there are spaces between coffees and VALUES. This space must be within 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 table coffeesvalues. Also note that we use single quotes on coffee name.

Statement stmt = Con.createstatement ();
Stmt.executeupdate (
"INSERT into coffees" +
"VALUES (' Colombian ', 101, 7.99, 0, 0)");

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

Stmt.executeupdate ("INSERT into coffees" +
"VALUES (' French_roast ', 49, 8.99, 0, 0)");

The remaining lines of data are 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)");

Get data from a table
Now that we have the data in the table coffees, we can write a SELECT statement to get these values. The asterisk (*) in the following SQL statement indicates that all columns are selected. Because the selected row is not restricted with a WHERE clause, the following SQL statement selects the entire table.

SELECT * FROM Coffees

The result is the data for 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 a SQL query directly into the database system, you will see the results on your terminal. When we access a database through a Java application, as we will soon do, we need to retrieve the results so that we can use them. You'll see how to do that in the next section.

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

SELECT Cof_name, price from coffees

The result set of the query will have the following form:

Cof_name Price
-------- ---------- -----
Colombian 7.99
French_roast 8.99
Espresso 9.99
COLOMBIAN_DECAF 8.99
FRENCH_ROAST_DECAF 9.99

The SELECT statement above obtains the name and price of all coffee. The following SELECT statement restricts the choice of coffee that is less than $9.00 per pound.
SELECT Cof_name, Price
From coffees
WHERE Price < 9.00

The result set will have the following form:

Cof_name Price
-------- ------- -----
Colombian 7.99
French_roast 8.99
Colombian decaf 8.99