JDBC Connection process

1, loading the database driver Class.forName (sun.jdbc.odbc.JdbcOdbcDriver);

2, establish the database connection Connection conn=drivermanager.getconnection (URL, "username", "pwd"); This URL is more difficult to write. If you are using the Jdbc-odbc Bridge, the JDBC URL will start with Jdbc:odbc: The remaining URLs are usually your data source name or database system. such as Jdbc:odbc:db1.mdb

3, create the JDBC Statement object Statement stmt=conn.  Creatstatement (); Then through the Stmt.executeupdata (), to the database operation ...

The first thing you need to do is to install it correctly. This includes the following steps:

Install Java and JDBC on your computer
Java Database Connectivity (JDBC) is a standard SQL (structured query Language) database provider that provides unified access to a variety of relational databases. JDBC (Java database Connection,java DB connections) also provides a benchmark by which more advanced tools and interfaces can be built. The current JDK (Java Development Kit,java Development toolkit) software bundle includes JDBC and JDBC-ODBC (open DB Connection, open database connection) bridge. These packages can also be obtained independently for use with JDK 1.0. It should be noted that the example in this article uses the JDBC 2.0 interface, which requires JDK 2.0来 to run and not run under JDK 1.1.

You can find the latest version from Http://java.sun.com/products/JDK/CurrentRelease.

Installing drivers
Your driver should have a method of installation. When you install a JDBC driver for a specific DBMSs, just copy it to your computer. No special configuration is required.

If you download a Solaris or Windows JDK1.1 version, the bridge is installed automatically with the JDK as a package sun.jdbc.odbc. For information about installing and configuring ODBC, consult your ODBC driver manufacturer. The bridge needs no special configuration. Consult your database manufacturer for client installation and configuration information.

If necessary, install the database system
If you are not sure whether to install the database system, you need to follow the vendor's requirements to install the database. Most users have already installed databases and can continue to use the databases they have installed.

Configuration database
We assume that the database Coffeebreak already exists. (Creating a database is not difficult, but requires certain permissions and is usually done by the database administrator) you will also need to create this tutorial as an example of the table used in this database. We intend to limit the size and number of the tables to facilitate management.

Suppose our database is used in a café, coffee beans are sold in pounds, and coffee is in cups. For simplicity's sake, it is also assumed that the operator needs only 2 tables to store information about different types of coffee and coffee suppliers.

First we demonstrate how to open a DBMS connection and how JDBC is sending SQL statements to your DBMS. With this code, we will show that using JDBC to pass SQL statements to your DBMS and process the returned results is very simple.

All the code was tested on several major DBMS products. However, if you use the Jdbc-odbc Bridge to connect to an older version of the ODBC driver, you may experience some compatibility issues.

The first thing you need to do is to establish a connection with the DBMS you want to use. This includes 2 steps: Loading the driver and establishing a connection.

Load Driver
Loading a driver requires only a very simple line of code. For example, if you want to use the Jdbc-odbc bridge driver, you can load it with the following code:

Class.forName ("Sun.jdbc.odbc.JdbcOdbcDriver");

Your driver documentation will tell you the class name you should use. For example, if the class name is JDBC. DRIVERXYZ, you will load the driver with the code below:

Class.forName ("jdbc.") Driverxyz ");

You do not need to create an instance of a driver class and register it with DriverManager, because calling Class.forName will automatically load the driver class. If you have created an instance yourself, you will create an unnecessary copy, but it will not do any harm.

After loading the Driver classes, they can be used to establish a connection to the database.

Establish a connection
The second step is to establish a connection with the DBMS with the appropriate driver class. The following code is a general practice:

Connection con = drivermanager.getconnection (URL, "MyLogin", "MyPassword");

This step is also very simple, the most difficult is how to provide the URL. If you are using the Jdbc-odbc Bridge, the JDBC URL will start with Jdbc:odbc: The remaining URLs are usually your data source name or database system. So, suppose you are using ODBC to access an ODBC data source called "Fred", your JDBC URL is jdbc:odbc:Fred. Replace "MyLogin" and "MyPassword" with the username and password of your login DBMS. If you log on to the database system with the username "Fernanda" password "J8", you can establish a connection with just the following 2 lines of code:

String url = "Jdbc:odbc:Fred";
Connection con = drivermanager.getconnection (URL, "Fernanda", "J8");
If you are using a JDBC driver developed by a third party, the document will tell you what subprotocol to use, or the part of the JDBC URL that follows JDBC. For example, if the driver developer registers Acme as Subprotocol, the first and second parts of the JDBC URL will be jdbc:acme. The driver documentation also tells you the format of the remaining JDBC URLs. The last part of the JDBC URL provides information about locating the database.

If you load a driver that identifies the JDBC URL provided to drivermanager.getconnection, the driver will establish a connection to the specified DBMS based on the JDBC URL. As the name shows, the DriverManager class manages all the details of establishing a connection for you behind the scenes. Unless you are writing a driver, you may not need to use any of these other methods, and the only way a programmer needs to use it directly in this class is drivermanager.getconnection.

The Drivermanager.getconnection method returns an open connection that you can use to create JDBC statements and send SQL statements to the database. In the previous example, the Con object is an open connection, and we want to use it in a future example.

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