Introduction to Java Programming-Comprehensive Version (6th Edition)

1.

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

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:

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

2.

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

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" );

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" );

3.

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();

4.

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'" );

5.

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.

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 }

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.

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.

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

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 }

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.