CS276 Advanced Oracle Using Java

1. CS276 Advanced Oracle Using Java Chapter 3 The Basics of JDBC Programming

2. The Oracle JDBC Drivers • Thin driver • OCI driver • Server-side internal driver • Server-side Thin driver

3. Importing the JDBC Packages • There are two sets of JDBC packages: • The standard JDBC packages from Sun Microsystems • Enable your Java programs to access the basic features of most databases, including • Oracle • SQL Server • DB2 • MySQL • Access • Oracle’s extension packages • Enable your programs to access all of the Oracle specific features, as well as the Oracle specific performance

4. Importing the JDBC Packages • To use JDBC packages in your programs you should: • import java.sql.*;

5. Registering the Oracle JDBC Drivers • There are two ways to register the Oracle JDBC drivers • The First is to use the forName( ) method of the class java.lang.Class Class.forName(“oracle.jdbc.OracleDriver”); • The Second way is to use the registerDriver( ) of the java.sql.DriverManager class DriverManager.registerDriver( new oracle.jdbc.OracleDriver( ));

6. Opening a Database Connection • Connecting to the Database Using the getConnection() Method of the DriverManager Class DriverManager.getConnection(URL, username, password);

7. Opening a Database ConnectionConnecting to the Database Using the getConnection() Method of the DriverManager Class • The Database URL • driver_name:@driver_information • driver_name: • jdbc:oracle:thin • jdbc:oracle:oci • jdbc:oracle:oci8 • jdbc:oracle:oci7

8. Opening a Database ConnectionConnecting to the Database Using the getConnection() Method of the DriverManager Class • driver_information • host_name:port:database_SID for Oracle JDBC Thin driver • For all the Oracle drivers may also use Oracle Net keyword_value pairs: (description=(address=(host=host_name)(protocol=tcp) (port=port))(connect_data=(sid=database_SID)))

9. Opening a Database ConnectionConnecting to the Database Using the getConnection() Method of the DriverManager Class • host_name: the name of the machine on which the database ia running • port: The port number on which the Oracle Net database listener waits for requests; 1521 is the default port number. • database_SID for Oracle JDBC Thin driver

10. Opening a Database ConnectionConnecting to the Database Using the getConnection() Method of the DriverManager Class • For example: Connection myConnection = DriverManager.getConnection(“jdbc:oracle:oci:@ (description=(address=(host=localhost)” + “(protocol=tcp)(port=1521))(connect_data=(sid=ORCL)))”, “scott”, “tiger”

11. Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source • There are three steps that must performed to use an Oracle Data Source: • Create an Oracle data source object of the oracle.jdbc.pool.OracleDataSource class • Set the Oracle data source object attributes using set methods • Connect to the database via the Oracle data source object using getConnection() method

12. Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source • Step 1 Create an Oracle data source object of the oracle.jdbc.pool.OracleDataSource class OracleDataSource myDataSource=new OracleDataSource();

13. Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source • Step 2 Set the Oracle data source object attributes using set methods The oracle.jdbc.pool.OracleDataSource class actually implements interface provided with JDBC. The javax.sql.DataSource interface defines a number of attributes, which are listed in Table 3-1, page 93 The oracle.jdbc.pool.OracleDataSource class provides an additional set of attributes( See Table 3-2, page94)

14. Oracle Data Source Attributes ( Table 3-1, page 93)

15. Oracle Data Source Attributes( Table 3-1, page 93)

16. oracle.jdbc.pool.OracleDataSource class additional set of attributes( See Table 3-2, page94)

17. Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source Examples using set methods: myDataSource.setServerName(“localhost”); myDataSource.setDatabaseName(“ORCL”); myDataSource.setDriverType(“Thin”); myDataSource.setNetworkProtocol(“tcp”); myDataSource.setPortNumber(1521); myDataSource.setUser(“scott”); myDataSource.setPassword(“tiger”);

18. Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source Examples using get methods: String serverName = myDataSource.getServerName(); String databaseName= myDataSource.getDatabaseName(); String driverType = myDataSource.getDriverType(); String networkProtocol = myDataSource.getNetworkProtocol(); int portNumber = myDataSource.getPortNumber();

19. Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source • Step 3 Connect to the database via the Oracle data source object using getConnection() method Connection myConnection = myDataSource.getConnection(); You can pass a username and password as parameters: Connection myConnection = myDataSource.getConnection(“store_user”, “store_password”); In this case, the username and password will override scott and tiger, previously set in myDataSource

20. Opening a Database Connection // declare Connection and Statement objects Connection myConnection = null; Statement myStatement = null; try { // register the Oracle JDBC drivers DriverManager.registerDriver( new oracle.jdbc.OracleDriver() ); // create a Connection object, and connect to the database // as store_user using the Oracle JDBC Thin driver myConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:ORCL", "store_user", "store_password" );

21. Opening a Database Connection // declare Connection and Statement objects Connection myConnection = null; Statement myStatement = null; try { // register the Oracle JDBC drivers DriverManager.registerDriver( new oracle.jdbc.OracleDriver() );

22. Opening a Database Connection // create a Connection object, and connect to the database // as store_user using the Oracle JDBC Thin driver myConnection = DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:ORCL", "store_user", "store_password" );

23. Perform DML statements // create a Statement object myStatement = myConnection.createStatement(); // create variables and objects used to represent // column values int id = 6; String firstName = "Jason"; String lastName = "Red"; java.sql.Date dob = new java.sql.Date(69, 1, 1); java.sql.Time dobTime; java.sql.Timestamp dobTimestamp; String phone = "800-555-1216";

24. Perform DML statements // 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(

25. Perform DML statements "INSERT INTO customers " + "(id, first_name, last_name, dob, phone) VALUES (" + id + ", '" + firstName + "', '" + lastName + "', " + "TO_DATE('" + dob + "', 'YYYY, MM, DD'), '" + phone + "')" ); System.out.println("Added row to customers table");

