Example Program: BasicExample1.java

The program BasicExample1.java shown in the following listing is a complete Java program that uses JDBC to access the database tables owned by store . This program and the other programs featured in this chapter may be found in the Java folder where you extracted this book s Zip file. The program performs the following tasks :

1. Imports the JDBC packages

2. Registers the Oracle JDBC drivers

3. Creates Connection and Statement objects

4. Connects to the database as the store database user using the Oracle JDBC Thin driver

5. Adds a new row to the customers table using an INSERT statement

6. Updates customer #1 s first name using an UPDATE statement

7. Deletes customer #5 using a DELETE statement

8. Creates and populates a ResultSet object using a SELECT statement that retrieves the column values for all the rows from the customers table

9. Reads the values from the ResultSet object using the get methods and stores those values for subsequent display

10. Closes that ResultSet object

11. Performs a rollback to undo the changes made to the customers table

12. Creates and populates another ResultSet object with the product_id , product_type_id , and price columns for product #12 (which has a database NULL value in the product_type_id column) retrieved from the products table

13. Reads and displays the column values for product #12 using the get methods, checks the product_type_id column using the wasNull() method, stores the value for the product_type_id column in a java.lang.Integer wrapper object (wrapper objects store database NULL values as Java null values), and uses various numeric variables to retrieve and display the product_id and price column values

14. Closes the ResultSet object

15. Creates a new table named addresses using the SQL DDL CREATE TABLE statement

16. Drops the addresses table using the DROP TABLE statement

