1 / 81

CS276 Advanced Oracle Using Java

CS276 Advanced Oracle Using Java. Chapter 6 Database Objects. Creating Object Types. -- create the object types CREATE TYPE address_typ AS OBJECT ( street VARCHAR2(15), city VARCHAR2(15), state CHAR(2), zip VARCHAR2(5) );. Creating Object Types.

elton-pope
Download Presentation

CS276 Advanced Oracle Using Java

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS276 Advanced Oracle Using Java Chapter 6 Database Objects

  2. Creating Object Types -- create the object types CREATE TYPE address_typ AS OBJECT ( street VARCHAR2(15), city VARCHAR2(15), state CHAR(2), zip VARCHAR2(5) );

  3. Creating Object Types CREATE TYPE person_typ AS OBJECT ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), dob DATE, phone VARCHAR2(12), address address_typ ) NOT FINAL;

  4. Creating Object Types CREATE TYPE product_typ AS OBJECT ( id NUMBER, name VARCHAR2(10), description VARCHAR2(22), price NUMBER(5, 2), days_valid NUMBER, -- declare the get_sell_by_date() member function, -- get_sell_by_date() returns the date by which the -- product must be sold MEMBER FUNCTION get_sell_by_date RETURN DATE );

  5. Creating Object Types CREATE TYPE BODY product_typ AS -- define the get_sell_by_date() member function, -- get_sell_by_date() returns the date by which the -- product must be sold MEMBER FUNCTION get_sell_by_date RETURN DATE IS sell_by_date DATE;

  6. Creating Object Types BEGIN -- calculate the sell by date by adding the days_valid attribute -- to the current date (sysdate) SELECT days_valid + sysdate INTO sell_by_date FROM dual;

  7. Creating Object Types -- return the sell by date RETURN sell_by_date; END; END;

  8. Using Object Types to Define Column Objects and Object Tables -- create the tables CREATE TABLE products ( product product_typ, quantity_in_stock NUMBER ); CREATE TABLE object_products OF product_typ; CREATE TABLE object_customers OF person_typ;

  9. Using Object Types to Define Column Objects and Object Tables Object References and Object Identifiers CREATE TABLE purchases ( id NUMBER PRIMARY KEY, customer REF person_typ SCOPE IS object_customers, product REF product_typ SCOPE IS object_products );

  10. Performing DML on the products Table The products table is defined as follows CREATE TABLE products ( product product_typ, quantity_in_stock NUMBER ); Inserting Rows into the products Table INSERT INTO products ( product, quantity_in_stock) VALUES ( product_typ(1, 'Pasta', '20 oz bag of pasta', 3.95, 10), 50 );

  11. Performing DML on the products Table Inserting Rows into the products Table INSERT INTO products ( product, quantity_in_stock) VALUES ( product_typ(2, ‘Sardines', ‘12 oz box of sardines', 2.99, 5), 25 );

  12. Performing DML on the products Table Selecting Rows from the products Table SELECT * FROM products; Product(ID,NAME,DESCRIPTION,PRICE,DAYS_VAL) QUANTITY_IN_STO -------------------------------------------------------------------------- ----------------------- PRODUCT_TYP(1,'Pasta','20 oz bag of pasta', 3.95,10) 50 PRODUCT_TYP(1,'Pasta','12 oz box of sardines', 2.99,5) 25

  13. Performing DML on the products Table Selecting Rows from the products Table SELECT p.product FROM products p WHERE p.product.id = 1; Product(ID,NAME,DESCRIPTION,PRICE,DAYS_VAL) -------------------------------------------------------------------------- PRODUCT_TYP(1,'Pasta','20 oz bag of pasta', 3.95,10)

  14. Performing DML on the products Table Selecting Rows from the products Table SELECT p.product.get_sell_by_date() FROM products p p.PRODUCT ---------------- 12-MAR-02 07-MAR-02

  15. Performing DML on the products Table • Updating a Row in the products Table UPDATE products p SET p.product.description = ’30 oz bag of pasta’ WHERE p.product.id = 1;

  16. Performing DML on the products Table • Deleting Rows from the products Table DELETE FROM products p WHERE p.product.id = 2;

  17. Performing DML on the object_products Table • CREATE TABLE object_products OF product_typ; Inserting Rows into the object_products Table

  18. Performing DML on the object_products Table • Selecting Rows from the object_products Table

  19. Performing DML on the object_products Table • Updating a Row in the object_products Table

  20. Performing DML on the object_products Table • Deleting a Row from the object_products Table

  21. Performing DML on the object_customers Table • CREATE TABLE object_customers OF person_typ; Inserting Rows into the object_customers Table

  22. Performing DML on the object_customers Table • Selecting Rows from the object_customers Table

  23. Performing DML on the purchases Table CREATE TABLE purchases ( id NUMBER PRIMARY KEY, customer REF person_typ SCOPE IS object_customers, product REF product_typ SCOPE IS object_products )

  24. Performing DML on the purchases Table • Inserting a Row into the purchases Table INSERT INTO purchases ( id, customer, product ) VALUES ( 1, (SELECT REF(oc) FROM object_customers oc WHERE oc.id = 1), (SELECT REF(op) FROM object_products op WHERE op.id = 1) );

  25. Performing DML on the purchases Table • Selecting Rows from the purchases Table SELECT * FROM purchases; IDCUSTOMER PRODUCT • 00002202AB6256 00002202AB4256 You can access the rows in the object tables that are pointed to by REF column values using the DEREF() function

  26. Performing DML on the purchases Table • Selecting Rows from the purchases Table SELECT DEREF(customer),DEREF(product) FROM purchases; DEREF(CUSTOMER)(ID,First_Name,. ADDRESS(STREET, CITY,STATE, ZIP)DEREF(PRODUCT)(ID, ..)

  27. Performing DML on the purchases Table • Updating a Row in the purchases Table UPDATE purchases SET product = ( SELECT REF(op) FROM object_products op WHERE op.id = 2) WHERE id = 1;

  28. Oracle9i Database Type Inheritance You can use object type inheritance. If you would like to inherite exiting attributes from object, for example person_typ, you must use NOT FINAL clause in this object. The NOT FINAL clause indicates that person_typ can be inherited from when defining another type. The default is FINAL.

  29. Oracle9i Database Type Inheritance CREATE TYPE person_typ AS OBJECT ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), dob DATE, phone VARCHAR2(12), address address_typ ) NOT FINAL;

  30. Oracle9i Database Type Inheritance To have a new type inherit attributes and methods from an exiting type, use the UNDER clause: CREATE TYPE business_person_typ UNDER person_typ ( title VARCHAR2(20), company VARCHAR2(20) );

  31. Oracle9i Database Type Inheritance CREATE TABLE object_business_customers OF business_person_typ; -- insert sample data into object_business_customers table INSERT INTO object_business_customers VALUES ( business_person_typ(1, 'John', 'Brown', '01-FEB-1955', '800-555-1211', address_typ('2 State Street', 'Beantown', 'MA', '12345'), 'Manager', 'XYZ Corp' ) );

  32. Oracle9i Database Type Inheritance SELECT * FROM object_business_customers;

  33. Oracle9i Database Type InheritanceNOT INSTANTIABLE Object Types You can mark an object type as NOT INSTANTIABLE, this prevents objects of that type from being added to tables. You use this clause when you want to use that type only as part of another type or as a supertype.

  34. Oracle9i Database Type InheritanceNOT INSTANTIABLE Object Types -- create the object types CREATE TYPE address_typ AS OBJECT ( street VARCHAR2(15), city VARCHAR2(15), state CHAR(2), zip VARCHAR2(5) ) NOT INSTANTIABLE;

  35. Accessing Database Objects Using Weakly Typed Java Objects Weakly typed objects are objects of the oracle.sql.STRUCT class Struct objects are referred to as weakly typed because their attributes are represented using the generic Java Object class and therefore don’t have a specific type. When you use weakly typed objects, you must make sure you are casting the object attributes to the required type.

  36. Accessing Database Objects Using Weakly Typed Java Objects You can also use Strongly Typed Objects that use a specific class to represent objects and their attributes. Why would you want to use Weakly Typed objects when you can use Strongly Typed objects? Weakly typed objects have two advantages:

  37. Accessing Database Objects Using Weakly Typed Java Objects Weakly typed objects have two advantages: • You can use Weakly Typed objects with any database object type. Strongly objects use a different class for each database object type. • You save some memory and time since you don’t incur the overhead associated with creating a strongly typed object.

  38. Accessing Database Objects Using Weakly Typed Java Objects So, you should typically use Weakly Typed objects if you are selecting and inserting data without doing a lot of manipulation of that data in memory. On other hand, if you do need to do a lot of manipulation of data in memory, you should use strongly typed objects, since they store the data in a more convenient form for data manipulation using Java statements.

  39. Accessing Database Objects Using Weakly Typed Java ObjectsInserting a Database Object Using a STRUCT // step 1: create a StructDescriptor object for the database // object type (in this case, OBJECT_USER.PRODUCT_TYP) StructDescriptor productDescriptor = StructDescriptor.createDescriptor( "OBJECT_USER.PRODUCT_TYP", myConnection );

  40. Accessing Database Objects Using Weakly Typed Java ObjectsInserting a Database Object Using a STRUCT // step 2: create an Object array to store the attributes for // the new database object (in this case, a new product) Object [] productAttributes = new Object[5]; productAttributes[0] = new BigDecimal(3); // id productAttributes[1] = "Chips"; // name productAttributes[2] = "10 oz bag of chips"; // description productAttributes[3] = new BigDecimal(0.99); // price productAttributes[4] = new BigDecimal(20); // days_valid

  41. Accessing Database Objects Using Weakly Typed Java ObjectsInserting a Database Object Using a STRUCT // step 3: create a STRUCT object STRUCT product = new STRUCT(productDescriptor, myConnection, productAttributes); // step 4: use a prepared statement to insert the STRUCT // into the table PreparedStatement myPrepStatement = myConnection.prepareStatement( "INSERT INTO object_products VALUES (?)" ); myPrepStatement.setObject(1, product); myPrepStatement.execute(); myPrepStatement.close();

  42. Accessing Database Objects Using Weakly Typed Java ObjectsSelecting Database Objects into STRUCT // step 1: create a result set and use it to select // the objects ResultSet productResultSet = myStatement.executeQuery( "SELECT VALUE(op) " + "FROM object_products op" );

  43. Accessing Database Objects Using Weakly Typed Java ObjectsSelecting Database Objects into STRUCT // a: retrieve each database object from the result set // using the getObject() method, casting the returned Object // to a STRUCT STRUCT product = (STRUCT) productResultSet.getObject(1); // b: retrieve the attribute values from the STRUCT using // the getAttributes() method, storing them in an Object array Object [] productAttributes = product.getAttributes(); // c: read the attribute values from the Object array System.out.println("id = " + productAttributes[0]); System.out.println("name = " + productAttributes[1]); System.out.println("description = " + productAttributes[2]); System.out.println("price = " + productAttributes[3]); System.out.println("days_valid = " + productAttributes[4]);

  44. Accessing Database Objects Using Weakly Typed Java ObjectsSelecting Database Objects into STRUCT // create a CallableStatement object and use it to call the // get_sell_by_date() function CallableStatement myCallableStatement = myConnection.prepareCall( "{? = call product_typ.get_sell_by_date(?)}" ); myCallableStatement.registerOutParameter(1, Types.DATE); myCallableStatement.setObject(2, product); myCallableStatement.execute(); System.out.println("sell by date = " + myCallableStatement.getDate(1)); myCallableStatement.close(); } // end of while loop // step 3: close the result set productResultSet.close();

  45. Accessing Database Objects Using Weakly Typed Java ObjectsUpdating Database Objects using a STRUCT SEE Steps1 through 4 in the previous section // step 5: change the attributes in the Object array productAttributes[2] = "25 oz box of sardines"; // description productAttributes[3] = new BigDecimal(3.49); // price // step 6: retrieve the StructDescriptor using the // getDescriptor() method StructDescriptor productDescriptor = product.getDescriptor();

  46. Accessing Database Objects Using Weakly Typed Java ObjectsUpdating Database Objects using a STRUCT // step 7: create a new STRUCT object STRUCT updatedProduct = new STRUCT(productDescriptor, myConnection, productAttributes); // step 8: use a prepared statement to perform the update PreparedStatement myPrepStatement = myConnection.prepareStatement( "UPDATE object_products op " + "SET VALUE(op) = ? " + "WHERE op.id = ?" ); myPrepStatement.setObject(1, updatedProduct); myPrepStatement.setInt(2, id); myPrepStatement.execute(); myPrepStatement.close();

  47. Accessing Database Objects Using Weakly Typed Java ObjectsDELETING Database Objects using a STRUCT int id ) throws SQLException { System.out.println("Deleting product #" + id + " from the object_products table"); // delete a product from the object_products table myStatement.execute( "DELETE FROM object_products op " + "WHERE op.id = " + id );

  48. Example Program:ObjectExample1.java

  49. Weakly Typed Object References • An Object Reference is a pointer to an object. You can store an object reference in a table using the REF database type, and the purchases table created earlier contains two REF columns named customer and products. These columns point to objects in the object_customers and onject_products tables, respectively. You can store an object reference in your Java program using the oracle.sql.REF class; this class implements the java.sql.Ref interface.

  50. Weakly Typed Object References • Selecting an Object Reference into a REF Object // step 1: create a result set and select the object // references from the REF columns ResultSet purchaseResultSet = myStatement.executeQuery( "SELECT customer, product " + "FROM purchases“ ); purchaseResultSet.next();

More Related