You can use parameter substitution in the @jc:sql annotation's statement attribute to form a query dynamically. The client calls the method on the Database control, passing in values for the method’s parameters, and those parameter values are substituted into the SQL statement.
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.
Substitution is subject to the following criteria:
Substitution matching is case sensitive. For example, 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. 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.
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.
To pass a whole SQL statement to the database, use the substitution syntax shown in red.
/** * @jc:sql statement="{sql: sqlStatement}" */ public myRecordType myQuery( String sqlStatement );
The SQL statement placed within the bracket syntax {sql: } is escaped and passed directly to the database.
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.
/** * @jc:sql statement="SELECT * FROM CUSTOMER {sql: whereClause}" */ public myRecordType myQuery( String whereClause );
In the following example, a column name is dynamically written to the SQL statement by means of the {sql: } bracket syntax.
/** * @jc:sql statement="SELECT SUM( {sql: colName} ) FROM MYTABLE" */ public int sumColumn(String colName);
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: }.
/** * @jc:sql statement="SELECT * FROM customer WHERE {sql:fn in(custid,{customerIDs})}" */ Customer[] callInternalFunction(Integer[] customerIDs);
Not all databases and database drivers support internal functions within substitution brackets, for example, Oracle drivers do not support this scenario.
If you are substituting individual values into a WHERE, LIKE, or AND clause, you may substitute them directly in the @jc:sql annotation's statement parameter without escaping the values with the {sql:} substitution syntax.
The following example illustrates simple parameter substitution:
/** * @jc: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.
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.
/** * @jc: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 before invoking the Database 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.
String partialNameToMatch = "%" + matchString + "%" String [] names = myDBControl.partialNameSeach(partialNameToMatch);
Then pass the pre-formatted string to the Database control:
/** * @jc:sql statement:: * SELECT name * FROM employees * WHERE name LIKE {partialNameToMatch} * :: */ public String[] partialNameSearch(String 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 the Customer class in the SQL statement, as shown in the following example:
/** * @jc: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.
The following list describes the precedence for resolving dot notations in substitutions given the substitution pattern {myClass.myMember}:
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().
Else if class myClass exposes a public field named myMember, myClass.myMember is substituted.
Lastly, if class myClass implements java.util.Map, myClass.get("myMember") is called and the return value is substituted.
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.
If none of these conditions exist, the Database control method will throw a com.bea.control.ControlException.
For detailed information on calling Stored procedures and functions see the help topics Stored Procedures and Stored Functions.
Mapping Database Field Types to Java Types in the Database Control
Returning a Single Value from a Database Control Method
Returning a Single Row from a Database Control Method