26. Perform DML statements // perform SQL UPDATE statement to modify the first_name // column of customer #1 firstName = "Jean"; myStatement.executeUpdate( "UPDATE customers " + "SET first_name = '" + firstName + "' " + "WHERE id = 1" );

27. Perform DML statements System.out.println("Updated row in customers table"); // perform SQL DELETE statement to remove customer #5 myStatement.executeUpdate( "DELETE FROM customers " + "WHERE id = 5" ); System.out.println("Deleted row row from customers table");

28. Control Transactions // disable auto-commit mode myConnection.setAutoCommit(false); // commit any chages myConnection.commit(); // rollback the changes made to the database myConnection.rollback();

29. Retrieving Rows from the DataBase Because a SELECT statement may return more than one row, executeQuery() returns an object that stores the rows - ResultSet object There are Three steps to read rows from the database: • Step 1 Create a ResultSet object, and populate it using a SELECT statement • Step 2 Read the column values from the ResultSet object using get methods • Step 3 Close the ResultSet object

30. Use ResultSet objects to retrieve rowsStep 1: Create and Populate a ResultSet object // create a ResultSet object, and populate it with the // result of a SELECT statement that retrieves the // 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

31. Use ResultSet objects to retrieve rowsStep 1: Create and Populate a ResultSet object ResultSet customerResultSet = myStatement.executeQuery( "SELECT id, first_name, last_name, dob, phone " + "FROM customers" ); System.out.println("Retrieved rows from customers table");

32. Use ResultSet objects to retrieve rowsStep 2: Read the Column Values from the ResultSet object • To read the column values for the rows stored in a ResultSet object, the ResultSet class provides a series of get methods. • Before, you need to understand how the data types used to represent values in Oracle may be mapped to compatible Java data types

33. Oracle and Java Types

34. Oracle and Java Types • From this table, you can see that an Oracle INTEGER is compatible with a Java int. • So, the id column (INTEGER) of the customers table may be stored in a Java int . • Similarly, the first_name, last_name, and phone column values may be stored in Java String variables

35. Oracle and Java Types • The Oracle DATE type stores a year, month, day, hour, minute, and second. • You may use: • java.sql.Date to store the date part of the dob column value • java.sql.Time to store the time part • java.sql.Timestamp to store both the date and the time parts

36. Oracle and Java Types • Examples how to declare Java variables: • int id = 0; • String firstName = null; • String lastname = null; • java.sql.Date dob = null; • String phone = null;

37. Oracle and Java Types • The int and String types are part of the core Java language • java,.sql.Date ia part of JDBC • However, JDBC doesn’t cover all types used by Oracle: • You must use oracle.sql.ROWID type to store Oracle ROWID • So, Oracle provides a number of additional types in oracle.sql package ( later in this chapter)

38. Use the get methods to Read Column Values // 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()) { id = customerResultSet.getInt("id"); firstName = customerResultSet.getString("first_name");

39. Use the get methods to Read Column Values lastName = customerResultSet.getString("last_name"); dob = customerResultSet.getDate("dob"); dobTime = customerResultSet.getTime("dob"); dobTimestamp = customerResultSet.getTimestamp("dob"); phone = customerResultSet.getString("phone");

40. Use the get methods to Read Column Values System.out.println("id = " + id); 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 this ResultSet object using the close() method customerResultSet.close();

41. Perform DDL statements // 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 (" + " id INTEGER CONSTRAINT addresses_pk PRIMARY KEY," + " customer_id INTEGER CONSTRAINT addresses_fk_customers " +

42. Perform DDL statements " REFERENCES customers(id)," + " street VARCHAR2(20) NOT NULL," + " city VARCHAR2(20) NOT NULL," + " state CHAR(2) NOT NULL" + ")" );

43. Closing Your JDBC Objects myStatement.close(); myConnection.close();

44. Example Program:BasicExample1.java The program performs the following tasks: 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

45. How to use prepared SQL statements // create a PreparedStatement object PreparedStatement myPrepStatement = myConnection.prepareStatement( "INSERT INTO products " + "(id, type_id, name, description, price) VALUES (" + "?, ?, ?, ?, ?" + ")" );

46. How to use prepared SQL statements There are two things: • The preparedStatement() method is used to specify the SQL statement • Qustion mark characters (?) are used to indicate the positions where you will later provide to be used when the SQL statement is actually run

47. How to use prepared SQL statements The following example features a loop that shows the use of set methods to bind the attributes of the Product objects in productArray to the PreparedStatement object. The execute metod is used to run the SQL statement

48. How to use prepared SQL statements for (int counter = 0; counter < product_array.length; counter ++) { myPrepStatement.setInt(1, product_array[counter].id); myPrepStatement.setInt(2, product_array[counter].typeId); myPrepStatement.setString(3, product_array[counter].name); myPrepStatement.setString(4, product_array[counter].description); myPrepStatement.setDouble(5, product_array[counter].price); myPrepStatement.execute(); } // end of for loop

49. How to use prepared SQL statements The program BasicExample2.java contains the statements shown in this section

50. The Oracle JDBC Extensions • oracle.sql Contains the classes that support all Oracle types • import oracle.sql; • oracle.jdbc Contains the interfaces that support access to an Oracle database • import oracle.jdbc;