The syntax diagrams and the descriptions of the syntax elements are in the following sections.
- Syntax for running SQL DML statements without cursors, as well as DDL statements
- Syntax for running CALL statements
- Syntax for running SELECT statements
- Syntax for positioned updates and deletes
- Descriptions of the syntax elements
To understand the conventions that are used in the syntax diagrams, see How to read syntax diagrams.
Syntax for running SQL DML statements without cursors, as well as DDL statements
Use this syntax for declaring annotated methods that run DML statements that do not use cursors. You can also use this syntax to run DDL statements.
>>-@Update--(--sql--=--"--SQL-statement--"--)-------------------> >--+------------------------------------------------------------------------------------------+--> '-@Handler--(--+-parameterHandler--=--class-name--------------------------------------+--)-' +-resultHandler--=--class-name--+------------------------------------+-+ | '-,--parameterHandler--=--class-name-' | '-rowHandler--=--class-name--+------------------------------------+----' '-,--parameterHandler--=--class-name-' >--modifiers--return-type--method-name--(--+-----------------------+--+------------------------------------+--);->< '-handler--handler-name-' | .-,------------------------------. | | V | | '---parameter-type--parameter-name-+-'
Example
Here is an example of the syntax for a method that runs a SELECT statement:
@Select(sql = "SELECT PID, QUANTITY, LOCATION FROM HEATHR.INVENTORY WHERE PID = ?")
Inventory getInventory(String pid);Here is an example of the syntax for a method that runs an INSERT statement:
@Update(sql = "INSERT INTO HEATHR.INVENTORY (PID, QUANTITY, LOCATION) VALUES (?, ?, ?)")
int createInventory(String pid, int quantity, String location);Syntax for running CALL statements
Use this syntax for declaring annotated methods that run CALL statements.
Example
@Call(sql = "Call MYSCHEMA.BONUS_INCREASE( :p_bonusfactor, :p_bonusmaxsumfordept,
:p_deptswithoutnewbonuses, :p_countdeptsviewed, :p_countdeptsbonuschanged,
:p_errormsg )")
StoredProcedureResult callBONUS_INCREASE(Bonus_increaseParam parms);Syntax for running SELECT statements
Use this syntax for declaring annotated methods that run SELECT statements. If you use the @Cursor annotation, you must import java.sql.ResultSet in your interface.
- You can use an option only once within an @Cursor annotation.
Example
@Select (sql="select PUBLISHER_ID, BOOK_NAME, BOOK_ID, COUNTY
from BOOKS WHERE STATE='CALIFORNIA'")
@Cursor (concurrency = ResultSet.CONCUR_READ_ONLY,
type=ResultSet.TYPE_FORWARD_ONLY,
holdability=ResultSet.HOLD_CURSORS_OVER_COMMIT)
Iterator<Books> getBooksInCaliforniaAndNameCursor ();Syntax for positioned updates and deletes
Use this syntax when declaring annotated methods for performing positioned updates and deletes. If you use the @Cursor annotation, you must import java.sql.ResultSet in your interface.
For information about writing annotated methods for positioned updates and deletes, see Performing positioned updates and deletes.
Syntax of the method that runs the SELECT statement that opens and names a cursor for a positioned update or delete.
- You can use these options only once within an @Cursor annotation.
Syntax of the method that runs an UPDATE or DELETE statement that uses the named cursor
Example
Here is an example of the syntax for a method that runs a SELECT statement that opens a cursor for positioned updates and deletes:
@Select (sql="select PUBLISHER_ID, BOOK_NAME, BOOK_ID, COUNTY
from BOOKS WHERE STATE='CALIFORNIA' FOR UPDATE OF PUBLISHER_ID")
@Cursor (concurrency = ResultSet.CONCUR_UPDATABLE,
type=ResultSet.TYPE_FORWARD_ONLY,
holdability=ResultSet.HOLD_CURSORS_OVER_COMMIT,
cursorName="TESTCURSORITER")
Iterator<Books> getBooksInCaliforniaAndNameCursor ();Here is an example of the syntax for a method that runs an UPDATE statement that uses the cursor:
@Update (sql="update BOOKS SET PUBLISHER_ID = :publisher_id",
positionedCursorName = "TESTCURSORITER")
int updateBooksByUsingNamedCursor (Book b);Descriptions of the syntax elements
- @Call
- Specifies that the method runs an SQL CALL statement.
- sql
- Specifies the CALL statement for the method to run.
- @Cursor
- Specifies the attributes of the cursor for a SELECT statement.
You can use this annotation only if you are also using the @Select
annotation.
- allowStaticRowsetCursors
- Specifies whether the cursor that is associated with a SELECT statement is a rowset cursor when the statement is run statically, if you are using a DB2 Universal Database for z/OS® Version 8 (New Function Mode) or DB2 Version 9.1 for z/OS data source and the IBM® Data Server Driver for JDBC and SQLJ to connect to that data source.
- false
- Specifies that the cursor that is associated with a SELECT statement is not a rowset cursor when the statement is run statically.
- This is the default value.
- true
- Specifies that the cursor that is associated with a SELECT statement
is a rowset cursor when the statement is run statically. When you
set the value to true, you must observe these restrictions:
- The SQL statement that you want to allow to use a rowset cursors is a SELECT statement that you provide in a @Select annotation for the method.
- The return type of the annotated method does not direct pureQuery to select only one row from the database.
- None of the columns in query results are of the LOB or XML data types.
- The cursor that the statement opens is read-only. If a static
updatable cursor uses rowsets, the results are unpredictable.
pureQuery considers a statement as updatable if either of these conditions are true:
- The concurrency attribute is not set to java.sql.ResultSet.CONCUR_UPDATABLE.
- The SQL statement does not contain the FOR UPDATE clause.
- The annotated method is never invoked by a Java™ stored procedure.
- concurrency
- java.sql.ResultSet.CONCUR_READ_ONLY
- The integer constant that specifies that the cursor prevents updates while the query result is open.
- java.sql.ResultSet.CONCUR_UPDATABLE
- The integer constant that specifies that the cursor allows updates while the query result is open.
- cursorName
- Specifies the name of a cursor to use for positioned updates and deletes. The name cannot start with either DB2JCCCURSOR or DB_PDQ. The name must also conform to the rules for naming cursors for your database.
- holdability
- java.sql.ResultSet.CLOSE_CURSORS_AT_COMMIT
- The integer constant that specifies that the cursor is closed at the end of a transaction.
- java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT
- The integer constant that specifies that the cursor remains open at the end of a transaction.
- type
- java.sql.ResultSet.TYPE_FORWARD_ONLY
- The integer constant that specifies that the cursor can move forward only.
- java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE
- The integer constant that specifies that the cursor can move forward and backward. The query result gives a snapshot of the data.
- java.sql.ResultSet.TYPE_SCROLL_SENSITIVE
- The integer constant that specifies that the cursor can move forward and backward. The query result reflects changes that are made to the data while the cursor is held.
- @Handler
- Directs pureQuery to use a provided implementation class, instead
of pureQuery's default procedure, when running the annotated method.
- callHandlerWithParameters
- Specifies an implementation of the com.ibm.pdq.handlers.CallHandlerWithParameters<CAL> interface. If you specify this attribute, pureQuery uses the class to process the results of an SQL stored procedure CALL and create the object that is returned by the annotated method. The class's handleCall() method processes the results of the SQL stored procedure call and returns an object of type <CAL> that describes the results.
- parameterHandler
- Specifies an implementation of the com.ibm.pdq.handlers.ParameterHandler interface. If you specify this attribute, pureQuery uses the class to set the parameter values on the java.sql.PreparedStatement object for the SQL statement.
- If the annotated method calls a stored procedure, the ParameterHandler must register any OUT and INOUT parameters.
- resultHandler
- Specifies an implementation of the com.ibm.pdq.handlers.ResultHandler<RES> interface. If you specify this attribute, pureQuery uses the class to create the object that is returned by the annotated method. The class's handle() method processes the java.sql.ResultSet for an SQL statement and returns the contents in an Object of type <RES>.
- rowHandler
- Specifies an implementation of the com.ibm.pdq.handlers.RowHandler<ROW> interface. If you speicfy this attribute, pureQuery uses the class to create the object that represents each row of the query results that are returned by the annotated method. This class's handle() method processes one row from the java.sql.ResultSet for an SQL statement and returns the contents in an Object of type <ROW>.
- @Select
- Specifies that the method runs an SQL SELECT statement.
- sql
- Specifies the SELECT statement for the method to run.
- @Update
- Specifies that the method runs either an SQL data manipulation
language (DML) or data definition language (DDL) statement.
- sql
- Specifies the SQL statement for the method to run.
- positionedCursorName
- Specifies the name of a cursor to use for positioned updates and deletes. The name must be defined in the cursorName attribute of the @Select annotation.
- When you generate an implementation of the interface that declares the method that uses this attribute, pureQuery adds the required WHERE CURRENT OF clause to the UPDATE or DELETE statement.
- method-name
- Specifies the name of the interface method.
- parameter-type parameter-name
A repeating argument pair that shows the types and names of the arguments that are expected at method invocation time.
These parameters are matched with the parameter markers specified in the SQL statement according to the following rules. These parameters can be scalar types, bean classes, or Map objects. If the SQL uses the :name notation for parameter references, the first parameter-type must be a bean or a Map. The property names from the bean or Map are used for matching with the :name occurrences in the SQL string.
If the SQL uses the ? notation for parameter references, you can provide only scalar values.
If the ?n and ?n.name notation is used, the method parameters must be either all scalar types or a combination of and bean classes and Map objects.
The parameters specified are normally used only input variables. However, in the case of OUT or INOUT parameters on an SQL CALL statement, they might represent output variables. See the earlier section that discusses the CALL statement support for more details.
- return-type
- Specifies the return type of the method
This table shows the return types that are possible for methods that use the @Call, @Select, and @Update annotations.
Table 1. Key to the table of return types Abbreviation Meaning CAL CallHandlerWithParameters I Iterator L List M Map O Object R java.sql.ResultSet RES ResultHandler ROW RowHandler S String SPR StoredProcedureResult T The primitive type int, a wrapper class for a primitive Java type, a simple Object type, or a bean. T is allowed to be an int only with @Update annotations, and in @Update annotations T is required to be int. Table 2. Return types according to type of annotation Annotation @Call @Select @Update Return type CAL ✓ int ✓ int[] ✓ I<M<S,O>> ✓ ✓ I<T> ✓ ✓ L<M<S,O>> ✓ ✓ L<T> ✓ ✓ M<S,O> ✓ M<S,O>[] ✓ ✓ R ✓ RES ✓ ROW ✓ ROW[] ✓ L<ROW> ✓ I<ROW> ✓ SPR ✓ <T> ✓ <T>[] ✓ ✓ void ✓ ✓ Note: If you use a stored procedure that returns more than one ResultSet object and you want to use all of those objects, use the StoredProcedureResult return type or use an implementation of the CallHanderWithParameters<CAL> interface. All of the other return types that you can use with the @Call annotation return only the first ResultSet object from a call to a stored procedure.When you use an annotated method, do not specify that a @Select or @Call returns a primitive Java type, or an Array, List, or Iterator of a primitive Java type.
Information regarding SQL null values is lost whenever information queried from SQL is stored in a primitive Java type. Also, Java requires that a generic method that specifies generic <T> class of a <primitive Java type>.class must return an instance of the wrapper class that is appropriate for that primitive Java type.
For example, Java does not allow method invocations such as this:
where this is the definition of myGenericMethod:int tCount = myGenericMethod( int.class );<T> T myGenericMethod( Class<T> cl );The declared class of tCount must be Integer.Integer tCount = myQuery.myGenericMethod( Integer.class );This restriction is associated with use of generic methods even though there is no explicit use of generic method signatures (no use of <T> and return class information) in the method signatures that are defined in your annotated interface. This restriction is needed because the generated implementation of an annotated interface uses methods similar in some respects to the inline method API's. Specifically, the implementation makes use of generic methods.
- void
- Specifies that the method returns nothing.
- java.sql.ResultSet
- Specifies that a ResultSet object is returned. The ResultSet is read-only.
- StoredProcedureResult
- A StoredProcedureResult is valid only as the return type for an @Call annotated method that invokes a CALL statement. After execution of the method the StoredProcedureResult object can be used to process multiple query results returned by the stored routine or to access output parameters.
- int
- Specifies that the method returns a count of updated rows.
- int[]
- Specifies that the method returns an array of integers. Each element in the array records the update count for a single run of the SQL Data Manipulation Language (DML) statement. Use this return type for batch updates.
- List<Map<String,Object>>
- Specifies that the method returns a list of Map<String,Object> objects.
- See the description of returned Map<String,Object> objects
for details.
Any underlying database ResultSet object is closed.
- List<T>
- Specifies that a List object of type T is returned.
When the given SQL statement is a query, each element corresponds
to a row of the query result.
When a <T> object is returned by a CALL statement, the source of the data that populates the object is the first ResultSet object that the stored procedure returns.
When a <T> object is returned by a SELECT statement, the source of the data that populates the object is the result of the query.
- All query result rows are materialized when the method is called and any underlying database ResultSet object is closed.
- Map<String,Object>
- Specifies that a Map<String,Object> object
is constructed and returned. The return column labels of the query
result become the keys of the map. The column labels are converted
to lowercase for closer conformity with common Java coding style. The corresponding column
values from a row of the query result become the values of the Map object.
If more than one row qualifies, the value from the first row is returned.
When a Map<String,Object> object is returned by a CALL statement, the source of the data that populates the object is the first ResultSet object that the stored procedure returns.
When a Map<String,Object> object is returned by a SELECT statement, the source of the data that populates the object is the result of the query.
Any underlying database ResultSet object is closed.
- Map<String,Object>[]
- Specifies that an array of Map<String,Object> objects
is constructed and returned. See the description of returned Map<String,Object> objects.
Any underlying database ResultSet object is closed.
- <T>
Specifies that a scalar or bean is returned. A scalar could be a wrapper such as Double, or a String, Date, or Timestamp.
If the provided SQL statement is a query and if more than one row qualifies, the value from the first row is returned.
Any underlying database ResultSet object is closed.
- <T>[]
- Specifies that an array of type T is returned, such as Employee[], Integer[],
or String[].
- When the given SQL statement is a query, each element corresponds to a row of the query result.
- When a <T> object is returned by a CALL statement, the source of the data that populates the object is the first ResultSet object that the stored procedure returns.
- When the given SQL statement is not a query or a CALL statement that returns a ResultSet, each element of the array must be an int which is set to the statements returned update count.
- All query result rows are materialized when the method is called and any underlying database ResultSet object is closed.
- Iterator<Map<String,Object>>
Specifies that an Iterator of Map<String,Object> objects is returned. See the description of returned Map<String,Object> objects for details.
The query result rows are retrieved from the data source as the application executes each next() operation on the Iterator.
Iterators in pureQuery are of type ResultIterator. You must close them with the ResultIterator.close() method after you finish using them.
- Iterator<T>
Specifies that an Iterator object is returned, with each element corresponding to a row. The parameterized type T must be specified.
The query result rows are retrieved from the data source as the application executes each next() operation on the Iterator.
When a <T> object is returned by a CALL statement, the source of the data that populates the object is the first ResultSet object that the stored procedure returns.
When a <T> object is returned by a SELECT statement, the source of the data that populates the object is the result of the query.
Iterators in pureQuery are of type ResultIterator. You must close them with the ResultIterator.close() method after you finish using them.
- sql
- Provides the SQL statement to run when the associated method is invoked. The SQL statement must be valid for the target database. pureQuery also defines a number of parameter markers. These markers correspond to input parameters in inline or annotated methods.
- Some JDBC drivers require you to use the JDBC/ODBC escape sequence if you want to run a CALL statement.