Java Class with Struct
If you’re new to writing Java classes that use the SQLData interface, inside the Oracle Database, please read the blog post on: “How to build a Java Class with the SQLData Interface”. This is a much larger example that shows you how to implement a Struct as a parameter option and how to work with other input and output data types.
Building the Java Object Class
The Item Java source file provides us with a lot of topics to cover. We debated about breaking the code up for display, but decided against it. In this section, we simply show you a complete Java
example class. We did limit the example to one getter, the getRating() method, and one setter, the setRating() method, for single attribute values.
We also gave you a getName() method that lets you capture the Java class name, and a setItem() method that lets you set the list of class attributes with a Struct object type. The setItem() method shows you how to set the instance values through a direct method call rather than the readSQL() method implementation of the SQLData interface.
The following SQL statement creates the Item class in an Oracle Database as follows:
SQL> CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Item" AS 2 3 // JAVA LIBRARY imports. 4 import JAVA.SQL.*; 5 import JAVA.io.*; 6 import oracle.SQL.*; 7 import oracle.jdbc.*; 8 import oracle.oracore.*; 9 import oracle.jdbc2.*; 10 import JAVA.math.*; 11 12 PUBLIC class Item implements SQLData 13 { 14 // Implement the attributes AND operations FOR this TYPE. 15 PRIVATE BigDecimal id; 16 PRIVATE String title; 17 PRIVATE String subtitle; 18 PRIVATE String rating; 19 PRIVATE String ratingAgency; 20 PRIVATE DATE releaseDate; 21 22 // A getter FOR the rating attribute. 23 PUBLIC String getRating() { 24 RETURN this.rating; } 25 26 // A getter FOR the class instance name. 27 PUBLIC String getName() { 28 RETURN this.getClass().getName(); } 29 30 // A getter FOR the rating attribute. 31 PUBLIC String getUserName() { 32 33 // DECLARE local variables. 34 String userName = NEW String(); 35 String query = "SELECT user"; 36 37 // CREATE a connection discover a USER name. 38 try { 39 // DECLARE an Oracle connection. 40 OracleConnectionWrapper conn = 41 (oracle.jdbc.OracleConnectionWrapper) 42 DriverManager.getConnection("jdbc:default:connection:"); 43 44 // Prepare AND EXECUTE a statement. 45 JAVA.SQL.PreparedStatement ps = conn.prepareStatement(query); 46 ResultSet rs = ps.executeQuery(); 47 48 // Read the result SET. 49 WHILE (rs.next()) 50 userName = rs.getString(1); } 51 catch (SQLException e) {} 52 // RETURN a USER name. 53 RETURN userName; } 54 55 // A setter FOR this object. 56 PUBLIC void setItem(Struct item) throws JAVA.SQL.SQLException { 57 58 // Get the attributes OF the Item object. 59 Object[] attributes = (Object[]) item.getAttributes(); 60 61 // Assign Item instance variables. 62 this.id = (BigDecimal) attributes[0]; 63 this.title = (String) attributes[1]; 64 this.subtitle = (String) attributes[2]; 65 this.rating = (String) attributes[3]; 66 this.ratingAgency = (String) attributes[4]; 67 this.releaseDate = NEW DATE(((TIMESTAMP) attributes[5]).getTime()); } 68 69 // A setter FOR the rating attribute. 70 PUBLIC void setRating(String rating) { 71 this.rating = rating; } 72 73 // DECLARE an instance toString method. 74 PUBLIC String toString() { 75 RETURN "ID # [" + this.id + "]\n" + "Title [" + this.title + ": " + this.subtitle +"]\n" + 76 "Rating [" + this.ratingAgency + ":" + this.rating + "]\n" + 77 "Release [" + this.releaseDate + "]\n"; } 78 79 /* Implement SQLData interface. 80 || -------------------------------------------------------- 81 || Required interface components: 82 || ============================== 83 || 1. String sql_type instance variable. 84 || 2. getSQLTypeName() method returns the sql_type value. 85 || 3. readSQL() method to read from the Oracle session. 86 || 4. writeSQL() method to write to the Oracle session. 87 */ 88 89 // Required INTERFACE variable. 90 PRIVATE String sql_type; 91 92 // Returns the INTERFACE required variable VALUE. 93 PUBLIC String getSQLTypeName() throws SQLException { 94 RETURN this.sql_type; } 95 96 // Reads the stream FROM the Oracle SESSION. 97 PUBLIC void readSQL(SQLInput stream, String typeName) throws SQLException { 98 99 // Map instance variables. 100 this.sql_type = typeName; 101 this.id = stream.readBigDecimal(); 102 this.title = stream.readString(); 103 this.subtitle = stream.readString(); 104 this.rating = stream.readString(); 105 this.ratingAgency = stream.readString(); 106 this.releaseDate = stream.readDate(); } 107 108 // Writes the stream TO the Oracle SESSION. 109 PUBLIC void writeSQL(SQLOutput stream) throws SQLException { 110 111 // Map instance variables. 112 stream.writeBigDecimal(this.id); 113 stream.writeString(this.title); 114 stream.writeString(this.subtitle); 115 stream.writeString(this.rating); 116 stream.writeString(this.ratingAgency); 117 stream.writeDate(this.releaseDate); } 118 119 /* 120 || -------------------------------------------------------- 121 || End Implementation of SQLData interface. 122 */ 123 } 124 / |
The generic getter on lines 23 and 24 lets you get the instance value of the rating attribute, and the generic setter on lines 70 and 71 lets you override the rating instance variable. Lines 56 through 67 contain the logic for the setItem method. The setItem method takes a Struct parameter, which illustrates how you can use a composite data type. Line 57 converts the fields of the Struct parameter to an array of objects. Most of the conversions are straightforward and map through simple casting operations based on the Oracle Database rules. A date data type in the Oracle database is a DATE data type. While you may think that an Oracle DATE data type should convert to a Java Date class, it doesn’t. An Oracle DATE inside a Struct converts to a Timestamp class. You must cast the Java Timestamp class to a Java Date class through a two-step method. First, use the getTime() method to capture the long value of the Timestamp. Second, construct a new Date class by using the long value result from the getTime() method.
Line 67 from the preceding Item class example demonstrate the required Oracle DATE to Java Timestamp technique:
67 | this.releaseDate = new Date(((Timestamp) attributes[5]).getTime()); } |
Line 90 declares the sql_type variable. Lines 93 and 94 implement the getSQLTypeName() method for the SQLData interface. Lines 97 through 106 implement the readSQL() method, and lines 109 through 117 implement the writeSQL() method for the SQLData interface.
After creating and compiling the Item class, you need to create an object type for it. The object type wraps access to the Java class file.
Creating the PL/SQL Object Type
The Item object type provides us with the syntax for mapping object methods against native Java classes. As a refresher, you create object types with a CREATE TYPE statement. Then, you can deploy them as persistent or transient objects. Persistent objects can be either stand-alone or embedded objects. Stand-alone objects are stored in a database table and have a unique identifier. Embedded objects are not stored in a database table but are embedded in another Oracle structure, like a stored program parameter, return type, or as an attribute of another object type. Transient objects are instances of objects that aren’t stored in the database. They have a lifetime limited to the duration of their use in a PL/SQL block.
You create an instance of an object type and use an INSERT or UPDATE statement to assign it as a persistent object in a table. Alternatively, you can retrieve an object type from an IN OUT or OUT-only mode parameter, or get one as the output parameter of a function.
The following CREATE TYPE statement creates the object declaration in the session scope. You should note that each object type’s methods point to valid Java methods from the same Java class.
SQL> CREATE OR REPLACE TYPE item_obj IS OBJECT 2 ( id NUMBER 3 , title VARCHAR2(60) 4 , subtitle VARCHAR2(60) 5 , rating VARCHAR2(8) 6 , rating_agency VARCHAR2(4) 7 , release_date DATE 8 , MEMBER FUNCTION get_rating RETURN VARCHAR2 9 AS LANGUAGE JAVA 10 NAME 'Item.getRating() return java.lang.String' 11 , MEMBER FUNCTION get_name RETURN VARCHAR2 12 AS LANGUAGE JAVA 13 NAME 'Item.getName() return java.lang.String' 14 , MEMBER FUNCTION get_sql_type RETURN VARCHAR2 15 AS LANGUAGE JAVA 16 NAME 'Item.getSQLTypeName() return java.lang.String' 17 , MEMBER FUNCTION get_user_name RETURN VARCHAR2 18 AS LANGUAGE JAVA 19 NAME 'Item.getUserName() return java.lang.String' 20 , MEMBER PROCEDURE set_item (item ITEM_OBJ) 21 AS LANGUAGE JAVA 22 NAME 'Item.setItem(java.sql.Struct)' 23 , MEMBER PROCEDURE set_rating (rating VARCHAR2) 24 AS LANGUAGE JAVA 25 NAME 'Item.setRating(java.lang.String)' 26 , MEMBER FUNCTION to_string RETURN VARCHAR2 27 AS LANGUAGE JAVA 28 NAME 'Item.toString() return java.lang.String') 29 INSTANTIABLE NOT FINAL; 30 / |
Lines 2 through 7 declare a list of attributes. The physical size assigned to the VARCHAR2 data types limits the size of the Java String attributes. Any attempt to pass a variable-length string longer than the imposed limit in the PL/SQL object type definition raises a “character string buffer too small” error.
Line 20 shows a set_item procedure that passes a SQL item_obj object type, which maps to a java.sql.Struct on line 22. All the other parameters and return types use Java String classes.
The next section shows you how to test the Java class instance by calling the PL/SQL object type in various contexts.
Testing the Java Class File
This section shows you how to insert instances of the item object type into an item_struct table, and how to query the persistent item_obj object instance from the item_struct table. Then, this section shows you how to work with transient object instances in an anonymous PL/SQL block.
The following CREATE TABLE statement creates the item_struct sample table:
SQL> CREATE TABLE item_struct 2 ( item_struct_id NUMBER DEFAULT item_struct_id_seq.NEXTVAL 3 , item_struct ITEM_OBJ); |
You can construct and insert an item_obj object instance to the item_struct table with the following syntax:
SQL> INSERT INTO item_struct VALUES 2 ( item_struct_s.NEXTVAL 3 , item_obj( 1 4 ,'The Hobbit' 5 ,'An Unexpected Journey' 6 ,'PG-13' 7 ,'MPAA' 8 ,'14-DEC-2012')); |
Lines 3 through 8 create an instance of (instantiate) the item_obj type. The instance is assigned as a flattened object to the VALUES clause of the INSERT statement, and is inserted into the item_struct table.
Any attempt to access a single object type from a table in a query returns
ORA-22905: cannot ACCESS ROWS FROM a non-nested TABLE item |
To avoid this error, you create an item_tab or table collection of item_obj with the following syntax:
SQL> CREATE OR REPLACE 2 TYPE item_tab IS TABLE OF item_obj; 3 / |
Now, you can query persistent copies of the item_obj data type like this:
SQL> COLUMN title FORMAT A36 HEADING "Title" SQL> COLUMN rating FORMAT A6 HEADING "Rating" SQL> COLUMN rating_agency FORMAT A6 HEADING "Rating|Agency" SQL> COLUMN release_date FORMAT A9 HEADING "Release|Date" SQL> SELECT title||': '||subtitle AS title 2 , rating 3 , rating_agency 4 , release_date 5 FROM item_struct 6 CROSS JOIN 7 TABLE( 8 SELECT CAST(COLLECT(item_struct) AS item_tab)); |
The combination of the SQL COLLECT, CAST, and TABLE built-in functions on lines 7 and 8 let us query persistent objects. Combining these SQL functions with a CROSS JOIN lets us write a SELECT statement to query persistent object types. The preceding query prints the following when you have one or more rows in the item_struct table:
Rating Release Title Rating Agency Date ------------------------------------ ------ ------ --------- The Hobbit: An Unexpected Journey PG-13 MPAA 14-DEC-12 |
An anonymous PL/SQL block provides a more robust test case solution. The following shows you how to create and assign an object type to a local variable, and shows you how to test the getter functions and setter procedures:
SQL> DECLARE 2 /* Create an object type instance. */ 3 lv_item ITEM_OBJ := 4 item_obj( 1 5 ,'The Hobbit' 6 ,'An Unexpected Journey' 7 ,'PG-13' 8 ,'MPAA' 9 ,'14-DEC-2012'); 10 BEGIN 11 /* Print the getter rating result. */ 12 dbms_output.put_line( 13 '---------------------------------------------'); 14 dbms_output.put_line( 15 'Rating Value: ['||lv_item.get_rating()||']'); 16 17 /* Set the value of the rating. */ 18 lv_item.set_rating('PG'); 19 20 /* Print the getter rating result. */ 21 dbms_output.put_line( 22 'Rating Value: ['||lv_item.get_rating()||']'); 23 dbms_output.put_line( 24 '---------------------------------------------'); 25 26 /* Print user name and sql_type value. */ 27 dbms_output.put_line( 28 'User Name: ['||lv_item.get_user_name()||']'); 29 dbms_output.put_line( 30 'Class Name: ['||lv_item.get_name()||']'); 31 dbms_output.put_line( 32 'Object Name: ['||lv_item.get_sql_type()||']'); 33 34 /* Print the toString value. */ 35 dbms_output.put_line( 36 '---------------------------------------------'); 37 parse_rows(lv_item.to_string()); 38 dbms_output.put_line( 39 '---------------------------------------------'); 40 END; 41 / |
Lines 3 through 9 create an instance of the item_obj object type and assign the instance to the lv_item local variable. Lines 14 and 15 print the current instance’s rating value, line 18 resets the instance’s rating value, and lines 21 and 22 print the modified rating value. These three activities demonstrate a call to the getter of an object type’s attribute before and after the call to the setter to reset the value of an object type’s attribute.
Lines 27 through 32 show how you can call several of the other getter functions, like the get_user function. The get_userto_string procedure of the lv_item local variable as a parameter to the parse_rows procedure.
The toString() methods of Java classes often return long strings. The parse_rows procedure lets you avoid the forced line breaks imposed by the dbms_output package. Wrapping text in a call to the following parse_rows procedure lets you avoid the 80-character limit of the put_line procedures:
SQL> CREATE OR REPLACE PROCEDURE parse_rows 2 ( pv_text VARCHAR2 ) IS 3 /* Declare parsing indexes. */ 4 lv_start NUMBER := 1; 5 lv_end NUMBER := 1; 6 lv_length NUMBER; 7 BEGIN 8 /* Assign an end value by parsing line return. */ 9 IF INSTR(pv_text,CHR(10),lv_start) = 0 THEN 10 lv_end := LENGTH(pv_text) + 1; 11 ELSE 12 lv_end := INSTR(pv_text,CHR(10),lv_start); 13 END IF; 14 15 /* Assign a length value to parsed strings. */ 16 lv_length := lv_end - lv_start; 17 18 /* Print first line. */ 19 dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length)); 20 21 /* Print the rows of a multiple line string. */ 22 WHILE (lv_end < LENGTH(pv_text)) LOOP 23 24 /* Assign a new start and end value. */ 25 lv_start := lv_end + 1; 26 IF INSTR(pv_text,CHR(10),lv_start + 1) = 0 THEN 27 lv_end := LENGTH(pv_text) + 1; 28 ELSE 29 lv_end := INSTR(pv_text,CHR(10),lv_start + 1); 30 END IF; 31 lv_length := lv_end - lv_start; 32 33 /* Print the individual rows. */ 34 dbms_output.put_line(SUBSTR(pv_text,lv_start,lv_length)); 35 36 END LOOP; 37 END; 38 / |
It prints the following:
--------------------------------------------- Rating Value: [PG-13] Rating Value: [PG] --------------------------------------------- User Name: [VIDEO] Class Name: [Item] Object Name: [VIDEO.ITEM_OBJ] --------------------------------------------- ID # [1] Title [The Hobbit: An Unexpected Journey] Rating [MPAA:PG] Release [2012-12-14] --------------------------------------------- |
The SELECT statement and anonymous PL/SQL blocks have shown you how to work with and test Oracle object types, that insert and query data. The next step would require you to learn how to use object subclasses, which are in the “How to write a Java Subclass with the SQLData Interface” blog post.
Subscribe via RSS