17. Closes the Statement and Connection objects in a finally clause

     /*  BasicExample1.java shows how to:  - import the JDBC packages  - load the Oracle JDBC drivers  - connect to a database  - perform DML statements  - control transactions  - use ResultSet objects to retrieve rows  - use the get methods  - perform DDL statements */ // import the JDBC packages import java.sql.*; public class BasicExample1 {  public static void main (String args []) {  // declare Connection and Statement objects   Connection myConnection = null;   Statement myStatement = null;   try {   // register the Oracle JDBC drivers    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());  // EDIT IF NECESSARY  // create a Connection object, and connect to the database    // as store using the Oracle JDBC Thin driver    myConnection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL",   "store",   "store_password");   // disable auto-commit mode    myConnection.setAutoCommit(false);   // create a Statement object    myStatement = myConnection.createStatement();   // create variables and objects used to represent    // column values   int customerId = 6;    String firstName = "Jason";   String lastName = "Red";   java.sql.Date dob = java.sql.Date.valueOf("1969-02-22");   java.sql.Time dobTime;  java.sql.Timestamp dobTimestamp;   String phone = "800-555-1216";   // perform SQL INSERT statement to add a new row to the   // customers table using the values set in the previous   // step - the executeUpdate() method of the Statement   // object is used to perform the INSERT   myStatement.executeUpdate("INSERT INTO customers " +     "(customer_id, first_name, last_name, dob, phone) VALUES (" +      customerId + ", '" + firstName + "', '" + lastName + "', " +    "TO_DATE('" + dob + "', 'YYYY, MM, DD'), '" + phone + "')");    System.out.println("Added row to customers table");  // perform SQL UPDATE statement to modify the first_name   // column of customer #1  firstName = "Jean";   myStatement.executeUpdate("UPDATE customers " +   "SET first_name = '" + firstName + "' " +    "WHERE customer_id = 1");  System.out.println("Updated row in customers table");   // perform SQL DELETE statement to remove customer #5    myStatement.executeUpdate("DELETE FROM customers " +    "WHERE customer_id = 5");    System.out.println("Deleted row from customers table");   // create a ResultSet object, and populate it with the  // result of a SELECT statement that retrieves the   // customer_id, first_name, last_name, dob, and phone columns    // for all the rows from the customers table - the    // executeQuery() method of the Statement object is used   // to perform the SELECT  ResultSet customerResultSet = myStatement.executeQuery("SELECT customer_id, first_name, last_name, dob, phone " +   "FROM customers");   System.out.println("Retrieved rows from customers table");    // loop through the rows in the ResultSet object using the   // next() method, and use the get methods to read the values   // retrieved from the database columns   while (customerResultSet.next()) {    customerId = customerResultSet.getInt("customer_id");   firstName = customerResultSet.getString("first_name");    lastName = customerResultSet.getString("last_name");   dob = customerResultSet.getDate("dob");   dobTime = customerResultSet.getTime("dob");   dobTimestamp = customerResultSet.getTimestamp("dob");    phone = customerResultSet.getString("phone");   System.out.println("customerId = " + customerId);   System.out.println("firstName = " + firstName);   System.out.println("lastName = " + lastName);    System.out.println("dob = " + dob);   System.out.println("dobTime = " + dobTime);   System.out.println("dobTimestamp = " + dobTimestamp);   System.out.println("phone = " + phone);    } // end of while loop  // close the ResultSet object using the close() method    customerResultSet.close();   // rollback the changes made to the database   myConnection.rollback();   // create numeric variables to store the product_id and price columns   short productIdShort;   int productIdInt;    long productIdLong;  float priceFloat;   double priceDouble;   java.math.BigDecimal priceBigDec;  // create another ResultSet object and retrieve the   // product_id, product_type_id, and price columns for product #12   // (this row has a NULL value in the product_type_id column)   ResultSet productResultSet = myStatement.executeQuery("SELECT product_id, product_type_id, price " +    "FROM products " +    "WHERE product_id = 12");   System.out.println("Retrieved row from products table");    while (productResultSet.next()) {   System.out.println("product_id = " +     productResultSet.getInt("product_id"));    System.out.println("product_type_id = " +     productResultSet.getInt("product_type_id"));   // check if the value just read by the get method was NULL    if (productResultSet.wasNull()) {    System.out.println("Last value read was NULL");     }    // use the getObject() method to read the value, and convert it   // to a wrapper object - this converts a database NULL value to a   // Java null value  java.lang.Integer productTypeId =     (java.lang.Integer) productResultSet.getObject("product_type_id");  System.out.println("productTypeId = " + productTypeId);    // retrieve the product_id and price column values into   // the various numeric variables created earlier    productIdShort = productResultSet.getShort("product_id");   productIdInt = productResultSet.getInt("product_id");    productIdLong = productResultSet.getLong("product_id");    priceFloat = productResultSet.getFloat("price");     priceDouble = productResultSet.getDouble("price");    priceBigDec = productResultSet.getBigDecimal("price");    System.out.println("productIdShort = " + productIdShort);   System.out.println("productIdInt = " + productIdInt);    System.out.println("productIdLong = " + productIdLong);     System.out.println("priceFloat = " + priceFloat);    System.out.println("priceDouble = " + priceDouble);   System.out.println("priceBigDec = " + priceBigDec);    } // end of while loop   // close the ResultSet object   productResultSet.close();   // perform a SQL DDL CREATE TABLE statement to create a new table    // that may be used to store customer addresses    myStatement.execute("CREATE TABLE addresses (" +    " address_id INTEGER CONSTRAINT addresses_pk PRIMARY KEY," +    " customer_id INTEGER CONSTRAINT addresses_fk_customers " +    "  REFERENCES customers(customer_id)," +   " street VARCHAR2(20) NOT NULL," +   " city VARCHAR2(20) NOT NULL," +   " state CHAR(2) NOT NULL" +    ")");  System.out.println("Created addresses table");    // drop this table using the SQL DDL DROP TABLE statement    myStatement.execute("DROP TABLE addresses");   System.out.println("Dropped addresses table");  } catch (SQLException e) {    System.out.println("Error code = " + e.getErrorCode());   System.out.println("Error message = " + e.getMessage());  System.out.println("SQL state = " + e.getSQLState());   e.printStackTrace();  } finally {   try {    // close the Statement object using the close() method    if (myStatement != null) {    myStatement.close();    }    // close the Connection object using the close() method   if (myConnection != null) {    myConnection.close();    }    } catch (SQLException e) {    e.printStackTrace();    }   }  } // end of main() } 

    Note  

    You may need to edit the line labeled with the text EDIT IF NECESSARY with the correct settings to access your database. Speak with your DBA if necessary. Also, if your DBA changes the connection information for the database, you ll need to edit and recompile the program before running it.

