You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@beehive.apache.org by ek...@apache.org on 2005/05/10 22:32:32 UTC
svn commit: r169518 [2/8] - in /incubator/beehive/trunk:
docs/forrest/src/documentation/content/xdocs/
docs/forrest/src/documentation/content/xdocs/system-controls/
docs/forrest/src/documentation/content/xdocs/system-controls/ejb/
docs/forrest/src/documentation/content/xdocs/system-controls/jdbc/
docs/forrest/src/documentation/content/xdocs/system-controls/jms/
docs/forrest/src/documentation/content/xdocs/system-controls/webservices/
docs/forrest/src/documentation/content/xdocs/tutorial/
system-controls/samples/ system-controls/samples/ejb/
system-controls/samples/ejb/client/ system-controls/samples/ejb/client/org/
system-controls/samples/ejb/client/org/apache/
system-controls/samples/ejb/client/org/apache/beehive/
system-controls/samples/ejb/client/org/apache/beehive/controls/
system-controls/samples/ejb/client/org/apache/beehive/controls/system/
system-controls/samples/ejb/client/org/apache/beehive/controls/system/ejb/
system-controls/samples/ejb/client/org/apache/beehive/controls/system/ejb/sample/
system-controls/samples/ejb/client/org/apache/beehive/controls/system/ejb/sample/client/
system-controls/samples/ejb/control/ system-controls/samples/ejb/control/org/
system-controls/samples/ejb/control/org/apache/
system-controls/samples/ejb/control/org/apache/beehive/
system-controls/samples/ejb/control/org/apache/beehive/controls/
system-controls/samples/ejb/control/org/apache/beehive/controls/system/
system-controls/samples/ejb/control/org/apache/beehive/controls/system/ejb/
system-controls/samples/ejb/control/org/apache/beehive/controls/system/ejb/sample/
system-controls/samples/ejb/control/org/apache/beehive/controls/system/ejb/sample/control/
system-controls/samples/ejb/ejb/ system-controls/samples/ejb/ejb/META-INF/
system-controls/samples/ejb/ejb/org/
system-controls/samples/ejb/ejb/org/apache/
system-controls/samples/ejb/ejb/org/apache/beehive/
system-controls/samples/ejb/ejb/org/apache/beehive/controls/
system-controls/samples/ejb/ejb/org/apache/beehive/controls/system/
system-controls/samples/ejb/ejb/org/apache/beehive/controls/system/ejb/
system-controls/samples/ejb/ejb/org/apache/beehive/controls/system/ejb/sample/
system-controls/samples/ejb/ejb/org/apache/beehive/controls/system/ejb/sample/bean/
system-controls/samples/jdbc/ system-controls/samples/jdbc/jdbcControlSample/
system-controls/samples/jdbc/jdbcControlSample/META-INF/
system-controls/samples/jdbc/jdbcControlSample/WEB-INF/
system-controls/samples/jdbc/jdbcControlSample/WEB-INF/src/
system-controls/samples/jdbc/jdbcControlSample/WEB-INF/src/controls/
system-controls/samples/jdbc/jdbcControlSample/WEB-INF/src/servlet/
system-controls/samples/jdbc/jdbcControlSample/WEB-INF/src/shared/
system-controls/samples/jms/ system-controls/samples/jms/src/
system-controls/samples/jms/src/org/
system-controls/samples/jms/src/org/apache/
system-controls/samples/jms/src/org/apache/beehive/
system-controls/samples/jms/src/org/apache/beehive/controls/
system-controls/samples/jms/src/org/apache/beehive/controls/system/
system-controls/samples/jms/src/org/apache/beehive/controls/system/jms/
system-controls/samples/jms/src/org/apache/beehive/controls/system/jms/samples/
system-controls/samples/webservice/
system-controls/samples/webservice/amazon/
system-controls/samples/webservice/amazon/junit/
system-controls/samples/webservice/amazon/schemas/
system-controls/samples/webservice/common/
system-controls/samples/webservice/common/src/
system-controls/samples/webservice/common/src/test/
system-controls/samples/webservice/google/
system-controls/samples/webservice/google/junit/
system-controls/samples/webservice/google/junit/model/
system-controls/samples/webservice/google/rpc_schemas/
system-controls/samples/webservice/google/servlet/
system-controls/samples/webservice/lib/
system-controls/samples/webservice/wsm_samples_client/
system-controls/samples/webservice/wsm_samples_client/doc_schemas/
system-controls/samples/webservice/wsm_samples_client/junit/
system-controls/samples/webservice/wsm_samples_client/junit/org/
system-controls/samples/webservice/wsm_samples_client/junit/org/wsm/
system-controls/samples/webservice/wsm_samples_client/junit/org/wsm/samples/
system-controls/samples/webservice/wsm_samples_client/junit/org/wsm/samples/client/
system-controls/samples/webservice/wsm_samples_client/junit/org/wsm/samples/client/header/
system-controls/samples/webservice/wsm_samples_client/junit/org/wsm/samples/client/webparam/
system-controls/samples/webservice/wsm_samples_client/rpc_schemas/
system-controls/samples/webservice/xmlbeans/
system-controls/samples/webservice/xmlbeans/junit/
system-controls/samples/webservice/xmlbeans/schemas/ system-controls/src/ejb/
Added: incubator/beehive/trunk/docs/forrest/src/documentation/content/xdocs/system-controls/jdbc/jdbcControlDevGuide.xml
URL: http://svn.apache.org/viewcvs/incubator/beehive/trunk/docs/forrest/src/documentation/content/xdocs/system-controls/jdbc/jdbcControlDevGuide.xml?rev=169518&view=auto
==============================================================================
--- incubator/beehive/trunk/docs/forrest/src/documentation/content/xdocs/system-controls/jdbc/jdbcControlDevGuide.xml (added)
+++ incubator/beehive/trunk/docs/forrest/src/documentation/content/xdocs/system-controls/jdbc/jdbcControlDevGuide.xml Tue May 10 13:32:29 2005
@@ -0,0 +1,1397 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "http://forrest.apache.org/dtd/document-v20.dtd">
+<document>
+ <header>
+ <title>Jdbc Control Developer's Guide</title>
+ </header>
+ <body>
+ <section>
+ <title>Jdbc Control Annotation's Reference</title>
+
+ <p> The Jdbc Control uses Java 1.5 annotations extensively. All annotations are defined in the
+ org.apache.beehive.controls.system.jdbc.JdbcControl interface. Whenever possible annotations are checked for validity during compile
+ time using an apt processor. The compile time checks include parsing the _statement_ member of the SQL annotation
+ to make sure it conforms to the parameter substitution syntax expected by the Jdbc Control.
+ </p>
+
+ <section>
+ <title>The ConnectionDataSource Annotation</title>
+ <p>The ConnectionDataSource annotation is a class-level annotation used to lookup a DataSource using the JNDI service.</p>
+ <table>
+ <tr><th>Member Name</th><th>Value Type</th><th>Value Required</th><th>Description</th></tr>
+ <tr><td>jndiName</td><td>String</td><td>Yes</td><td>A data source name which can be used for a JNDI lookup</td></tr>
+ <tr><td>jndiContextFactory</td><td>Class <? extends JndiContextFactory></td><td>No</td><td>A JNDI context factory</td></tr>
+ </table>
+ </section>
+ <section>
+ <title>The ConnectionDriver Annotation</title>
+ <p>The ConnectionDriver annotation is a class-level annotation used to connect directly to a database
+ instance using a connection URL. </p>
+
+ <table>
+ <tr><th>Member Name</th><th>Value Type</th><th>Value Required</th><th>Description</th></tr>
+ <tr><td>databaseDriverClass</td><td>java.lang.Class</td><td>Yes</td><td>The database driver class</td></tr>
+ <tr><td>databaseURL</td><td>String</td><td>Yes</td><td>The database connection URL</td></tr>
+ <tr><td>userName</td><td>String</td><td>No</td><td>The username to connect to the database with</td></tr>
+ <tr><td>password</td><td>String</td><td>No</td><td>The password associated with userName</td></tr>
+ <tr><td>properties</td><td>String</td><td>No</td><td>A comma seperated list of properties for the connection</td></tr>
+ </table>
+ </section>
+ <section>
+ <title>The ConnectionOptions Annotation</title>
+ <p>The ConnectionOptions annotation is a class-level annotation used to set options on a JDBC connection.
+ It is used in conjunction with the ConnectionDataSource and ConnectionDriver annotations but is not required.
+ </p>
+
+ <table>
+ <tr><th>Member Name</th><th>Value Type</th><th>Value Required</th><th>Description</th></tr>
+ <tr><td>readOnly</td><td>|boolean</td><td>No</td><td>If set to true tells the database to optimize the connection for read-only access (still can do updates, etc), defaults to false</td></tr>
+ <tr><td>resultSetHoldability</td><td>HoldabilityType</td><td>No</td><td>Specifies ResultSet cursor holdability, defaults to close cursors after commit</td></tr>
+ <tr><td>typeMappers</td><td>TypeMapper[]</td><td>No</td><td>Type mappers implement the java.sql.SQLData interface and handle mappings between SQL UDTs and Java classes</td></tr>
+ </table>
+ </section>
+ <section>
+ <title>The SQL Annotation</title>
+ <p>The SQL annotation is method annotation which specifies the SQL to send to the database as
+ well as any other options for the query.</p>
+
+ <table>
+ <tr><th>Member Name</th><th>Value Type</th><th>Value Required</th><th>Description</th></tr>
+ <tr><td>statement</td><td>String</td><td>Yes</td><td>The SQL statement to send to the database</td></tr>
+ <tr><td>arrayMaxLength</td><td>int</td><td>No</td><td>If the method return type is an array type, limit the size of the array to this value</td></tr>
+ <tr><td>batchUpdate</td><td>boolean</td><td>No</td><td>Defaults to false, JDBC 3.0 batch update</td></tr>
+ <tr><td>fetchSize</td><td>int</td><td>No</td><td>Performance hint for fetching ResultSet rows, defaults to zero, indicating db shoud determine fectch size.</td></tr>
+ <tr><td>fetchDirection</td><td>FetchDirection</td><td>No</td><td>Performance hint for fetching ResultSet rows, defaults to foward.</td></tr>
+ <tr><td>getGeneratedKeys</td><td>boolean</td><td>No</td><td>Defaults to false, JDBC 3.0 generated keys</td></tr>
+ <tr><td>generatedKeyColumnNames</td><td>String array</td><td>No</td><td>Defines column names of columns with generated keys to be returned</td></tr>
+ <tr><td>generatedKeyColumnIndexes</td><td>int array</td><td>No</td><td>Defines column indexes of columns with generated keys to be returned</td></tr>
+ <tr><td>iteratorElementType</td><td>Class</td><td>No</td><td>Defines type of class to iterate over when method return type is Iterator</td></tr>
+ <tr><td>maxRows</td><td>int</td><td>No</td><td>Limit the maximum number of rows returned by the database.</td></tr>
+ <tr><td>resultSetHoldabilityOverride</td><td>HoldabilityType</td><td>No</td><td>Overrides value set by ConnectionOptions holdability element for the duration of the method call.</td></tr>
+ <tr><td>resultSetMapper</td><td>Class</td><td>No</td><td>Defines a custom ResultSetMapper for use with this method</td></tr>
+ <tr><td>scrollableResultSet</td><td>ScrollType enumeration</td><td>No</td><td>Enables the return of scrollable ResultSet's, default is non-scrollable. See JdbcControl.jcs for ScrollType values.</td></tr>
+ <tr><td>typeMappersOverride</td><td>TypeMapper[]</td><td>No</td><td>Overrides typemapper's set in the ConnectionOptions annotation.</td></tr>
+ </table>
+ </section>
+ </section>
+ <section>
+ <title>Parameter Substitution in the SQL Annotation's Statement Member</title>
+
+ <p>You can use parameter substitution in the SQL annotation's _statement_ member to form a query dynamically.
+ The client calls the method on the Jdbc control, passing in values for the method's parameters, and
+ those parameter values are substituted into the SQL statement.</p>
+
+ <p>This topic describes substitution techniques and rules, including how to treat curly braces, how to
+ substitute whole SQL statements, SQL phrases, simple parameters, and indirect parameters.</p>
+ <section>
+ <title>Substitution Criteria</title>
+
+ <p>Substitution is subject to the following criteria:</p>
+ <ul>
+ <li><strong>Substitution matching is case sensitive.</strong> For example, the method parameter CustCity will not match the substitution pattern {custCity}.</li>
+
+ <li><strong>The type of the method parameter must be compatible with the type of the associated database field in the statement.</strong> If you attempt to substitute a Java String where the database expects a NUMBER, the statement will fail. For information on mapping between database types and Java types, see Mapping Database Field Types to Java Types in the Database Control.</li>
+
+ <li><strong>Substitution will not occur if the substitution pattern contains spaces.</strong> The Java Database Connectivity (JDBC) API allows access to built-in database functions via escapes of the form {fn user()}. If spaces occur in an item enclosed in curly braces ({}) item, the Database control treats the item as a JDBC escape and passes it on without substitution. For example, the custCity method parameter will not be substituted if the substitution is specified as {custCity } or { custCity}. For more information on JDBC escapes, please consult the documentation for your JDBC driver.</li>
+
+ <li><strong>When substituting date or time values, use the classes in the java.sql package.</strong> For example, attempting to substitute java.util.Date in a SQL Date field will not work. Use java.sql.Date instead.</li>
+ </ul>
+ </section>
+
+ <section>
+ <title>Substituting Simple Parameters</title>
+
+ <p>If you are substituting individual values into a WHERE, LIKE, or AND clause, you may substitute them directly
+ in the @SQL annotation's statement parameter without escaping the values with the {sql:} substitution syntax.</p>
+
+ <p>The following example illustrates simple parameter substitution:</p>
+
+ <source>
+@SQL(statement="SELECT name FROM customer WHERE city={custCity} AND state={custState}")
+public String [] getCustomersInCity( String custCity, String custState );
+ </source>
+
+ <p>The value of the custCity method parameter is substituted in the query in place of the {custCity} item, and the value of the custState method parameter is substituted in the query in place of the {custState} item.</p>
+ </section>
+
+ <section>
+ <title>Treatment of Curly Braces Within Literals</title>
+
+ <p>Curly braces (\{\}) within literals (strings within quotes) are ignored. This means statements like the
+ following will not work as you might expect. In the following example the curly braces have lost their
+ substitution functionality, because they appear within single quotes.</p>
+
+ <source>
+ @SQL( statement="SELECT name FROM employees WHERE name LIKE '%{partialName}%'")
+public String[] partialNameSearch(String partialName);
+ </source>
+
+ <p>Since the curly braces are ignored inside the literal string, the expected substitution of the
+ partialName Java String into the SELECT statement does not occur. To avoid this problem,
+ pre-format the match string before invoking the Jdbc control method, as shown below. Note
+ that single quotes are not included in the pre-formatted string because single quotes are
+ implicitly added to the substitution value when it is passed to the SQL query.</p>
+
+ <source>
+String partialNameToMatch = "%" + matchString + "%"
+String [] names = myJdbcControl.partialNameSeach(partialNameToMatch);
+ </source>
+
+ <p>Then pass the pre-formatted string to the Jdbc control:</p>
+
+ <source>
+@SQL ( statement="SELECT name FROM employees WHERE name LIKE {partialNameToMatch}")
+ public String[] partialNameSearch(String partialNameToMatch);
+ </source>
+ </section>
+
+ <section>
+ <title>Substituting Indirect Parameters</title>
+
+ <p>Assume the following class is declared and is accessible to the Database control:</p>
+
+ <source>
+public static class Customer
+{
+ public String firstName;
+ public String lastName;
+ public String streetAddress;
+ public String city;
+ private String state;
+ public String zipCode;
+ public String getState() {return state};
+}
+ </source>
+
+ <p>You can then refer to the members of the Customer class in the SQL statement, as shown in the following example: </p>
+
+ <source>
+@SQL( statement="SELECT name FROM customer WHERE city={cust.city} AND state={cust.state}")
+public String [] getCustomersInCity( Customer cust );
+ </source>
+
+ <p>Note: Class member variables and accessor (getXxx) methods must be public in order for the Database control to substitute them.</p>
+
+ <p>The dot notation is used to access the members of the parameter object.</p>
+
+ <p>The following list describes the precedence for resolving dot notations in substitutions given the substitution pattern {myClass.myMember}:</p>
+
+ <ul>
+ <li>If class myClass exposes public getMyMember() and setMyMember() methods, getMyMember() is called and the return value is substituted. For Boolean variables, substitute isMyMember() for getMyMemnber().</li>
+ <li>Else if class myClass exposes a public field named myMember, myClass.myMember is substituted.</li>
+ <li>Lastly, if class myClass implements java.util.Map, myClass.get("myMember") is called and the return value is substituted.</li>
+ <li>Any combination of these may exist, as in {A.B.C} where B is a public member of A and B has a public getC() method.</li>
+ </ul>
+
+ <p>If none of these conditions exist, the Jdbc control method will throw a com.bea.control.ControlException.</p>
+ </section>
+ <section>
+ <title>Generic Substitution</title>
+
+ <p>To pass a whole SQL statement to the database, use the substitution syntax shown in red.</p>
+
+ <source>
+@SQL(statement="{sql: sqlStatement}")
+public myRecordType myQuery( String sqlStatement );
+ </source>
+
+ <p>The SQL statement placed within the bracket syntax {sql: } is escaped and passed directly to the database.</p>
+
+ <p>You can use same substitution syntax to pass in any part of a SQL statement, such as a WHERE or LIKE clause, or a column name. In the following example, filtering phrases can be substituted into the base SQL statement.</p>
+
+ <source>
+@SQL(statement="SELECT * FROM CUSTOMER {sql: whereClause}")
+public myRecordType myQuery( String whereClause );
+ </source>
+
+ <p>In the following example, a column name is dynamically written to the SQL statement by means of the {sql: } bracket syntax.</p>
+
+ <source>
+@SQL(statement="SELECT SUM( {sql: colName} ) FROM MYTABLE")
+public int sumColumn(String colName);
+ </source>
+ </section>
+
+ <section>
+ <title>Referring to Functions in Substitution Statements</title>
+
+ <p>If your database supports internal functions, you can refer to the internal function within
+ the substitution syntax {sql: }. The following method refers to the function in(), by placing
+ the function call within the brackets {sql: }.</p>
+
+ <source>
+ @SQL( statement="SELECT * FROM customer WHERE {sql:fn in(custid,{customerIDs})}")
+ Customer[] callInternalFunction(Integer[] customerIDs);
+ </source>
+
+ <p>Not all databases and database drivers support internal functions within substitution brackets, for example, Oracle drivers do not support this scenario.</p>
+ </section>
+
+ <section>
+ <title>SQL Escapes Support</title>
+ <p>The SQL annotations statement member supports the use of the SQL Escape syntax within the SQL statement.
+ SQL Escapes follow the standard escape syntax and may contain parameter substitutions. The set of
+ supported escape keywords is:</p>
+ <ul>
+ <li> escape </li>
+ <li> fn </li>
+ <li> d </li>
+ <li> t </li>
+ <li> ts </li>
+ <li> call </li>
+ <li> ?= </li>
+ <li> oj </li>
+ </ul>
+
+ <p>The following examples illustrate some of the possible usages.</p>
+
+ <source>
+@SQL(statement="INSERT INTO USERS (creationDate, userName) VALUES({d {creationDateFormat}},{userName})
+public int addUser(String creationDateFormat, String userName) throws SQLException;
+ </source>
+
+ <source>
+@SQL(statement="INSERT INTO USERS (userId, userName) VALUES({?= sp_userId()},{userName})")
+public int addUser(String userName) throws SQLException;
+ </source>
+ </section>
+ </section>
+
+ <section>
+ <title>Invoking Stored Procedures With the Jdbc Control</title>
+
+ <p>The following topics explain how to call and create stored procedures with the Jdbc Control.</p>
+ <section>
+ <title>Calling Stored Procedures with IN Parameters</title>
+ <p>If the stored procedure contains only IN parameters, you can call the procedure by
+ passing method parameters to the procedure.</p>
+ <p>Assume the following procedure sp_updateData has been created on the database.</p>
+
+ <source>
+ CREATE OR REPLACE PROCEDURE sp_updateData
+ (pkID IN SMALLINT,
+ intVal IN INT)
+ AS
+ BEGIN
+ UPDATE CUSTOMER
+ SET NAME = intVal
+ WHERE CUSTID = pkID;
+ END sp_updateData;
+ </source>
+
+ <p>The following database control method calls the procedure sp_updateData and
+ passes two method parameters to the procedure.</p>
+
+ <source>
+ @SQL(statement="call sp_updateData({keyVal}, {intVal})"
+ void call_sp_updateCust(short keyVal, int intVal);
+ </source>
+
+ <p><strong>The method parameters are substituted into the procedure call using the curly brace substition syntax.</strong></p>
+
+ <p>If you are calling this stored procedure against a Sybase database, you must include curly braces around the stored procedure call. For Sybase, the annotation value should look like this:</p>
+
+ <source>
+@SQL(statement="{call sp_updateData({keyVal}, {intVal})}")
+ </source>
+ </section>
+ <section>
+ <title>Calling Stored Procedures with OUT Parameters</title>
+
+ <p>To call a procedure that contains OUT parameters:</p>
+ <ol>
+ <li>Use a SQLParameter Array as the parameter of the Java method that calls the procedure.</li>
+ <li>Use question marks as placeholders for the parameters within the procedure call.</li>
+ </ol>
+
+ <p>The SQLParameter class is an public inner class of JdbcControl.java, source follows:</p>
+
+ <source>
+ public static class SQLParameter {
+ public static final int IN = 1;
+ public static final int OUT = 2;
+ public static final int INOUT = IN | OUT;
+
+ public Object value = null;
+ public int type = Types.NULL;
+ public int dir = IN;
+
+ public SQLParameter(Object value) {
+ this.value = value;
+ }
+
+ public SQLParameter(Object value, int type) {
+ this.value = value;
+ this.type = type;
+ }
+
+ public SQLParameter(Object value, int type, int dir) {
+ this.value = value;
+ this.type = type;
+ this.dir = dir;
+ }
+
+ public Object clone() {
+ return new SQLParameter(value, type, dir);
+ }
+ }
+ </source>
+
+ <p>For example, assume that the following procedure sp_squareInt exists on the database.</p>
+
+ <source>
+ CREATE OR REPLACE PROCEDURE sp_squareInt
+ (field1 IN INTEGER, field2 OUT INTEGER) IS
+ BEGIN
+ field2 := field1 * field1;
+ END sp_squareInt;
+ </source>
+
+ <p>The following Java method will call the procedure sp_squareInt.</p>
+
+ <source>
+ @SQL(statement="{call sp_squareInt(?, ?)})"
+ void call_sp_squareInt(SQLParameter[] params) throws SQLException;
+ </source>
+
+ <p>Note that the method parameter params is not explicitly substituted into the procedure
+ call {call sp_squareInt(?, ?)}. The substitution syntax {call ...} has special
+ meaning within the @SQL statement annotation. When the substitution syntax
+ {call myStoredProc(?,?,?...)} is encountered, it automatically distributes the
+ elements of params into the procedure call.</p>
+
+ <p>The following shows how to construct an SQLParameter[] to call the procedure sp_squareInt.</p>
+
+ <source>
+ // Construct a SQLParameter[]
+ // to hold two SQLParameter objects
+ SQLParameter[] params = new SQLParameter[2];
+
+ // Construct two objects corresponding to the initial values of the
+ // stored procedure's two parameters.
+ Object obj0 = new Integer\(x);
+ Object obj1 = new Integer(0);
+
+ // The stored procedure sp_squareInt has two parameters:
+ // an IN parameter of data type INTEGER
+ // and an OUT parameter of data type INTEGER.
+ // params[0] is build to correspond to the IN parameter,
+ // params[1] is build to correspond to the OUT parameter.
+ params[0] = new SQLParameter(obj0, Types.INTEGER, SQLParameter.IN);
+ params[1] = new SQLParameter(obj1, Types.INTEGER, SQLParameter.OUT);
+
+ // Call the stored procedure.
+ // Note that the procedure does not return any value.
+ // Instead the result of the procedure is loaded directly into the OUT parameter,
+ // and, in turn, into params[1].
+ myJDBCControlFile.call_sp_squareInt(params);
+
+ // Get the result loaded directly into params[1].
+ return Integer.parseInt(params[1].value.toString());
+ </source>
+
+ <p>Note that Jdbc control method call_sp_squareInt does not return the result of the procedure call.
+ Instead the result of the procedure is loaded directly into the procedure's OUT parameter,
+ and this in turn is loaded directly into the corresponding SQLParameter object. To get the
+ result of the procedure, examine the .value property of the of the SQLParameter object.</p>
+
+ <source>
+ params[1].value
+ </source>
+ </section>
+ <section>
+ <title>Wrapping Procedures in Functions</title>
+
+ <p>An alternative to calling stored procedures directly is to wrap them in stored functions,
+ then call the wrapping function from your database control file. </p>
+
+ <p>For example the following Jdbc control method will create a function that wraps the
+ procedure sp_squareInt.</p>
+
+ <source>
+ /**
+ * Wraps a procedure in a function.
+ * /
+ @SQL(statement="CREATE OR REPLACE FUNCTION wrapProc (p1 INTEGER) RETURN INTEGER IS p2 INTEGER; BEGIN sp_squareInt(p1, p2); RETURN p2; END;")
+ public void create_wrapProc();
+ </source>
+
+ <p>Once the procedure has been wrapped, you can call the function, instead of calling the procedure directly.</p>
+
+ <source>
+ @SQL(statement="SELECT wrapProc({x}) FROM DUAL")
+ public int callWrapProc(int x, int y);
+ </source>
+ </section>
+ <section>
+ <title>Creating Strored Procedures</title>
+
+ <p>You can also send any DDL statement to the database through a database control method.</p>
+
+ <source>
+ /**
+ * A stored procedure that takes an integer, squares it, and loads
+ * the result into an OUT parameter.
+ * /
+ @SQL(statement="CREATE OR REPLACE PROCEDURE sp_squareInt (field1 IN INTEGER, field2 OUT INTEGER) IS BEGIN field2 := field1 * field1; END sp_squareInt; ")
+ void create_sp_squareInt() throws SQLException;
+ </source>
+
+ <p>Some XA database drivers contain restrictions on code that rollsback or commits a
+ transaction independently of the driver's transaction management. Since DDL
+ statements are implicitly transactional (COMMIT is called whether it or not
+ it explicitly appears in the DDL statement), you may have to suspend the
+ transaction with these XA drivers. For example if you send a DDL statement
+ using the Oracle XA thin client without suspending the transaction, the driver
+ throws the following exception:</p>
+
+ <p><strong>ORA-02089: COMMIT is not allowed in a subordinate session</strong></p>
+
+ <p>The following code suspends the transaction, executes the DDL statement, and then resumes the transaction.</p>
+
+ <source>
+ import javax.transaction.Transaction;
+ import weblogic.transaction.TransactionManager;
+ import weblogic.transaction.TxHelper;
+
+ TransactionManager tm = TxHelper.getTransactionManager();
+ Transaction saveTx = null;
+ try
+ {
+
+ // Suspend the transaction
+ saveTx = tm.forceSuspend();
+
+
+ // Execute the DDL statement
+ myDBControlFile.create_sp_squareInt();
+ }
+ finally
+ {
+
+ // Resume the transaction
+ tm.forceResume(saveTx);
+ }
+ </source>
+ </section>
+ </section>
+ <section>
+ <title>Stored Functions</title>
+
+ <p>This topic explains how to call and create stored functions using Jdbc control.</p>
+ <section>
+ <title>Calling Stored Functions</title>
+
+ <p>To call a stored function, place the function call in a @SQL statement annotation. When the
+ Java method callMyFunction is called the SQL statement in the @SQLl statement annotation
+ is passed to the database. Any data returned by the SQL statement is passed back to, and
+ returned by, the Java method.</p>
+
+ <source>
+ @SQL(statement="SELECT my_function FROM DUAL")
+ int callMyFunction() throws SQLException;
+ </source>
+
+ <p>In most cases, the Jdbc control automatically converts between the appropriate database data types
+ to the Java data types. For example, if the database function my_function returns the database
+ type INTEGER, the Java method callMyFunction() will automatically convert it into the Java type int.</p>
+
+ <p>You can substitute values dynamically into the database function call using curly braces.
+ The following method passes the parameter int x to the function call.</p>
+ </section>
+ <section>
+ <title>Creating Stored Functions</title>
+
+ <p>You can also send any DDL statement to the database through a Jdbc control method.</p>
+
+ <source>
+ /**
+ * A stored function that takes an integer, squares it, and returns the
+ * result through the database control method.
+ * /
+ @SQL(statement="CREATE OR REPLACE FUNCTION fn_squareInt (field1 IN INTEGER) RETURN INTEGER IS field2 INTEGER; BEGIN field2 := field1 * field1; RETURN field2; END fn_squareInt;")
+ void create_fn_squareInt() throws SQLException;
+ </source>
+
+ <p>Some XA database drivers contain restrictions on code that rolls back or commits a
+ transaction independently of the driver's transaction management. Since DDL
+ statements are implicitly transactional (COMMIT is called whether it or not
+ it explicitly appears in the DDL statement), you may have to suspend the
+ transaction with these XA drivers. For example if you send a DDL statement
+ using the Oracle XA thin client without suspending the transaction, the driver
+ throws the following exception:</p>
+
+ <p><strong>ORA-02089: COMMIT is not allowed in a subordinate session</strong></p>
+
+ <p>The following code suspends the transaction, executes the DDL statement, and then resumes the transaction.</p>
+
+ <source>
+ import javax.transaction.Transaction;
+ import weblogic.transaction.TransactionManager;
+ import weblogic.transaction.TxHelper;
+
+ TransactionManager tm = TxHelper.getTransactionManager();
+ Transaction saveTx = null;
+ try
+ {
+
+ // Suspend the transaction
+ saveTx = tm.forceSuspend();
+
+
+ // Execute the DDL statement
+ myDBControlFile.create_fn_squareInt();
+ }
+ finally
+ {
+
+ // Resume the transaction
+ tm.forceResume(saveTx);
+ }
+ </source>
+ </section>
+ </section>
+
+ <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 modelling
+ 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 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 file (JPF), 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 file. 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 file (JPF), 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 file. 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>Editting 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="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="NAME" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="ADDRESS" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="CITY" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="STATE" type="xsd:string" wld:JDBCType="CHAR" minOccurs="0" wld:TableName="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="ZIP" type="xsd:string" wld:JDBCType="VARCHAR" minOccurs="0" wld:TableName="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="AREA_CODE" type="xsd:string" wld:JDBCType="CHAR" minOccurs="0" wld:TableName="WEBLOGIC.CUSTOMER" nillable="true"><xsd:element>
+ <xsd:element name="PHONE" type="xsd:string" wld:JDBCType="CHAR" minOccurs="0" wld:TableName="WEBLOGIC.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 explicity 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 most 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, which is installed with WebLogic Server.</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>