Using JDBC in JSP(1)

 

我上课讲稿,大部分资料来源sun公司网站上的学习资料及Java Document.

Ⅰ. What is JDBC

1.         What is JDBC?

    JDBC(Java DataBase Connectivity) is Java API for executing SQL statements. It consists of a set of classes and interfaces written in the Java programming language.

2.         What does JDBC do?

JDBC makes it possible to do three things:

1)        establish a connection with a database

2)        send SQL statements

3)        process the results. 

3.         JDBC versus ODBC

Why do you need JDBC? There are servel anserws to this question:

1)        ODBC uses a C interface. Calls from Java to native C code have a number of drawbacks in the security, implementation, robustness, and automatic portability of applications.

2)        Java has no pointers, and ODBC makes copious use of them.

3)        ODBC is hard to learn. It mixes simple and advanced features together, and it has complex options even for simple queries. JDBC, on the other hand, was designed to keep simple things simple while allowing more advanced capabilities where required.

4)       A Java API like JDBC is needed in order to enable a ``pure Java'' solution. When ODBC is used, the ODBC driver manager and drivers must be manually installed on every client machine. When the JDBC driver is written completely in Java, however, JDBC code is automatically installable, portable, and secure on all Java platforms from network computers to mainframes.

Ⅱ.Accessing DBMS by JDBC

1.         establishing a connection with DBMS

1.1.        Typically, a JDBC application connects to a target data source using one of two mechanisms:

l         DriverManager: This fully implemented class requires an application to load a specific driver, using a hardcoded URL. As part of its initialization, the DriverManager class attempts to load the driver classes referenced in the jdbc.drivers system property. This allows you to customize the JDBC Drivers used by your applications.

l         DataSource: This interface is preferred over DriverManager because it allows details about the underlying data source to be transparent to your application. A DataSource object's properties are set so that it represents a particular data source.

1.2.        Establishing a connection with DBMS by DriverManager

Establishing a connection involves two steps: Loading the driver, and making the connection.

Step 1: Loading the driver

       The JDBC drivers that we are aware of at this time generally fit into one of two categories:

l         JDBC-ODBC bridge plus ODBC driver: The JavaSoft bridge product provides JDBC access via ODBC drivers. Note that ODBC binary code, and in many cases database client code, must be loaded on each client machine that uses this driver. As a result, this kind of driver is most appropriate on a corporate network where client installations are not a major problem, or for application server code written in Java in a three-tier architecture.

l         Native-protocol pure Java driver: This kind of driver converts JDBC calls into the network protocol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is an excellent solution for intranet access. Since many of these protocols are proprietary, the database vendors themselves will be the primary source. Several database vendors have these in progress.

For normal use, you should obtain a commercial JDBC driver from a vendor such as your database vendor or your database middleware vendor. The JDBC-ODBC Bridge driver provided with JDBC is recommended only for development and testing, or when no other alternative is available.

Loading the driver you want to use is very simple. It involves just one line of code in your program. Calling the Class.forName automatically creates an instance of a driver and registers it with the DriverManager. For example:

//To use the JDBC-ODBC driver

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

//To use the Java DB driver, add the following line of code:

Class.forName(“org.apache.derby.jdbc.EmbeddedDriver”);

//To use the SQL Server driver

Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver”);

//To use the Oracle driver

Class.forName(“oracle.jdbc.driver.OracleDriver”);

//To use the MySql driver

Class.forName(“com.mysql.jdbc.Driver”);

After you have loaded a driver, it can make a connection with a DBMS.

Step 2: making the connection

In order to establish a connection with a DBMS, you can write codes as:

Connection con = java.sql.DriverManager.getConnection(dbURL,user,password);

Where, drivertype express the type of drever, “driversubtype” is a optional parameter and parameters are set the IP dress, port and the database name.

The DriverManager class works with the Driver interface to manage the set of drivers available to a JDBC client. When the client requests a connection and provides a URL, the DriverManager is responsible for finding a driver that recognizes the URL and for using it to connect to the corresponding data source. Connection URLs have the following form:

jdbc:drivertype:driversubtype://parameters

examples:

A. to connect with Oracle: jdbc:oracle:thin:@localhost:1521:dbname

       B. to connect with Mysql: jdbc:mysql://localhost:3306/dbname

       C. to connect with SQLServer: jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=dbn

       D. to connect with ODBC: jdbc:odbc:datasource

2.         Sending SQL statement

Statement stmt = con.creatStatement();

String sql = “SELECT NAME, PRICE FROM COFFEES”;

ResultSet rs = stmt.executeQuery(sql);

3.         Process the results

The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics.

3.1.        Using the next() methods

When a ResultSet object is first created, the cursor is positioned before the first row. To move the cursor forward one row, you can use the method next().

next() - moves the cursor forward one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.

3.2.        Using the getXXXMethods

The ResultSet interface declares getter methods (getBoolean, getLong, and so on) for retrieving column values from the current row. Your application can retrieve values using either the index number of the column or the name of the column. The column index is usually more efficient. Columns are numbered from 1. For maximum portability, result set columns within each row should be read in left-to-right order, and each column should be read only once.

Column names used as input to getter methods are case insensitive. When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

The getXXX method of the appropriate type retrieves the value in each column. For example, the first column in each row of srs is COF_NAME, which stores a value of SQL type VARCHAR. The method for retrieving a value of SQL type VARCHAR is getString. The second column in each row stores a value of SQL type FLOAT, and the method for retrieving values of that type is getFloat. The following code accesses the values stored in the current row of srs and prints a line with the name followed by three spaces and the price. Each time the method next is invoked, the next row becomes the current row, and the loop continues until there are no more rows in rs.

