35.13. Review Questions | Introduction to Java Programming-Comprehensive Version (6th Edition)

[Page 1106 ( continued )]

32.4. JDBC

The Java API for developing Java database applications is called JDBC . JDBC is the trademarked name of a Java API that supports Java programs that access relational databases. JDBC is not an acronym, but it is often thought to stand for Java Database Connectivity.

JDBC provides Java programmers with a uniform interface for accessing and manipulating a wide range of relational databases. Using the JDBC API, applications written in the Java programming language can execute SQL statements, retrieve results, present data in a user -friendly interface, and propagate changes back to the database. The JDBC API can also be used to interact with multiple data sources in a distributed, heterogeneous environment.

The relationships between Java programs, JDBC API, JDBC drivers, and relational databases are shown in Figure 32.18. The JDBC API is a set of Java interfaces and classes used to write Java programs for accessing and manipulating relational databases. Since a JDBC driver serves as the interface to facilitate communications between JDBC and a proprietary database, JDBC drivers are database-specific and are normally provided by the database vendors . You need MySQL JDBC drivers to access the MySQL database, and Oracle JDBC drivers to access the Oracle database. For the Access database, use the JDBC-ODBC bridge driver included in JDK. ODBC is a technology developed by Microsoft for accessing databases on the Windows platform. An ODBC driver is preinstalled on Windows. The JDBC-ODBC bridge driver allows a Java program to access any ODBC data source.

[Page 1107]

Figure 32.18. Java programs access and manipulate databases through JDBC drivers.

(This item is displayed on page 1106 in the print version)

32.4.1. Developing Database Applications Using JDBC

The JDBC API is a Java application program interface to generic SQL databases that enables Java developers to develop DBMS-independent Java applications using a uniform interface.

The JDBC API consists of classes and interfaces for establishing connections with databases, sending SQL statements to databases, processing the results of the SQL statements, and obtaining database metadata. Four key interfaces are needed to develop any database application using Java: Driver , Connection , Statement , and ResultSet . These interfaces define a framework for generic SQL database access. The JDBC API defines these interfaces. The JDBC driver vendors provide implementation for them. Programmers use the interfaces.

The relationship of these interfaces is shown in Figure 32.19. A JDBC application loads an appropriate driver using the Driver interface, connects to the database using the Connection interface, creates and executes SQL statements using the Statement interface, and processes the result using the ResultSet interface if the statements return results. Note that some statements, such as SQL data definition statements and SQL data modification statements, do not return results.

Figure 32.19. JDBC classes enable Java programs to connect to the database, send SQL statements, and process results.

The JDBC interfaces and classes are the building blocks in the development of Java database programs. A typical Java program takes the steps outlined below to access the database.

Loading drivers.

An appropriate driver must be loaded using the statement shown below before connecting to a database.

 Class.forName("JDBCDriverClass");

A driver is a concrete class that implements the java.sql.Driver interface. The drivers for Access, MySQL, and Oracle are listed in Table 32.3.

Table 32.3. JDBC Drivers

(This item is displayed on page 1108 in the print version)

Database	Driver Class	Source
Access	sun.jdbc.odbc.JdbcOdbcDriver	Already in JDK
MySQL	com.mysql.jdbc.Driver	Companion Website
Oracle	oracle.jdbc.driver.OracleDriver	Companion Website

The JDBC-ODBC driver for Access is bundled in JDK. The MySQL JDBC driver is contained in mysqljdbc.jar (downloadable from mysqljdbc.jar ). The Oracle JDBC driver is contained in classes12.jar (downloadable from classes12.jar ). To use the MySQL and Oracle drivers, you have to add mysqljdbc.jar and classes12.jar in the classpath using the following DOS command on Windows:

[Page 1108]

 set classpath=%classpath%;c:\book\mysqljdbc.jar;c:\book\classes12.jar

If your program accesses several different databases, all their respective drivers must be loaded.

Note

