Getting started with JDBC

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 connection (JDBC) is a standard SQL (Structured Query Language, Structured Query Language) database access interface that provides unified access to a variety of relational databases. JDBC (Java DataBase Connection) also provides a benchmark to build more advanced tools and interfaces. The current JDK (Java Development Kit, Java Development Kit) software bundle includes JDBC and JDBC-ODBC (Open DataBase Connection, Open DataBase Connection) Bridge. These packages can also be obtained independently and used together 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 cannot run under JDK 1.1.

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

Install the driver
Your driver should have the installation method. When installing a JDBC Driver written for a specific DBMSs, you only need to copy it to your computer. No special configuration is required.

If you download Solaris or Windows JDK1.1, the bridge is automatically installed together with JDK as the sun. jdbc. odbc package. For information on installing and configuring ODBC, consult the ODBC driver vendor. No special configuration is required for the bridge. For information about Client installation and configuration, consult the database vendor.

If necessary, install the Database System
If you cannot confirm whether the database system is installed, you need to install the database according to the supplier's requirements. Most users have already installed databases and can continue to use the databases they have installed.

Configure Database
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 also need to create a table in this database as an example. We intentionally limit the table size and number to facilitate management.

Assume that our database is used in a coffee shop. The coffee beans are sold by pound, while the coffee is made by cup. For simplicity, It is also assumed that the operator only needs two tables to store information about different types of coffee and coffee suppliers.

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

All the code is tested in several major DBMS products. However, if you use the JDBC-ODBC bridge to connect to the old ODBC driver, you may encounter some compatibility issues

The first thing you need to do is to establish a connection with the DBMS you want to use. This involves two steps: loading the driver and establishing a connection.

Load driver
Loading the driver requires only a very simple line of code. For example, 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 document will tell you the class name you should use. For example, if the class name is jdbc. DriverXYZ, you will use the following code to load the driver:

　　　　 Class.forName("jdbc.DriverXYZ");

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

After the Driver class is loaded, they can be used to establish a connection with the database.

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

　　　　 Connection con = DriverManager.getConnection(url, "myLogin", "myPassword");

This step is also very simple, and the most difficult is how to provide a url. If you are using a JDBC-ODBC bridge, the jdbc url will start with jdbc: odbc: The remaining URL is typically your data source name or database system. Therefore, if you are using ODBC to access an ODBC data source named "Fred", your jdbc url is jdbc: odbc: Fred. Replace "myLogin" and "myPassword" with your username and password for logging on to the DBMS. If the username you log on to the database system is "Fernanda" and the password is "J8", you only need the following two lines of code to establish a connection:

String url = "jdbc:odbc:Fred";
Connection con = DriverManager.getConnection(url,"Fernanda", "J8");
If you are using a third-party JDBC driver, the document will tell you what subprotocol to use, that is, the part that is placed behind JDBC in the jdbc URL. For example, if the driver developer registers acme as subprotocol, the first and second parts of jdbc url are jdbc: acme. The driver documentation also tells you the format of the remaining jdbc url. The last part of the jdbc url provides information for locating the database.

If the driver you load recognizes the jdbc url provided to DriverManager. getConnection, the driver will establish a connection to the specified DBMS based on the jdbc url. As shown in the name, the DriverManager class is behind the scenes to manage all the details of establishing a connection for you. Unless you are writing a driver, you may not need to use any other method of this class. The only method that programmers need to use directly in this class is DriverManager. getConnection.

The DriverManager. getConnection method returns an open connection. You can use this connection to create JDBC statements and send SQL statements to the database. In the previous example, the con object is an open connection, and we will use it in future examples.

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)