You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@beehive.apache.org by cs...@apache.org on 2007/02/07 22:28:10 UTC
svn commit: r504697 [2/2] - in
/beehive/trunk/docs/forrest/release/src/documentation/content/xdocs: ./
system-controls/jdbc/
Added: beehive/trunk/docs/forrest/release/src/documentation/content/xdocs/system-controls/jdbc/jdbc-typemapping-guide.xml
URL: http://svn.apache.org/viewvc/beehive/trunk/docs/forrest/release/src/documentation/content/xdocs/system-controls/jdbc/jdbc-typemapping-guide.xml?view=auto&rev=504697
==============================================================================
--- beehive/trunk/docs/forrest/release/src/documentation/content/xdocs/system-controls/jdbc/jdbc-typemapping-guide.xml (added)
+++ beehive/trunk/docs/forrest/release/src/documentation/content/xdocs/system-controls/jdbc/jdbc-typemapping-guide.xml Wed Feb 7 13:28:09 2007
@@ -0,0 +1,869 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+ $Header:$
+ -->
+<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "http://forrest.apache.org/dtd/document-v20.dtd">
+<document>
+ <header>
+ <title>Jdbc Control Type Mapping Guide</title>
+ </header>
+ <body>
+
+ <section>
+ <title>Jdbc Control Return Type Mapping</title>
+ <p>When returning a value from a database, the Jdbc Control maps the JDBC ResultSet generated by the SQL to the calling
+ method's return type. These mappings can be characterized as follows:</p>
+
+ <section>
+ <title>Mapping a Single Value</title>
+
+ <p>This topic describes how to write methods that return a single value from the database. The example
+ provided represents a SELECT statement that requests only a single field of a single row. The
+ return value of the method should be an object or primitive of the appropriate type for that
+ field's data.</p>
+
+ <section>
+ <title>Returning a Single Column</title>
+
+ <p>The following example assumes a Customers table in which the field custid, representing
+ the customer ID, is the primary key. Given the customer ID, the method looks up a
+ single customer name. </p>
+
+ <source>
+@SQL(statement="SELECT name FROM customer WHERE custid={customerID}")
+public String getCustomerName(int customerID);
+ </source>
+
+ <p>In this example, the name field is of type VARCHAR, so the return value is declared as String.
+ The method's customerID parameter is of type int. When the SQL statement executes, this
+ parameter is mapped to an appropriate numeric type accepted by the database. </p>
+ </section>
+ <section>
+ <title>Returning an Update Count</title>
+
+ <p>Suppose that with the same database table a row is inserted; the following code could be
+ used to get the update count from the insert statement:</p>
+
+ <source>
+@SQL(statement="INSERT INTO customer VALUES ({customerName},{customerID})")
+public int insertCustomer(String customerName, int customerID);
+ </source>
+ </section>
+ </section>
+ <section>
+ <title>Mapping a Single Row</title>
+
+ <p>This topic describes how to write methods on a Jdbc control that return a single row from the database.
+ When you return a single row with multiple fields, your method must have a return type that can
+ contain multiple values--either an object that is an instance of a class that you have built for
+ that purpose, or a java.util.HashMap object.</p>
+
+ <p>If you know the names of the fields returned by the query, you will probably want to return a
+ custom object. If the number of columns or the particular field names returned by the query are
+ unknown or may change, you may choose to return a HashMap.</p>
+
+ <section>
+ <title>Returning an Object</title>
+
+ <p>You can specify that the return type of a Jdbc control method is a custom object, an instance
+ of a class whose members correspond to fields in the database table. In most cases, a
+ class whose members hold corresponding database field values is declared as an inner
+ class (a class declared inside another class) in the Jdbc control's JCX file. However,
+ it may be any Java class that meets the following criteria:</p>
+
+ <ul>
+ <li>The class must contain members with names that match the names of the columns that
+ will be returned by the query. Because database column names are case-insensitive,
+ the matching names are case-insensitive. The class may also contain other members,
+ but members with matching names are required.</li>
+ <li>The members must be of an appropriate type to hold a value from the corresponding
+ column in the database.</li>
+ <li>The class must be declared as public static if the class is an inner class.</li>
+ </ul>
+
+ <p>The following example declares a Customer class with members corresponding to fields in the
+ Customers table. The findCustomer method returns an object of type Customer:</p>
+
+ <source>
+public static class Customer
+{
+ public int custid;
+ public String name;
+ public Customer() {};
+}
+
+@SQL(statement="SELECT custid,name FROM customer WHERE custid={customerID})"
+Customer findCustomer(int customerID)
+ </source>
+
+ <p>Note: The Customer class above is simplified for the sake of clarity. For data modeling
+ classes, it is generally good design practice to have private fields, with public
+ setter and getter methods.</p>
+
+ <source>
+ public static class Customer
+ {
+ private int custid;
+ private String name;
+
+ public Customer() {};
+
+ public int getCustid()
+ {
+ return this.custid;
+ }
+
+ public void setCustid(int custid)
+ {
+ this.custid = custid;
+ }
+
+ public String getName()
+ {
+ return this.name;
+ }
+
+ public void setName(String name)
+ {
+ this.name = name;
+ }
+ }
+ </source>
+ </section>
+ <section>
+ <title>Handling Empty Values When Returning Objects</title>
+
+ <p>If a database field being queried contains no value for a given row, the class member is set to
+ null if it is an object and to 0 or false if it is a primitive. This may affect your
+ decisions regarding the types you use in your class. If the database field contained no data,
+ an Integer member would receive the value null, but an int member would receive the value 0.
+ Zero may be a valid value, so using int instead of Integer makes it impossible for subsequent
+ code to determine whether a value was present in the database.</p>
+
+ <p>If there is no column in the database corresponding to a member of the class, that member is also
+ set to null or 0, depending on whether the member is an primitive or an object.</p>
+
+ <p>If the query returns columns that cannot be matched to the members of the class, an exception is
+ thrown. If you don't know the columns that will be returned or if they may change, you should
+ consider returning a HashMap instead of a specific class. For more information, see the
+ Returning a HashMap section, below.</p>
+
+ <p>If no rows are returned by the query, the returned value of the Jdbc control method is null.</p>
+
+ <p>In the example given above, the method is declared as returning a single object of type Customer.
+ So even if the database operation returns multiple rows, only the first row is returned to
+ the method's caller. To learn how to return multiple rows to the caller,
+ see Mapping Multiple Rows.</p>
+ </section>
+ <section>
+ <title>Returning a HashMap or Map</title>
+
+ <p>If the number of columns or the particular column names returned by the query are unknown
+ or may change, you may choose to return a HashMap. To return a HashMap, declare the
+ return value of the method as java.util.HashMap, as shown here:</p>
+
+ <source>
+@SQL(statement="SELECT * FROM customer WHERE custid={custID})"
+public java.util.HashMap findCustomerHash(int custID);
+ </source>
+
+ <p>The HashMap returned contains an entry for each column in the result. The key for each
+ entry is the corresponding column name. The capitalization of the key names returned
+ by HashMap.keySet() depends on the database driver in use, but all keys are case-insensitive
+ when accessed via the HashMap's methods. The value is an object of the Java Database
+ Connectivity (JDBC) default type for the database column. </p>
+
+ <p>In the example above, the method is declared as returning a single object of type
+ java.util.HashMap. So even if the database operation returns multiple rows,
+ only the first row is returned to the method's caller.</p>
+
+ <p>To learn how return multiple rows to the caller, see Mapping Multiple Rows.</p>
+
+ <p>The following code allows you to access the name field of the returned record:</p>
+
+ <source>
+@Control
+private CustomerDBControl custDB;
+
+public String getCustomerName(int custID)
+{
+ java.util.HashMap hash;
+ String name;
+ hash = custDB.findCustomerHash(custID);
+ if( hash != null )
+ {
+ name = (String)hash.get("NAME");
+ }
+ else
+ {
+ name = new String("Customer not found");
+ }
+ return name;
+}
+ </source>
+
+ <p>If the query returns no rows, the returned value of the Jdbc control method is null.</p>
+ </section>
+ </section>
+
+ <section>
+ <title>Returning Multiple Rows from a Jdbc Control Method</title>
+
+ <p>This topic describes how to write a method on a Jdbc control that returns multiple rows from the
+ database. It describes the ways in which you can perform this operation, including returning
+ an array, returning an Iterator object, and returning a resultset.</p>
+ <section>
+ <title>Deciding How to Return Multiple Rows</title>
+
+ <p>A SELECT query may return one or more fields from multiple rows. A method on a Jdbc control
+ that returns multiple rows should have a return type that can store these values. The
+ Jdbc control method can return an array of objects, an Iterator, or a resultset.</p>
+
+ <p>Returning an array of objects is the easiest way to return multiple rows, so it is a good choice
+ if you think your users will prefer simplicity when using your control. However, when an
+ array is returned only one database operation is performed, and the entire resultset must
+ be stored in memory. For large resultsets, this is problematic. You can limit the size
+ of the returned array, but then you cannot provide a way for your user to get the remainder
+ of the resultset. To learn how to return an array of objects, see the Returning an Array of Objects
+ section, below.</p>
+
+ <p>While Iterators require more sophistication on the part of users of your control, they are more
+ efficient at handling large resultsets. An Iterator is accessed one element (row) at a time
+ via the Iterator's next() method, and it transparently makes repeated requests from the database
+ until all records have been processed. An Iterator does not present the risk of running out of
+ memory that an array presents. However, note that an Iterator returned from a database control
+ cannot be used within a Page Flow controller class, because an Iterator wraps a ResultSet object,
+ which is always closed by the time it is passed to the web-tier (where Page Flow files reside).
+ For this reason, your Jdbc control should return an array of objects (see above) when it is called
+ from a page flow controller. Also, an Iterator cannot be returned to a stateful process, because
+ stateful processes cannot maintain an open database connection (which Iterators require). To
+ learn about returning a java.util.Iterator, see the Returning an Iterator section, below.</p>
+
+ <p>Finally, you can choose to return a java.sql.ResultSet from a Jdbc control method. This grants
+ complete access to the results of the database operation to clients of your control, but it
+ requires knowledge of the java.sql package. Also, note that a ResultSet returned from a
+ Jdbc control cannot be used within a Page Flow controller, because a ResultSet object is
+ always closed by the time it is passed to the web-tier (where Page Flow files reside). For
+ this reason, your Jdbc control should provide an array of objects when it is called from a
+ Page Flow controller. To learn about returning a java.sql.ResultSet, see the Returning a Resultset
+ section, below.</p>
+ </section>
+
+ <section>
+ <title>Returning an Array of Objects</title>
+
+ <p>To return an array of objects, declare the method's return type to be an array of the object you
+ want to return. That type may be either a type you define, or it may be java.util.Hashmap.</p>
+
+ <p>Examples of both of these techniques are provided in the following sections.</p>
+ </section>
+
+ <section>
+ <title>Returning an Array of User-Defined Objects</title>
+
+ <p>The following example demonstrates how to return an array of objects whose type you have declared.
+ In this case, an array of Customer objects is returned:</p>
+
+ <source>
+public static class Customer
+{
+ public int custid;
+ public String name;
+}
+
+@SQL(statement="SELECT custid,name FROM customer WHERE custage<19", arrayMaxLength=100)
+Customer [] findAllMinorCustomers()
+ </source>
+
+ <p>This example returns all rows in which the custage field contains a value less than 19.</p>
+
+ <p>When returning an array of objects, the class declared as the return type of the method
+ must meet the criteria described in the Returning an Object section of the Returning
+ a Single Row from a Jdbc Control topic. If no rows are returned by the query, the returned
+ value of the Database control method is a zero-length array.</p>
+
+ <p>If you are returning an array from Jdbc control method, you can limit the size of the array
+ returned by setting the arrayMaxLength attribute of the @SQL annotation. This attribute
+ can protect you from very large resultsets that may be returned by very general queries.
+ If arrayMaxLength is present, no more than that many rows are returned by the method.</p>
+
+ <p>The default value of arrayMaxLength is 1024. For very large ResultSets you can avoid excessive
+ memory usage by returning an Iterator object as described below in the Returning an Iterator
+ section, below.</p>
+
+ </section>
+ <section>
+ <title>Returning an Array of HashMaps</title>
+
+ <p>Returning an array of HashMaps is analogous to returning an array of user-defined objects,
+ which is described in the preceding section.</p>
+
+ <p>The following example demonstrates returning an array of HashMaps:</p>
+
+ <source>
+public static class Customer
+{
+ public int custid;
+ public String name;
+ public Customer() {};
+}
+
+@SQL(statement="SELECT custid,name FROM customer WHERE custage<19", arrayMaxLength=100)
+java.util.HashMap [] findAllMinorCustomersHash()
+ </source>
+
+ <p>The array of HashMaps returned contains an element for each row returned, and each element of the
+ array contains an entry for each column in the result. The key for each entry is the corresponding
+ column name. The capitalization of the key names returned by HashMap.keySet() depends on the
+ database driver in use, but keys are case-insensitive when accessed via the HashMap's methods.
+ The value returned is an object of the Java Database Connectivity (JDBC) default type for the
+ database column.</p>
+
+ <p>If no rows are returned by the query, the returned value of the Jdbc control method is a zero-length array.</p>
+
+ <p>The following code shows how to access the name field of the returned records:</p>
+
+ <source>
+@Control
+private CustomerDBControl custDB;
+
+java.util.HashMap [] hashArr;
+String name;
+
+hashArr = custDB.findAllMinorCustomersHash();
+for(i=0; i<hashArr.length; i++)
+{
+ name = (String)hashArr[i].get("NAME");
+ // say hello to the all of the minors
+
+ System.out.println("Hello, " + name + "!");
+}
+ </source>
+ </section>
+
+ <section>
+ <title>Returning an Iterator</title>
+
+ <p>When you want to return an Iterator object, you declare the method's return type to be java.util.Iterator.
+ You then add the iteratorElementType attribute to the @SQL annotation to indicate the underlying
+ type that the Iterator will contain. The specified type may be either a type you define, or it may
+ be java.util.Hashmap. Examples of these techniques are given in the following sections. If your
+ method returns an Iterator, a compile time error will be generated if the iteratorElementType
+ annotation member has not been set.</p>
+
+ <p>The Iterator that is returned is only guaranteed to be valid for the life of the method call to which it is
+ returned. You should not store an Iterator returned from a Jdbc control method as a static member of
+ your web service's class, nor should you attempt to reuse the Iterator in subsequent method calls if
+ it is persisted by other means.</p>
+ </section>
+ <section>
+ <title>Returning an Iterator with a User-Defined Object</title>
+
+ <p>To return an Iterator that encapsulates a user-defined type, provide the class name as the
+ value of the iteratorElementType attribute of the @SQL annotation, as shown here:</p>
+
+ <source>
+public static class Customer
+{
+ public int custid;
+ public String name;
+ public Customer() {};
+}
+
+@SQL(statement="SELECT custid,name FROM customer" iteratorElementType=Customer.class)
+java.util.Iterator getAllCustomersIterator()
+ </source>
+
+ <p>The class specified in the iterator-element-type attribute must meet the criteria described
+ in Returning an Object.</p>
+
+ <p>The following example shows how to access the returned records:</p>
+
+ <source>
+CustomerJDBCControl.Customer cust;
+java.util.Iterator iter = null;
+iter = custDB.getAllCustomersIterator();
+while (iter.hasNext())
+{
+ cust = (CustomerJDBCControl.Customer)iter.next();
+ // say hello to every customer
+ System.out.println("hello, " + cust.name + "!");
+}
+ </source>
+
+ </section>
+ <section>
+ <title>Returning an Iterator with HashMap</title>
+ <p>To return an Iterator that encapsulates a HashMap, provide java.util.HashMap as the value of
+ the iterator-element-type attribute of the @SQL annotation, as shown here:</p>
+
+ <source>
+public static class Customer
+{
+ public int custid;
+ public String name;
+ public Customer() {};
+}
+
+@SQL(statement="SELECT custid,name FROM customer", iteratorElementType=java.util.HashMap.class)
+java.util.Iterator getAllCustomersIterator()
+ </source>
+
+ <p>The following code shows how to access the returned records:</p>
+
+ <source>
+java.util.HashMap custHash;
+java.util.Iterator iter = null;
+int customerID;
+String customerName;
+iter = custDB.getAllCustomersIterator();
+while (iter.hasNext())
+{
+ custHash = (java.util.HashMap)iter.next();
+ customerID = (int)custHash.get("CUSTID");
+ customerName = (String)custHash.get("NAME");
+}
+ </source>
+
+ <p>The HashMap contains an entry for each database column that is returned by the query. The key for
+ each entry is the corresponding column name, in all uppercase. The value is an object of
+ the JDBC default type for the database column.</p>
+ </section>
+ <section>
+ <title>Returning a ResultSet</title>
+
+ <p>The Jdbc control is designed to allow you to obtain data from a database in a variety of ways without
+ having to understand the classes in the java.sql package. If you and your users do understand
+ these classes, however, you can gain complete access to the java.sql.ResultSet object returned by a query.</p>
+
+ <p>If you want to return a resultset, you declare the method's return type to be java.sql.ResultSet. A client
+ of your control then accesses the resultset directly to process the results of the database operation.</p>
+
+ <p>The following example demonstrates returning a resultset:</p>
+
+ <source>
+@SQL(statement="SELECT * FROM customer")
+public java.sql.ResultSet findAllCustomersResultSet();
+ </source>
+
+ <p>The following code shows how to access the returned resultset:</p>
+
+ <source>
+java.sql.ResultSet resultSet;
+String thisCustomerName;
+resultSet = custDB.findAllCustomersResultSet();
+while (resultSet.next())
+{
+ thisCustomerName = new String(resultSet.getString("name"));
+}
+ </source>
+
+ <p>This example assumes the rows returned from the database operation include a column called name.</p>
+ </section>
+ </section>
+
+ <section>
+ <title>Returning Apache XMLBeans from a Jdbc Control</title>
+
+ <p><strong>This topic assumes a strong understanding of Apache XML Beans.</strong> For additional information about XML Bean see the Apache XML Beans Site http://xmlbeans.apache.org/.</p>
+
+ <p>The following topic explains how to return XMLBean types from custom Jdbc controls.</p>
+
+ <p>An XMLBean is essentially an XML document with a Java API attached to it. The API is used for parsing and manipulating
+ the data in the XML document. A typical XMLBean might represent database data in the following form.</p>
+
+ <source>
+<DOCTYPE XCustomer>
+<XCustomer xmlns="java:///database/customer_db" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
+ <XCustomerRow>
+ <CUSTID>1<CUSTID>
+ <NAME>Fred Williams<NAME>
+ <ADDRESS>123 Slugger Circle<ADDRESS>
+ <XCustomerRow>
+ <XCustomerRow>
+ <CUSTID>2<CUSTID>
+ <NAME>Marnie Smithers<NAME>
+ <ADDRESS>5 Hitchcock Lane<ADDRESS>
+ <XCustomerRow>
+ <XCustomerRow>
+ <CUSTID>3<CUSTID>
+ <NAME>Bill Walton<NAME>
+ <ADDRESS>655 Tall Timbers Road<ADDRESS>
+ <XCustomerRow>
+<XCustomer>
+ </source>
+
+ <p>The data can be accessed and manipulated using the XMLBean's API. For example, assume that custBean represents
+ the XML document above. The following Java code extracts the Fred Williams from the document.</p>
+
+ <source>
+String name = custBean.getXCustomer().getXCustomerRowArray(1).getNAME();
+ </source>
+
+ <p>Retrofitting database controls to return XMLBeans rather than RowSets, ResultSets, or Iterators, is a powerful
+ technique because there are few restrictions on where XMLBeans can be imported. This is not the case with
+ ResultSets and Iterators, which cannot be passed directly to web-tier classes (web services and page flows).
+ Also, data in XMLBean form is very easy to manipulate because there is a rich API attached to the XMLBean.</p>
+ <section>
+ <title>Creating a Schema</title>
+
+ <p>The first step in using XMLBean classes is creating a schema from which the XMLBean classes can be generated.
+ The schema you create for a database control must be capable of modeling the sorts of data returned
+ from the database.</p>
+
+ <p>If you write your own schema, at a minimum, the schema's elements should have the same names as the fields
+ in the database, which allows data returned from the database to be automatically mapped into the XMLBean.</p>
+
+ <p>When the XSD file is compiled, XMLBean types are generated that can be returned by the methods in the database control.</p>
+ </section>
+ <section>
+ <title>Editing Schemas to Create New "Document" Types</title>
+
+ <p>Note that only one of the generated types is a "Document" XMLBean type: XCustomerDocument. The other types,
+ XCustomerDocument.XCustomer and XCustomerDocument.XCustomer.XCustomerRow, can only be used with reference
+ to the "Document" type. This distinction is especially important because only "Document" types are eligible
+ for direct participation in a business process, or to be passed to a web service. For this reason you may
+ want to edit your schema to include "Document" types corresponding to other types in the Schema, especially
+ if you have a very large schema with many nested types defined in terms of a single "Document" type.</p>
+
+ <p>To generate a new Document type for some element, move that element so that it becomes a top-level element in
+ the schema. In the following example, the XCustomerRow element has been moved to the top-level of the
+ schema: its original position has been replaced with a reference element: <xsd:element ref="XCustomerRow"/>.</p>
+
+ <source>
+<xml version="1.0" encoding="UTF-8"?>
+<xsd:schema targetNamespace="java:///database/customer_db"
+ xmlns="java:///database/customer_db" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
+ xmlns:wld="http://www.bea.com/2002/10/weblogicdata"
+ elementFormDefault="qualified"
+ attributeFormDefault="unqualified">
+
+ <xsd:element name="XCustomer" wld:DefaultNamespace="java:///database/customer_db" wld:RowSet="true">
+ <xsd:complexType>
+ <xsd:choice maxOccurs="unbounded">
+ <xsd:element ref="XCustomerRow"/>
+ <xsd:choice>
+ <xsd:complexType>
+ <xsd:element>
+ <xsd:element name="XCustomerRow">
+ <xsd:complexType>
+ <xsd:sequence>
+ <xsd:element name="CUSTID" type="xsd:int" wld:JDBCType="INTEGER" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="NAME" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="ADDRESS" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="CITY" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="STATE" type="xsd:string" wld:JDBCType="CHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="ZIP" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="AREA_CODE" type="xsd:string" wld:JDBCType="CHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="PHONE" type="xsd:string" wld:JDBCType="CHAR" minOccurs="0" wld:TableName="MYSCHEMA.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:sequence>
+ <xsd:anyAttribute namespace="http://www.bea.com/2002/10/weblogicdata" processContents="skip">lt;xsd:anyAttribute>
+ <xsd:complexType>
+ <xsd:element>
+<xsd:schema>
+ </source>
+
+ <p>There are now two top-level elements, XCustomer and XCustomerRow, which compile into two
+ corresponding "Document" types: XCustomerDocument and XCustomerRowDocument.</p>
+ </section>
+
+ <section>
+ <title>Returning a XMLBean Types from Control Methods</title>
+
+ <p>Once you have generated XMLBean types that model the database data, you can import these types into
+ your Jdbc control.</p>
+
+ <source>
+import databaseCustomerDb.XCustomerDocument;
+import databaseCustomerDb.XCustomerDocument.XCustomer;
+import databaseCustomerDb.XCustomerDocument.Factory;
+ </source>
+
+ <p>XMLBean types can be returned from the control's methods.</p>
+
+ <source>
+@SQL(statement="SELECT custid, name, address FROM customer")
+public XCustomerDocument findAllCustomersDoc();
+ </source>
+
+ <p>The data returned from the query is automatically mapped into the XMLBean because the names of the
+ database fields match the fields of the XMLBean.</p>
+ </section>
+
+ </section>
+
+ <section>
+ <title>Mapping to a RowSet</title>
+
+ <p>This topic describes how to write methods on a Jdbc control that return a RowSet from the database.
+ Since the RowSet implementations provided by the JDK are part of the javax.sql, package the
+ JdbcControl does not support any of them by default. A sample ResultSetMapper for RowSet's is
+ included as part of the Jdbc Control's distribution but must be explicitly set in the @SQL
+ annotation in order to be invoked.</p>
+
+ <p>The DefaultRowSetResultSetMapper will create a javax.sql.CachedRowSetImpl. The following example
+ sets the resultSetMapper for the method getAllUsers() to the DefaultRowSetResultSetMapper
+ which enables the Jdbc control to map the ResultSet to a RowSet.</p>
+
+ <source>
+@SQL(statement="SELECT * FROM USERS", resultSetMapper=org.apache.beehive.controls.system.jdbc.DefaultRowSetResultSetMapper.class)
+public RowSet getAllUsers() throws SQLException;
+ </source>
+
+ <p>ResultSetMapper's can be created for other types of RowSets and almost any other type of mapping
+ from a result set to any object. See the [Jdbc Control Custom ResultSetMappers] topic for more information.
+ </p>
+ </section>
+
+ <section>
+ <title>Creating Customer Result Set Mappers</title>
+
+ <section>
+ <title>Overview</title>
+ <p>When the Jdbc Control maps a ResultSet to a return type, it first checks to see if a resultSetMapper
+ has been set in the method's @SQL annotation. If a mapper has been set, it is always the one used
+ for mapping the ResultSet to the method's return type. If resultSetMapper has not been
+ set, the Jdbc control looks for a _resultSetMapper_ based on the method's return type.</p>
+
+ <table>
+ <tr><th>Mapper Class Name</th><th>Method Return Type</th></tr>
+ <tr><td>DefaultIteratorResultSetMapper</td><td>Iterator</td></tr>
+ <tr><td>DefaultResultSetMapper</td><td>ResultSet</td></tr>
+ <tr><td>DefaultXmlObjectResultSetMapper</td><td>Classes derived from XmlObject</td></tr>
+ <tr><td>DefaultObjectresultMapper</td><td>Default to this mapper</td></tr>
+ </table>
+
+ </section>
+ <section>
+ <title>Creating a custom ResultSet Mapper</title>
+ <p>To create your own ResultSet mapper, create a new class which extends the abstract class
+ org.apache.beehive.controls.system.jdbc.ResultSetMapper. The mapToResultType() method does all the work
+ of mapping the ResultSet to the method's return type -- it will be invoked by the
+ JdbcControl when the control is ready to perform the mapping. Below is the code for
+ the ResultSetMapper class.</p>
+
+ <source>
+/**
+ * Extend this class to create new ResultSet mappers. The extended class will be invoked by the JdbcController
+ * when it is time to map a ResultSet to a method's return type.
+ *
+ * ResultSet mappers must be specified on a per method basis using the SQL annotation's resultSetMapper field
+ */
+public abstract class ResultSetMapper {
+
+ /**
+ * Map a ResultSet to an object type
+ *
+ * @param context A ControlBeanContext instance, see Beehive controls javadoc for additional information
+ * @param m Method assoicated with this call.
+ * @param resultSet Result set to map.
+ * @param cal A Calendar instance for time/date value resolution.
+ * @return The Object resulting from the ResultSet
+ * @throws Exception On error.
+ */
+ public abstract Object mapToResultType(ControlBeanContext context, Method m, ResultSet resultSet, Calendar cal)
+ throws Exception;
+
+ /**
+ * Can the ResultSet which this mapper uses be closed by the Jdbc control?
+ * @return true if the ResultSet can be closed by the JdbcControl
+ */
+ public boolean canCloseResultSet() { return true; }
+}
+ </source>
+ </section>
+ <section>
+ <title>An Example</title>
+
+ <p>Suppose you have a return type class which needs to do some special processing of a ResultSet.</p>
+
+ <source>
+public final class CustomerX
+{
+ private String _customerName;
+ private String _customerPhoneNumber;
+
+ public void setCustomerName(String firstName, String lastName) {
+ _customerName = firstName + " " + lastName;
+ }
+
+ public String getCustomerName() { return _customerName; }
+
+ public void setCustomerPhoneNumber(int areaCode, String phoneNumber) {
+ _customerPhoneNumber = "(" + areaCode + ")" + phoneNumber;
+ }
+
+ public String getCustomerPhoneNumber() { return _customerPhoneNumber; }
+}
+ </source>
+
+ <p>Let's assume the ResultSet contains the following columns:</p>
+ <table>
+ <tr><th>Column Name</th><th>Type</th></tr>
+ <tr><td>FIRST_NAME</td><td>Varchar</td></tr>
+ <tr><td>LAST_NAME</td><td>Varchar</td></tr>
+ <tr><td>AREA_CODE</td><td>INT</td></tr>
+ <tr><td>PHONE_NUMBER</td><td>Varchar</td></tr>
+ </table>
+
+ <p>Here's what the ResultSetMapper implementation might look like:</p>
+ <source>
+public final class CustomerXResultSetMapper extends ResultSetMapper {
+
+ public Object mapToResultType(ControlBeanContext context, Method m, ResultSet resultSet, Calendar cal)
+ throws Exception
+ {
+ resultSet.next();
+ CustomerX c = new CustomerX();
+ final String fName = resultSet.getString("FIRST_NAME");
+ final String lName = resultSet.getString("LAST_NAME");
+
+ c.setCustomerName(fName, lName);
+
+ final int aCode = resultSet.getInt("AREA_CODE");
+ final int phone = resultSet.get("PHONE_NUMBER");
+
+ c.setCustomerPhoneNumber(aCode, phone);
+
+ return c;
+ }
+}
+ </source>
+
+ <p>and finally the method and SQL annotation to invoke:</p>
+
+ <source>
+@SQL(statement="SELECT FIRST_NAME,LAST_NAME,AREA_CODE,PHONE_NUMBER FROM customers WHERE userId={userId}",
+ resultSetMapper=CustomerXResultSetMapper.class)
+public CustomerX getCustomer(String userId);
+ </source>
+ </section>
+ <section>
+ <title>Additional Examples</title>
+ <p>See the Jdbc Control Rowset Mapping topic for an example of using a ResultSet mapper to support
+ the RowSet return type.</p>
+ </section>
+ </section>
+ <section>
+ <title>Database -> Java Type Mapping Tables</title>
+ <section>
+ <title>PointBase 4.4 Type Mappings</title>
+
+ <p>The following table lists the relationships between database types and Java types for the
+ PointBase Version 4.4 database.</p>
+
+ <table>
+ <tr><th>Java Data Types</th><th>JDBC Data Types</th><th>PointBase SQL Data Types (Version 4.4)</th></tr>
+ <tr><td>boolean</td><td>BIT</td><td>boolean</td></tr>
+ <tr><td>byte</td><td>TINYINT</td><td>smallint</td></tr>
+ <tr><td>short</td><td>SMALLINT</td><td>smallint</td></tr>
+ <tr><td>int</td><td>INTEGER</td><td>integer</td></tr>
+ <tr><td>long</td><td>BIGINT</td><td>numeric/decimal</td></tr>
+ <tr><td>double</td><td>FLOAT</td><td>real</td></tr>
+ <tr><td>double</td><td>DOUBLE</td><td>double</td></tr>
+ <tr><td>float</td><td>FLOAT</td><td>float</td></tr>
+ <tr><td>java.math.BigDecimal</td><td>NUMERIC</td><td>numeric</td></tr>
+ <tr><td>java.math.BigDecimal</td><td>DECIMAL</td><td>decimal</td></tr>
+ <tr><td>String</td><td>CHAR</td><td>char</td></tr>
+ <tr><td>String</td><td>VARCHAR</td><td>varchar</td></tr>
+ <tr><td>String</td><td>LONGVARCHAR</td><td>clob</td></tr>
+ <tr><td>java.sql.Date</td><td>DATE</td><td>date</td></tr>
+ <tr><td>java.sql.Time</td><td>TIME</td><td>time</td></tr>
+ <tr><td>java.sql.Timestamp</td><td>TIMESTAMP</td><td>timestamp</td></tr>
+ <tr><td>byte[]</td><td>BINARY</td><td>blob</td></tr>
+ <tr><td>byte[]</td><td>VARBINARY</td><td>blob</td></tr>
+ <tr><td>byte[]</td><td>LONGVARBINARY</td><td>blob</td></tr>
+ <tr><td>java.sql.Blob</td><td>BLOB</td><td>blob</td></tr>
+ <tr><td>java.sql.Clob</td><td>CLOB</td><td>clob</td></tr>
+ </table>
+ </section>
+ <section>
+ <title>Oracle Type Mappings</title>
+
+ <p>Type Mappings for Oracle 8i</p>
+
+ <p>The following table lists the relationships between database types and Java types for the Oracle 8i database.</p>
+
+ <table>
+ <tr><th>Java Data Types</th><th>JDBC Data Types</th><th>Oracle SQL Data Types (Version 8i)</th></tr>
+ <tr><td>boolean</td><td>BIT</td><td>NUMBER</td></tr>
+ <tr><td>byte</td><td>TINYINT</td><td>NUMBER</td></tr>
+ <tr><td>short</td><td>SMALLINT</td><td>NUMBER</td></tr>
+ <tr><td>int</td><td>INTEGER</td><td>NUMBER</td></tr>
+ <tr><td>long</td><td>BIGINT</td><td>NUMBER</td></tr>
+ <tr><td>double</td><td>FLOAT</td><td>NUMBER</td></tr>
+ <tr><td>float</td><td>REAL</td><td>NUMBER</td></tr>
+ <tr><td>double</td><td>DOUBLE</td><td>NUMBER</td></tr>
+ <tr><td>java.math.BigDecimal</td><td>NUMERIC</td><td>NUMBER</td></tr>
+ <tr><td>java.math.BigDecimal</td><td>DECIMAL</td><td>NUMBER</td></tr>
+ <tr><td>String</td><td>CHAR</td><td>CHAR</td></tr>
+ <tr><td>String</td><td>VARCHAR</td><td>VARCHAR2</td></tr>
+ <tr><td>String</td><td>LONGVARCHAR</td><td>LONG</td></tr>
+ <tr><td>java.sql.Date</td><td>DATE</td><td>DATE</td></tr>
+ <tr><td>java.sql.Time</td><td>TIME</td><td>DATE</td></tr>
+ <tr><td>java.sql.Timestamp</td><td>TIMESTAMP</td><td>DATE</td></tr>
+ <tr><td>byte[]</td><td>BINARY</td><td>NUMBER</td></tr>
+ <tr><td>byte[]</td><td>VARBINARY</td><td>RAW</td></tr>
+ <tr><td>byte[]</td><td>LONGVARBINARY</td><td>LONGRAW</td></tr>
+ <tr><td>java.sql.Blob</td><td>BLOB</td><td>BLOB</td></tr>
+ <tr><td>java.sql.Clob</td><td>CLOB</td><td>CLOB</td></tr>
+ </table>
+ </section>
+ <section>
+ <title>Derby Type Mappings</title>
+
+ <p>Type Mappings for Derby 10</p>
+ <table>
+ <tr><th>Java Data Types</th><th>JDBC Data Types</th><th>Derby SQL Data Types (Version 4.4)</th></tr>
+ <tr><td>long</td><td>BIGINT</td><td>BIGINT</td></tr>
+ <tr><td>java.sql.Blob</td><td>BLOB</td><td>BLOB</td></tr>
+ <tr><td>String</td><td>CHAR</td><td>CHAR</td></tr>
+ <tr><td>java.sql.Clob</td><td>CLOB</td><td>CLOB</td></tr>
+ <tr><td>java.sql.Date</td><td>DATE</td><td>DATE</td></tr>
+ <tr><td>java.math.BigDecimal</td><td>DECIMAL</td><td>DECIMAL,NUMERIC</td></tr>
+ <tr><td>double</td><td>DOUBLE</td><td>DOUBLE [PRECISION]</td></tr>
+ <tr><td>float</td><td>FLOAT</td><td>float</td></tr>
+ <tr><td>int</td><td>INTEGER</td><td>integer</td></tr>
+ <tr><td>String</td><td>LONGVARCHAR</td><td>LONG VARCHAR</td></tr>
+ <tr><td>short</td><td>SMALLINT</td><td>SMALLINT</td></tr>
+ <tr><td>java.sql.Time</td><td>TIME</td><td>time</td></tr>
+ <tr><td>java.sql.Timestamp</td><td>TIMESTAMP</td><td>timestamp</td></tr>
+ <tr><td>String</td><td>VARCHAR</td><td>VARCHAR</td></tr>
+ </table>
+ </section>
+ </section>
+ <section>
+ <title>New Features and Enhancements</title>
+ <p>JDBC 3.0 feature support as well as other new features are being added to the JdbcControl on a
+ regular basis. Here some of the latest features which have been added:</p>
+
+ <ul>
+ <li>Support for custom mapping of SQL UDTs</li>
+ <li>Support for ResultSet holdability (connection and statement level support)</li>
+ <li>Support for fetchSize and direction</li>
+ <li>Support for scrollable ResultSets</li>
+ <li>Retrieval of auto-generated keys</li>
+ <li>BOOLEAN and DATALINK data types</li>
+ <li>Blob and Clob type support</li>
+ <li>Batch Update support</li>
+ </ul>
+ </section>
+ </section>
+ </body>
+
+</document>
Propchange: beehive/trunk/docs/forrest/release/src/documentation/content/xdocs/system-controls/jdbc/jdbc-typemapping-guide.xml
------------------------------------------------------------------------------
svn:eol-style = native