com.mysql.jdbc.Driver is a class in mysqljdbc.jar , and oracle.jdbc.driver.OracleDriver is a class in classes12.jar . mysqljdbc.jar and classes12.jar contain many classes to support the driver. These classes are used by JDBC, but not directly by JDBC programmers. When you use a class explicitly in the program, it is automatically loaded by the JVM. The driver classes, however, are not used explicitly in the program, so you have to write the code to tell the JVM to load them.

Establishing connections.

To connect to a database, use the static method getConnection(databaseURL) in the DriverManager class, as follows :

 Connection connection = DriverManager.getConnection(databaseURL);

where databaseURL is the unique identifier of the database on the Internet. Table 32.4 lists the URLs for the MySQL, Oracle, and Access databases.

Table 32.4. JDBC URLs

Database	URL Pattern
Access	jdbc:odbc:dataSource
MySQL	jdbc:mysql://hostname/dbname
Oracle	jdbc:oracle:thin:@hostname:port#:oracleDBSID

For an ODBC data source, the databaseURL is jdbc:odbc:dataSource . An ODBC data source can be created using the ODBC Data Source Administrator on Windows. See Supplement IV.D, "Tutorial for Microsoft Access," on how to create an ODBC data source for an Access database. Suppose a data source named ExampleMDBDataSource has been created for an Access database. The following statement creates a Connection object:

 Connection connection = DriverManager.getConnection (   "jdbc:odbc:ExampleMDBDataSource"   );

The databaseURL for a MySQL database specifies the host name and database name to locate a database. For example, the following statement creates a Connection object for the local MySQL database test:

 Connection connection = DriverManager.getConnection (   "jdbc:mysql://localhost/test"   );

[Page 1109] Recall that by default MySQL contains two databases named mysql and test . You can create a custom database using the MySQL SQL command create database databasename .

The databaseURL for an Oracle database specifies the hostname , the port# where the database listens for incoming connection requests , and the oracleDBSID database name to locate a database. For example, the following statement creates a Connection object for the Oracle database on liang.armstrong.edu with username scott and password tiger:

 Connection connection = DriverManager.getConnection (   "jdbc:oracle:thin:@liang.armstrong.edu:1521:orcl"   ,   "scott"   ,   "tiger"   );

Creating statements.

If a Connection object can be envisioned as a cable linking your program to a database, an object of Statement or its subclass can be viewed as a cart that delivers SQL statements for execution by the database and brings the result back to the program. Once a Connection object is created, you can create statements for executing SQL statements as follows:

 Statement statement = connection.createStatement();

Executing statements.

An SQL DDL or update statement can be executed using executeUpdate(String sql) , and an SQL query statement can be executed using executeQuery(String sql) . The result of the query is returned in ResultSet . For example, the following code executes the SQL statement create table Temp (col1 char(5), col2 char(5)) :

 statement.executeUpdate (   "create table Temp (col1 char(5), col2 char(5))"   );

The next code executes the SQL query select firstName, mi, lastName from Student where lastName = 'Smith' :

  // Select the columns from the Student table  ResultSet resultSet = statement.executeQuery (   "select firstName, mi, lastName from Student where lastName "   +   " = 'Smith'"   );

Processing ResultSet .

The ResultSet maintains a table whose current row can be retrieved. The initial row position is null . You can use the next method to move to the next row and the various get methods to retrieve values from a current row. For example, the code given below displays all the results from the preceding SQL query.

  // Iterate through the result and print the student names    while   (resultSet.next()) System.out.println(resultSet.getString(   1   ) +   " "   + resultSet.getString(  2  ) +   ". "   + resultSet.getString(   3   ));

The getString(1) , getString(2) , and getString(3) methods retrieve the column values for firstName , mi , and lastName , respectively. Alternatively, you can use getString("firstName") , getString("mi") , and getString("lastName") to retrieve the same three column values. The first execution of the next() method sets the current row to the first row in the result set, and subsequent invocations of the next() method set the current row to the second row, third row, and so on, to the last row.