The method getString is invoked on the ResultSet object srs, so getString retrieves (gets) the value stored in the column COF_NAME in the current row of srs . The value that getString retrieves has been converted from an SQL VARCHAR to a String in the Java programming language, and it is assigned to the String object s.

Note that although the method getString is recommended for retrieving the SQL types CHAR and VARCHAR, it is possible to retrieve any of the basic SQL types with it. (You cannot, however, retrieve the new SQL3 datatypes with it. We will discuss SQL3 types later in this tutorial.) Getting all values with getString can be very useful, but it also has its limitations. For instance, if it is used to retrieve a numeric type, getString converts the numeric value to a Java String object, and the value has to be converted back to a numeric type before it can be operated on as a number. In cases where the value is treated as a string anyway, there is no drawback. Further, if you want an application to retrieve values of any standard SQL type other than SQL3 types, use the getString method.

ResultSet srs = stmt.executeQuery( "SELECT NAME, PRICE FROM COFFEES");

while (srs.next()) {

        String name = srs.getString("NAME");

        float price = srs.getFloat("PRICE");

        System.out.println(name + "     " + price);

}

 

JDBC offers two ways to identify the column from which a getXXX method gets a value. One way is to give the column name, as was done in the example above. The second way is to give the column index (number of the column), with 1 signifying the first column, 2 , the second, and so on. Using the column number instead of the column name looks like this:

String s = srs.getString(1);

float n = srs.getFloat(2);

Ⅲ. Demos

Here are two examples of using JDBC in JSP.

1.       Example 7-1: showByJdbcOdbc.jsp

2.       Example 7-2: showBySQLDriver.jsp

Workshop 1: To rewite the second example by using javabean.

Ⅳ. Manipulating DataBase

1.         Quering

In the previous lesson, ResultSet was briefly mentioned. Now, we will learn the details of the ResultSet interface. The ResultSet interface provides methods for retrieving and manipulating the results of executed queries, and ResultSet objects can have different functionality and characteristics. These characteristics are result set type, result set concurrency, and cursor holdability. A table of data representing a database result set is usually generated by executing a statement that queries the database.

The type of a ResultSet object determines the level of its functionality in two areas: the ways in which the cursor can be manipulated, and how concurrent changes made to the underlying data source are reflected by the ResultSet object.

The sensitivity of the ResultSet object is determined by one of three different ResultSet types:

l         TYPE_FORWARD_ONLY — The result set is not scrollable; its cursor moves forward only, from before the first row to after the last row. The rows contained in the result set depend on how the underlying database materializes the results. That is, it contains the rows that satisfy the query at either the time the query is executed or as the rows are retrieved.

l         TYPE_SCROLL_INSENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

l         TYPE_SCROLL_SENSITIVE — The result set is scrollable; its cursor can move both forward and backward relative to the current position, and it can move to an absolute position.

Now, you'll see how to send the above SELECT statements from a program written in the Java programming language and how you get the results we showed.

JDBC returns results in a ResultSet object, so we need to declare an instance of the class ResultSet to hold our results. In addition, the Statement methods executeQuery and getResultSet both return a ResultSet object, as do various DatabaseMetaData methods. The following code demonstrates declaring the ResultSet object rs and assigning the results of our earlier query to it by using the executeQuery method.

Before you can take advantage of these features, however, you need to create a scrollable ResultSet object. The following line of code illustrates one way to create a scrollable ResultSet object:

Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,

                                     ResultSet.CONCUR_READ_ONLY);

ResultSet srs = stmt.executeQuery("SELECT COF_NAME, PRICE FROM COFFEES");

This code is similar to what you have used earlier, except that it adds two arguments to the createStatement method. The first argument is one of three constants added to the ResultSet API to indicate the type of a ResultSet object: TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE, and TYPE_SCROLL_SENSITIVE. The second argument is one of two ResultSet constants for specifying whether a result set is read-only or updatable: CONCUR_READ_ONLY and CONCUR_UPDATABLE . The point to remember here is that if you specify a type, you must also specify whether it is read-only or updatable. Also, you must specify the type first, and because both parameters are of type int, the compiler will not complain if you switch the order.

Specifying the constant TYPE_FORWARD_ONLY creates a nonscrollable result set, that is, one in which the cursor moves only forward. If you do not specify any constants for the type and updatability of a ResultSet object, you will automatically get one that is TYPE_FORWARD_ONLY and CONCUR_READ_ONLY.

To move the cursor, you can use the following methods:

l         previous() - moves the cursor backwards one row. Returns true if the cursor is now positioned on a row and false if the cursor is positioned before the first row.

l         first() - moves the cursor to the first row in the ResultSet object. Returns true if the cursor is now positioned on the first row and false if the ResultSet object does not contain any rows.

l         last() - moves the cursor to the last row in the ResultSet object. Returns true if the cursor is now positioned on the last row and false if the ResultSet object does not contain any rows.

l         beforeFirst() - positions the cursor at the start of the ResultSet object, before the first row. If the ResultSet object does not contain any rows, this method has no effect.

l         afterLast() - positions the cursor at the end of the ResultSet object, after the last row. If the ResultSet object does not contain any rows, this method has no effect.

l         relative(int rows) - moves the cursor relative to its current position.

l         absolute(int row) - positions the cursor on the row-th row of the ResultSet object.

Example 3: scrolledResultSet.jsp

2.         Updating, adding and deleting

The method executeUpdate( string sql) is involved to executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement or an SQL statement that returns nothing, such as an SQL DDL statement.

Example 4: updateRecord.jsp

Ⅴ. Summary

In this lesson, we had learnt how to connect with DBMS DriverManger and  manipulated the database thru JDBC.

Ⅵ. Extension of lesson

Workshop 2: Which troubles does these examples exists? What is your improved approaches?

Homework: To collect the materials of establishing connection with database thru DataSource.