CS276 Advanced Oracle Using Java

74 pages
4 views

Please download to get full document.

View again

of 74
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Share
Description
CS276 Advanced Oracle Using Java. Chapter 3 The Basics of JDBC Programming. The Oracle JDBC Drivers. Thin driver OCI driver Server-side internal driver Server-side Thin driver. Importing the JDBC Packages. There are two sets of JDBC packages:
Transcript
CS276 Advanced Oracle Using JavaChapter 3The Basics of JDBC ProgrammingThe Oracle JDBC Drivers
  • Thin driver
  • OCI driver
  • Server-side internal driver
  • Server-side Thin driver
  • 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
  • Importing the JDBC Packages
  • To use JDBC packages in your programs you should:
  • import java.sql.*;
  • 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( ));Opening a Database Connection
  • Connecting to the Database Using the getConnection() Method of the DriverManager Class
  • DriverManager.getConnection(URL, username, password);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
  • 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)))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
  • 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”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
  • 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 classOracleDataSource myDataSource=new OracleDataSource();Opening a Database ConnectionConnecting to the Database Using an Oracle Data Source
  • Step 2
  • Set the Oracle data source object attributes using set methodsThe 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 93The oracle.jdbc.pool.OracleDataSource class provides an additional set of attributes( See Table 3-2, page94)Oracle Data Source Attributes ( Table 3-1, page 93)Oracle Data Source Attributes( Table 3-1, page 93)oracle.jdbc.pool.OracleDataSource class additional set of attributes( See Table 3-2, page94)Opening a Database ConnectionConnecting to the Database Using an Oracle Data SourceExamples using set methods:myDataSource.setServerName(“localhost”);myDataSource.setDatabaseName(“ORCL”);myDataSource.setDriverType(“Thin”);myDataSource.setNetworkProtocol(“tcp”);myDataSource.setPortNumber(1521);myDataSource.setUser(“scott”);myDataSource.setPassword(“tiger”);Opening a Database ConnectionConnecting to the Database Using an Oracle Data SourceExamples using get methods:String serverName = myDataSource.getServerName();String databaseName= myDataSource.getDatabaseName();String driverType = myDataSource.getDriverType();String networkProtocol = myDataSource.getNetworkProtocol();int portNumber = myDataSource.getPortNumber();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 myDataSourceOpening 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" );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() );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" );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";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(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");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" );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");Control Transactions // disable auto-commit mode myConnection.setAutoCommit(false);// commit any chages myConnection.commit();// rollback the changes made to the database myConnection.rollback();Retrieving Rows from the DataBaseBecause a SELECT statement may return more than one row, executeQuery() returns an object that stores the rows - ResultSet objectThere 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
  • 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 SELECTUse 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");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
  • Oracle and Java TypesOracle 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
  • 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
  • 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;
  • 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)
  • 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");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");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();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 " +Perform DDL statements " REFERENCES customers(id)," + " street VARCHAR2(20) NOT NULL," + " city VARCHAR2(20) NOT NULL," + " state CHAR(2) NOT NULL" + ")" );Closing Your JDBC ObjectsmyStatement.close();myConnection.close();Example Program:BasicExample1.javaThe 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 statementsHow to use prepared SQL statements// create a PreparedStatement object PreparedStatement myPrepStatement = myConnection.prepareStatement( "INSERT INTO products " + "(id, type_id, name, description, price) VALUES (" + "?, ?, ?, ?, ?" + ")" );How to use prepared SQL statementsThere 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
  • How to use prepared SQL statementsThe 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 statementHow to use prepared SQL statementsfor (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 loopHow to use prepared SQL statementsThe program BasicExample2.java contains the statements shown in this sectionThe 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;
  • The Oracle JDBC ExtensionsThe oracle.sql packageThe Oracle JDBC ExtensionsThe oracle.sql packageThe oracle.sql.NUMBER Classoracle.sql.NUMBER id = new oracle.sql.NUMBER(6);int idInt = id.intValue();example for floating – point number:oracle.sql.NUMBER id = new oracle.sql.NUMBER(19.95);The Oracle JDBC ExtensionsThe oracle.sql packageThe oracle.sql.NUMBER ClassYou can use following examples with 5 methods from this class:float priceFloat = price.floatValue();double priceDouble = price.doubleValue();java.math.BigDecimal priceBigDec = price.bigDecimalValue();string priceString = price.stringValue();The Oracle JDBC ExtensionsThe oracle.sql package
  • The oracle.sql.CHAR Class
  • There are two steps you must folow to create your own oracle.sql.CHAR object:
  • Create an oracle.sql.CharacterSet object containig the character set you wish to use
  • Create an oracle.sql.CHAR object using previous oracle.sql.CharacterSet object to specify the character set.
  • The Oracle JDBC ExtensionsThe oracle.sql package
  • The oracle.sql.CHAR Class
  • Create an oracle.sql.CharacterSet object containig the character set you wish to use
  • oracle.sql.CharacterSet myCharSet = CharacterSet.make(CharacterSet.US7ASCII_CHARSET);
  • The Oracle JDBC ExtensionsThe oracle.sql packageThe oracle.sql.CHAR Class2.Create an oracle.sql.CHAR objectoracle.sql.CHAR firstName = new oracle.sql.CHAR( “Jason”, myCharSet);String firstNameString = firstName.stringValue();System.out.println(“firstNameString = “ + firstNameString);The Oracle JDBC ExtensionsThe oracle.sql packageThe oracle.sql.Date Class is compatible with the database DATE typeoracle.sql.DATE dob = new oracle.sql.DATE( “1969-02-01 13:54:12”);String dobString = dob.stringValue();System.out.println(“firstNameString = “ + firstNameString);The Oracle JDBC ExtensionsThe oracle.sql packageThe oracle.sql.ROWIDClass is compatible with the database ROWID typeoracle.sql.ROWID ROWID;The Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Classes and Interfaces of the oracle.jdbc Package ( See Table, page 129).The Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Oracle JDBC ExtensionsThe oracle.JDBC PackageThe Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OraclePreparedStatement Object// create an OraclePreparedStatement object OraclePreparedStatement myPrepStatement = (OraclePreparedStatement) myConnection.prepareStatement( "INSERT INTO customers " + "(id, first_name, last_name, dob, phone) VALUES (" + "?, ?, ?, ?, ?" + ")" );The Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OraclePreparedStatement Object// bind the objects to the OraclePreparedStatement using the // appropriate set methods myPrepStatement.setNUMBER(1, id); myPrepStatement.setCHAR(2, firstName); myPrepStatement.setCHAR(3, lastName); myPrepStatement.setDATE(4, dob);// set the phone column to NULL myPrepStatement.setNull(5, OracleTypes.CHAR);The Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OraclePreparedStatement Object// run the PreparedStatement myPrepStatement.execute(); System.out.println("Added row to customers table");The Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OracleResultSet Object// retrieve the ROWID, id, first_name, last_name, dob, and // phone columns for this new row using an OracleResultSet // object Statement myStatement = myConnection.createStatement();The Oracle JDBC ExtensionsThe oracle.JDBC PackageOracleResultSet customerResultSet = (OracleResultSet) myStatement.executeQuery( "SELECT ROWID, id, first_name, last_name, dob, phone " + "FROM customers " + "WHERE id = 6" ); System.out.println("Retrieved row from customers table");The Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OracleResultSet Object// declare an oracle.sql.ROWID object to store the ROWID, and // an oracle.sql.CHAR object to store the phone column oracle.sql.ROWID rowid; oracle.sql.CHAR phone = new oracle.sql.CHAR("", myCharSet);The Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OracleResultSet Object// display the column values for row using the // get methods to read the values while (customerResultSet.next()) { rowid = customerResultSet.getROWID("ROWID"); id = customerResultSet.getNUMBER("id"); firstName = customerResultSet.getCHAR("first_name"); lastName = customerResultSet.getCHAR("last_name"); dob = customerResultSet.getDATE("dob"); phone = customerResultSet.getCHAR("phone"); (see next slide)The Oracle JDBC ExtensionsThe oracle.JDBC PackageUsing an OracleResultSet ObjectSystem.out.println("rowid = " + rowid.stringValue()); System.out.println("id = " + id.stringValue()); System.out.println("firstName = " + firstName); System.out.println("lastName = " + lastName); System.out.println("dob = " + dob.stringValue()); System.out.println("phone = " + phone); } // end of while loopExample Program:BasicExample3.java
    Related Search
    We Need Your Support
    Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

    Thanks to everyone for your continued support.

    No, Thanks