[Page 1110]

Listing 32.1 is a complete example that demonstrates connecting to a database, executing a simple query, and processing the query result with JDBC. The program connects to a local MySQL database and displays the students whose last name is Smith.

Listing 32.1. SimpleJDBC.java

 1   import   java.sql.*; 2 3   public class   SimpleJdbc { 4   public static void   main(String[] args) 5   throws   SQLException, ClassNotFoundException { 6  // Load the JDBC driver  7    Class.forName   (   "com.mysql.jdbc.Driver"   );  8 System.out.println(   "Driver loaded"   ); 9 10  // Establish a connection  11    Connection connection = DriverManager.getConnection    12    ("jdbc:mysql://localhost/test");    13 System.out.println(   "Database connected"   ); 14 15  // Create a statement  16  Statement statement = connection.createStatement();  17 18  // Execute a statement  19  ResultSet resultSet = statement.executeQuery  20  (   "select firstName, mi, lastName from Student where lastName "    21  +   " = 'Smith'"   );  22 23  // Iterate through the result and print the student names  24   while   (  resultSet.next()  ) 25 System.out.println(  resultSet.getString(   1   )  +   "\t"   + 26  resultSet.getString(   2   )  +   "\t"   +  resultSet.getString(   3   )  ); 27 28  // Close the connection  29  connection.close();  30 } 31 }

The statement in line 7 loads a JDBC driver for MySQL, and the statement in lines 11 “12 connects to a local MySQL database. You may change them to connect to an Access or Oracle database. The last statement (line 29) closes the connection and releases resource related to the connection.

Note

Do not use a semicolon (;) to end the Oracle SQL command in a Java program. The semicolon does not work with the Oracle JDBC drivers. It does work, however, with the other drivers used in the book.

Note

The Connection interface handles transactions and specifies how they are processed . By default, a new connection is in auto-commit mode, and all its SQL statements are executed and committed as individual transactions. The commit occurs when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a result set, the statement completes when the last row of the result set has been retrieved or the result set has been closed. If a single statement returns multiple results, the commit occurs when all the results have been retrieved. You can use the setAutoCommit(false) method to disable auto-commit, so that all SQL statements are grouped into one transaction that is terminated by a call to either the commit() or the rollback() method. The rollback() method undoes all the changes made by the transaction.

[Page 1111]

32.4.2. Accessing a Database from a Java Applet

Using the JDBC-ODBC bridge driver, your program cannot run as an applet from a Web browser because the ODBC driver contains non-Java native code. The JDBC drivers for MySQL and Oracle are written in Java and can run from the JVM in a Web browser. This section gives an example that demonstrates connecting to a database from a Java applet. The applet lets the user enter the SSN and the course ID to find a student's grade, as shown in Figure 32.20. The code in Listing 32.2 uses the MySQL database on the host liang.armstrong.edu.

Figure 32.20. A Java applet can access the database on the server.

Listing 32.2. FindGrade.java

(This item is displayed on pages 1111 - 1112 in the print version)

 1   import   javax.swing.*; 2   import   java.sql.*; 3   import   java.awt.*; 4   import   java.awt.event.*; 5 6   public class   FindGrade   extends   JApplet { 7   private   JTextField jtfSSN =   new   JTextField(   9   ); 8   private   JTextField jtfCourseId =   new   JTextField(   5   ); 9   private   JButton jbtShowGrade =   new   JButton(   "Show Grade"   ); 10 11  // Statement for executing queries  12   private    Statement stmt;  13 14  /** Initialize the applet */  15   public void   init() { 16  // Initialize database connection and create a Statement object  17 initializeDB(); 18 19 jbtShowGrade.addActionListener( 20   new   java.awt.event.ActionListener() { 21   public void   actionPerformed(ActionEvent e) { 22 jbtShowGrade_actionPerformed(e); 23 } 24 }); 25 26 JPanel jPanel1 =   new   JPanel(); 27 jPanel1.add(   new   JLabel(   "SSN"   )); 28 jPanel1.add(jtfSSN); 29 jPanel1.add(   new   JLabel(   "Course ID"   )); 30 jPanel1.add(jtfCourseId); 31 jPanel1.add(jbtShowGrade); 32

[Page 1112]

 33 add(jPanel1, BorderLayout.NORTH); 34 } 35 36   private void   initializeDB() { 37   try   { 38  // Load the JDBC driver  39  Class.forName(   "com.mysql.jdbc.Driver"   );  40  // Class.forName("oracle.jdbc.driver.OracleDriver");  41 System.out.println(   "Driver loaded"   ); 42 43  // Establish a connection  44  Connection connection = DriverManager.getConnection  45  (   "jdbc:mysql://liang.armstrong.edu/test"   );  46  // ("jdbc:oracle:thin:@liang.armstrong.edu: 1521:orcl"  , 47  // "scott", "tiger");  48 System.out.println(   "Database connected"   ); 49 50  // Create a statement  51  stmt = connection.createStatement();  52 } 53   catch   (Exception ex) { 54 ex.printStackTrace(); 55 } 56 } 57 58   private void   jbtShowGrade_actionPerformed(ActionEvent e) { 59 String ssn = jtfSSN.getText(); 60 String courseId = jtfCourseId.getText(); 61   try   { 62 String queryString =   "select firstName, mi, "   + 63   "lastName, title, grade from Student, Enrollment, Course "   + 64   "where Student.ssn = '"   + ssn +   "' and Enrollment.courseId "   65 +   "= '"   + courseId + 66   "' and Enrollment.courseId = Course.courseId "   + 67   " and Enrollment.ssn = Student.ssn"   ; 68 69  ResultSet rset = stmt.executeQuery(queryString);  70 71   if   (  rset.next()  ) { 72 String lastName =  rset.getString(   1   )  ; 73 String mi =  rset.getString(   2   )  ; 74 String firstName =  rset.getString(   3   )  ; 75 String title =  rset.getString(   4   )  ; 76 String grade =  rset.getString(   5   )  ; 77 78  // Display result in a dialog box  79 JOptionPane.showMessageDialog(   null   , firstName +   " "   + mi + 80   " "   + lastName +   "'s grade on course "   + title +   " is "   + 81 grade); 82 }   else   { 83  // Display result in a dialog box  84 JOptionPane.showMessageDialog(   null   ,   "Not found"   ); 85 } 86 } 87   catch   (SQLException ex) { 88 ex.printStackTrace(); 89 } 90 } 91 }

[Page 1113]

The initializeDB() method (lines 36 “56) loads the MySQL driver (line 39), connects to the MySQL database on host liang.armstrong.edu (lines 44 “45), and creates a statement (line 51).

You can run the applet standalone from the main method (note that the listing for the main method is omitted for all the applets in the book for brevity) or test the applet using the appletviewer utility, as shown in Figure 32.20. If this applet is deployed on the server where the database is located, any client on the Internet can run it from a Web browser. Since the client may not have a MySQL driver, you should make the driver available along with the applet in one archive file. This archive file can be created as follows:

Copy c:\book\mysqljdbc.jar to a new file named FindGrade.zip.
Add FindGrade.class into FindGrade.zip using the WinZip utility.
Add FindGrade$1.class into FindGrade.zip using the WinZip utility. FindGrade$1.class is for the anonymous inner event adapter class for listening to the button action.

You need to deploy FindGrade.zip and FindGrade.html on the server. FindGrade.html should use the applet tag with a reference to the Zip file, as follows:

   <applet     code   =   "FindGrade"     archive   =   "FindGrade.zip"     width   =   380     height   =   80   >   </applet>

Note

To access the database from an applet, security restrictions make it necessary for the applet to be downloaded from the server where the database is located. Therefore, you have to deploy the applet on the server.