Parameter Substitution in @jws:sql Statements
This topic discusses parameter substitution in the @jws:sql tags associated with methods of a Database control.
To learn about Database controls, see Database Control: Using a Database from Your Web Service.
To learn about creating a Database control, see Creating a New Database Control.
Substitution is subject to the following criteria:
Substitution matching is case sensitive. The method parameter CustCity will not match the substitution pattern {custCity}.
The type of the method parameter must be compatible with the type of the associated database field in the statement. 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.
Substitution will not occur if the substitution pattern contains spaces, as in {custCity } or { custCity}. The Java Database Connectivity API, JDBC, allows access to built-in database functions via escapes of the form {fn user()}. If spaces occur in a {} item, the Database control will treat the item as a JDBC escape and will pass it on without substitution. For more information on JDBC escapes, please consult the documentation for your JDBC driver.
When substituting date or time values, use the classes in the java.sql package. For example, attempting to substitute java.util.Date in a SQL Date field will not work. Use java.sql.Date instead.
If the parameters of a Database control method are primitives or simple types, you may refer to them directly in a {} substitution in the string value of the @jws:sql tag's statement parameter.
The following example illustrates simple parameter substitution:
/** * @jws:sql statement="SELECT name FROM customer WHERE city={custCity} AND state={custState}" */ public String [] getCustomersInCity( String custCity, String custState );
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.
Substitutions are subject to the criteria described in Substitution Criteria.
Curly braces "{}" within literals (strings within quotes) are ignored. This means statements like the following will not work as you might expect:
/** * @jws:sql statement:: * SELECT name * FROM employees * WHERE name LIKE '%{partialName}%' * :: */ public String[] partialNameSearch(String partialName);
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 in the JWS file before invoking the Database control method, as shown below:
String partialNameToMatch = "'%" + matchString + "%'" String [] names = myDBControl.partialNameSeach(partialNameToMatch);
Assume the following class is declared and is accessible to the Database control.
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}; }
You can then refer to the members of a Customer parameter in the {} substitutions, as shown in the following example:
/** * @jws:sql statement="SELECT name FROM customer WHERE city={cust.city} AND state={cust.state}" */ public String [] getCustomersInCity( Customer cust );
Note: Class member variables and accessor (getXxx) methods must be public in order for the Database control to substitute them.
The dot notation is used to access the members of the parameter object.
Given the substitution pattern {myClass.myMember}, the precedence for resolving dot notations in substitutions is:
If class myClass exposes public getMyMember() and setMyMember() methods, getMyMember() will be called and the return value will be substituted. For boolean variables, substitute isMyMember() for getMyMemnber().
Else if class myClass exposes a public field named myMember, myClass.myMember will be substituted.
Lastly if class myClass implements java.util.Map, myClass.get("myMember") will be called and the return value will be substituted.
Any combination of these may exist, for example {A.B.C} where B is a public member of A and B has a public getC() method.
If none of these conditions exist, the Database control method will throw a weblogic.jws.control.ControlException.
Substitutions are subject to the criteria described in Substitution Criteria.
Mapping Database Field Types to Java Types in the Database Control
Returning a Single Value from a Database Control Method
.