Compile BasicExample1

To compile BasicExample1.java , type the following command at your operating system command prompt:

 javac BasicExample1.java 

If you haven t set the CLASSPATH environment variable properly, you ll get the following error message when trying to compile the FirstExample.java program:

 FirstExample.java:22: cannot resolve symbol symbol : class OracleDriver location: package jdbc   new oracle.jdbc.OracleDriver()        ^ 1 error 

You should check the setting for your CLASSPATH environment variable ”it s likely your CLASSPATH is missing the Oracle JDBC classes Zip file ( classes12.zip , for example). Refer to the earlier section Setting the CLASSPATH Environment Variable. Alternatively, you can simply include the full directory path to the front of BasicExample1.java where you saved this file. For example, if you saved the file in E:\sql_book\java on Windows, the command would be:

 javac E:\sql_book\java\BasicExample1.java 

You ll also need the operating system write permission to write the .class file into the directory where you perform the compilation, but that won t be a problem if you re using Windows. If you re using Unix or Linux, speak with your system administrator about directory permissions.

Tip  

You can enter javac -help to get help on the Java compiler.

Run BasicExample1

Once BasicExample1.java is compiled, you can run the resulting executable class file named BasicExample1.class by first changing to the directory where that file is stored and then entering the following command:

 java BasicExample1 

Caution  

Java is case-sensitive so make sure you enter BasicExample1 with uppercase B and E characters .

If the program fails with the following error code and message, it means the store user with a password of store_password doesn t exist in your database:

 Error code = 1017 Error message = ORA-01017: invalid username/password; logon denied 

If you get this error, ask your DBA for the correct username and password.

The program may also be unable to find your database, in which case you ll get the following error:

 Error code = 17002 Error message = Io exception: The Network Adapter could not establish  the connection 

Typically, there are two reasons why you might get this error:

• There is no database running on your localhost machine with the Oracle SID of ORCL .

• Oracle Net is not running, or is not listening for connections on port 1521.

You should once again check with the DBA to resolve this issue.

Assuming the program runs you should get the following output:

 Added row to customers table Updated row in customers table Deleted row from customers table Retrieved rows from customers table customerId = 1 firstName = Jean lastName = Brown dob = 1965-01-01 dobTime = 00:00:00 dobTimestamp = 1965-01-01 00:00:00.0 phone = 800-555-1211 customerId = 2 firstName = Cynthia lastName = Green dob = 1968-02-05 dobTime = 00:00:00 dobTimestamp = 1968-02-05 00:00:00.0 phone = 800-555-1212 customerId = 3 firstName = Steve lastName = White dob = 1971-03-16 dobTime = 00:00:00 dobTimestamp = 1971-03-16 00:00:00.0 phone = 800-555-1213 customerId = 4 firstName = Gail lastName = Black dob = null dobTime = null dobTimestamp = null phone = 800-555-1214 customerId = 6 firstName = Jason lastName = Red dob = 1969-02-22 dobTime = 00:00:00 dobTimestamp = 1969-02-22 00:00:00.0 phone = 800-555-1216 Retrieved row from products table product_id = 12 product_type_id = 0 Last value read was NULL productTypeId = null productIdShort = 12 productIdInt = 12 productIdLong = 12 priceFloat = 13.49 priceDouble = 13.49 priceBigDec = 13.49 Created addresses table Dropped addresses table