Oracle-magpie tutorial

Oracle-magpie tutorial

Class mapping and oracle object type method call

Different methods call and arguments passing tuning

Handling collections and in/out arguments

0. Sample enviroment and configuration

The tutorial is based on a simple bookstore application. Before examining oracle-magpie use cases you should set up a sample environment.

Oracle user schema set up

For the tutorial samples execution you need an oracle user schema with the RESOURCE and CONNECT roles granted. Execute the script /src/sqlscripts/prepareSchema.sql under the user: it creates necessary tables, loads data, creates oracle object types and packages.

Java sample application description

Samples java sources consist of packages: tutor.model - entity model classes, tutor.proxies - proxy interfaces for the oracle stored objects (packages/types), tutor.cases - runnable sample cases, described in the tutorial. Each section in this document has corresponding sample class, for example Simple stored procedure call - tutor.cases.Case1 etc.

Required libraries

Oracle-magpie library has other .jar dependencies:

ojdbc14.jar,orai18n.jar - oracle jdbc driver and classes for NLS support in Oracle Object and Collection types, last versions are here JDBC, SQLJ, Oracle JPublisher and Universal Connection Pool (UCP).
xml-apis.jar - xerces xml parser The Apache Xerces Project.

Configuration file

In main xml-configuration file you can define data-source looking on your sample schema and paths to other xml-resources with class and interface mappings descriptions. Below your can see the sample application configuration file: /src/resources/tutor-config.xml


<?xml version="1.0" encoding="UTF-8"?> 


<config>


    <data-source class="oracle.jdbc.pool.OracleDataSource"

	 
          url="jdbc:oracle:thin:@127.0.0.1:1521:db102"

		
          user="bookstore" password="bookstore">

		
     </data-source>

		
    <mappings>

	
      <file name="./bin/resources/default-mappings.xml"/>

		
	    	<file name="./bin/resources/class-mappings.xml"/>

		
	    	<file name="./bin/resources/object-mappings.xml"/>

		
	  </mappings>

	

</config>

default-mappings.xml is the configuration file with predefined mappings for the common scalar sql and java types (like int, Long, VARCHAR2, NUMBER,..).
class-mappings.xml and object-mappings.xml files have mappings specific to the sample bookstore application.

1. Simple stored procedure call

In the first example we make simple calls of the package stored procedures. The package is PKG_BOOKS_API. The java use case class is tutor.cases.Case1. Before any call to oracle, we have to initialize library configuration by one of the Configuration class constructor, as in example:

Configuration config = new Configuration("./bin/resources/tutor-config.xml");
((OracleDataSource)config.getDataSource()).setConnectionCachingEnabled(true);

By the second line we set pool to the caching mode (after any stored procedure call connection.close() method is invoked, setting cache mode we exclude reconnection overhead). Next step is to get a proxy of the PKG_BOOKS_API package:

PKG_BOOKS_API packageInstance = (PKG_BOOKS_API) config.getPackageProxy(PKG_BOOKS_API.class);

