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:
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:
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 (+).
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:
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.
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:
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:
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 content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