The PKG_BOOKS_API class is a java interface which methods are stubs to the corresponding PKG_BOOKS_API (name the same, but it's not mandatory) package procedures.

Note: All PKG_BOOKS_API interface methods throw two checked exceptions: SQLException and DBObjectConversionException. It's recommended always to declare this exceptions in your methods, but not mandatory.

Capability to obtain the proxy is defined in the object-mappings.xml configuration file with a mapping:


	<db-object object-is="package" class="tutor.proxies.PKG_BOOKS_API" 
				name="PKG_BOOKS_API">

           

 
	 	<procedure java-name="updateBookPrice" db-name="updateBookPrice">

		
	   		<argument position="0" db-name="bookId" class="java.lang.Long"/>

			
	   		<argument position="1" db-name="newPrice" class="java.lang.Double"/>

					
	 	</procedure>

		


	 	<procedure java-name="getBookPrice" db-name="getBookPrice">

		
		   	<return  class="double" />

			
		   	<argument position="0" db-name="bookId" class="java.lang.Long"/>

								
		  </procedure>
		


		
	  	.......




	</db-object>>

After getting the proxy instance we can try to invoke the interface methods and check stored procedures calls correctness. Purpose of the procedures is the simple getting and setting the PRICE column of the BOOK table.


		double bookPrice = packageInstance.getBookPrice(68l);	

	
		packageInstance.updateBookPrice(68l, new Double(1000.0));

		
		double newBookPrice = packageInstance.getBookPrice(68l);

2. Class mapping and oracle object type method call

This example describes a java class to an oracle object type mapping and database methods invocation. Oracle object types is the object-oriented buildup under the pl/sql language, which is real objected-oriented language with some limitations. For the more information, see official oracle documentation: Oracle® Database Object-Relational Developer's Guide. Before using any java class instance as argument in a stored procedure call we have to define oracle object type or use existing one to which the java class will be mapped. For example, for the tutor.model.Book class with definition:


public class Book {



     Long			bookId;

      
     String			title;

     
     String			isbn;

    
     String			annotation;

     
     String			publicationDate;

    
     int				pages;

     
     String			publisher; 

    
      Double			price;

    
      List<BookReview>	reviews;

    
      List<String>		authors;

    
      .....

    
}

we have an oracle object type TBOOK with definition:


CREATE OR REPLACE TYPE TBOOK AS OBJECT


(


     bookId              number,

      
     title               varchar2(4000),

     
     isbn                varchar(50),

    
     annotation          CLOB,

     
     publicationDate     varchar2(20),

    
     pages               integer,

     
     publisher           varchar2(1000),

     
     price               binary_double,

    
    
     reviews             tab_reviews,

    
     authors             TAB_VARCHAR2,

    
    
     member procedure save,


     ....

        
)
/

and a mapping in the class-mappings.xml file with such simple syntax:



 	<class-mapping class="tutor.model.Book" db-type="TBOOK">
  
  		<field name="bookId" db-name="BOOKID" />
  
  		<field name="title" db-name="TITLE" />
  
  		<field name="isbn" db-name="ISBN" />
  
  		<field name="authors" db-name="AUTHORS" element-class="java.lang.String" />
  
  		<field name="reviews" db-name="REVIEWS"
  
   				element-class="tutor.model.BookReview" />
  
  		<field name="publisher" db-name="PUBLISHER" />
  
  		<field name="publicationDate" db-name="PUBLICATIONDATE" />
  
  		<field name="pages" db-name="PAGES" />
  
  		<field name="annotation" db-name="ANNOTATION" />
  
  		<field name="price" db-name="PRICE" />
  
 	</class-mapping>

Notes:
1. You have to set full class name in the "class" attribute value.
2. Setting value for the attribute "element-class" for collection fields is mandatory.
3. Mapped class have to have well-defined getters and setters for all mapped fields.

After the mapping definition it is possible to use instances of the tutor.model.Book class. Look at the tutor.model.Case2 sample. First lines are the same as in previous case. Then, we create tutor.model.Book object instance, and next get an object type proxy for this instance:


		TYPE_BOOK dbProxy = (TYPE_BOOK) config.getObjectTypeProxy(TYPE_BOOK.class, book);

		
		book.setDbProxy(dbProxy);

After getting the proxy we make some object type methods calls:


		
		book.save();
		

		....
		

		book.addReview(review);

During invoking magpie library automatically passes the tutor.model.Book instance to the oracle, transforms it to the TYPE_BOOK instance, calls appropriate methods, transforms back, returns to the java client, and updates source java class instance. An object type methods mapping looks like a package mapping, except of the object-is attribute of db-object element, which have to be setted to "type" value, and mandatory the base-class attribute.



	<db-object object-is="type" class="tutor.proxies.TYPE_BOOK" 

		   		name="TBOOK" base-class="tutor.model.Book">


                    	            
  		<procedure java-name="save" db-name="save">		

  		</procedure>


		
  		....

		

  		<procedure java-name="addReview" db-name="addReview">

     				<argument position="0" class="tutor.model.BookReview" db-name="newReview"/>

  		</procedure>



	</db-object>

3. Different methods call and arguments passing tuning

Third sample case examine tuning features of the magpie library. Through the tutor.cases.Case3.main() method code we get customers list from the database by calling PKG_BOOKS_API.getCustomersList procedure. Next, in the "for" cycle we look for a customer with the maximum sum by all orders. It's performed by the calculateTotalOrderedSum method of TCUSTOMER object type. There is just simple select in the method:


            select nvl(sum(o.sum),0) into result from orders o
                where customer_id=self.customerId;

For the correct execution of the method we need only customerId field value and no necessity to pass whole object graph instance to the oracle and back. If we pass the whole instance, in a real application it could be serious performance downgrade in a similar case. So it would be rational to restrict the instance data completeness in this procedural call. It can be carry out by means of the property-group element in a class mapping configuration. Look at the Customer class mapping in the class-mappings.xml configuration file:


	<class-mapping class="tutor.model.Customer" db-type="TCUSTOMER">

		  <field name="customerId" db-name="customerId" />


		  ....
		


		  <property-group name="withoutOrders" behaviour="skip">

			    <property name="orders"/>

		  </property-group>


		
		  <property-group name="empty" behaviour="pass">

		  </property-group>

		
	</class-mapping>

There are two property-groups in the class mapping, behaviour attribute value serves as fields presense predicate, so "pass" value means that all listed fields will be passed, and "skip" value means that will be passed all fields except listed. So, "withoutOrders" property-group contains all fields, except of the orders ArrayList, and "empty" property-group doesn't contain any field at all. Using of a selected property-group in a concrete method call is defined in a method mapping, for example, the calculateTotalOrderedSum mapping:


		  <procedure java-name="calculateTotalOrderedSum" db-name="calculateTotalOrderedSum"

			    	in-group="withoutOrders" out-group="empty">	

			    <return  class="java.lang.Double"/>	
		
		  </procedure>

The in-group attribute is used for defining fields which will be passed to the oracle in a method call;
The out-group attribute is used for defining fields which values will be updated after an instance return from the oracle;

Further in the tutor.cases.Case3.main() code you can test this options in different calls of the TCUSTOMER.updateCustomerInfo method. Note, that is possible to map the same object type method to different java interface methods.

4. Handling collections and out arguments

Fourth sample case focuses on stored procedures arguments passing behaviour, which can be IN (by default), OUT and IN/OUT. There is nothing interesting in the IN mode - an argument value is just passed. With the OUT mode there are cases:

1. If the java method argument class is primitive - it will be ignored.
2. If the argument value in a concrete invoking is null - it will be ignored too.
3. If the argument class isn't primitive and a value isn't null - it will be updated with accordance to the appropriate mapping.

The IN/OUT mode is just IN mode plus OUT mode.

In the tutor.cases.Case4 class code key point is a calling of the PKG_BOOKS_API.formatBookIsbns procedure. The procedure iterates through list of the TBOOK objects and modifies the ISBN field with accordant table data updating. The procedure argument mode is the IN/OUT, so updated list will be returned to the java client. How it will be handled? There is a attribute collection-merging in the argument element of the method mapping, which determine what to do with an initial collection after IN/OUT argument returning. Possible attribute values are:

1. "reload": the initial collection will be cleared and filled with new values of returned argument.
2. "append": collection values of returned argument will be added to the initial collection.
3. "update": the initial collection values will be updated by the appropriate return argument values.

5. Value type mapping

Oracle-magpie library allows to map classes not only to oracle object types, but to simple sql scalar types as NUMBER,VARCHAR2,DATE,etc too. Such mappings are called value-mappings. There is exists predifined set of value-mappings, defined in the default-mappings.xml configuration file. Fifth sample case deals with a user-defined value-mapping of the java enum tutor.model.OrderState to the NUMBER sql type. The mapping is defined in the class-mappings.xml configuration file:


	<value-mapping class="tutor.model.OrderState">
					
			  <sql-type name="NUMBER"

			  to-oracle-method="tutor.model.OrderState.toNumber"

			  from-oracle-method="tutor.model.OrderState.fromNumber" />
	
	</value-mapping>

Attributes to-oracle-method and from-oracle-method point to the user-defined static methods which convert mapped class instances to the appropriate one for this sql type. Concrete signatures of this methods you can find in the documentation..