Parameter markers in SQL statements

There are several different markers that you can use in SQL statements to refer to parameters in methods that run against databases.

An SQL statement can use the values that are passed at run time to the parameters of the method that uses the statement. SQL statements denote these parameters with markers. Each marker corresponds to a parameter in a method.

When a method is invoked, the values for these parameters are obtained from the arguments provided by the invoking application and used during execution of the SQL statement.

There are several types of markers that you can use in SQL statements:

?
These markers match a method's parameters by position.
  • When used in an inline method, the nth ? marker matches the (n-1)th entry in the Object[ ] that contains the values from the Object... parameters.
  • When used in an annotated method, the nth ? marker matches the nth parameter of the annotated method.
      @Select(sql="select DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION from DEPARTMENT where DEPTNO = ?")
      Iterator<Department> getDepartment(String deptno);
      @Update(sql="insert into DEPARTMENT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION) values( ?, ?, ?, ?, ?)")
      int createDepartment(String deptno, String deptname, String mgrno, String admrdept, String location);
      @Update(sql="update DEPARTMENT set DEPTNO = ?, DEPTNAME = ?, MGRNO = ?, ADMRDEPT = ?, LOCATION = ? where DEPTNO = ?")
      int updateDepartment(String deptno, String deptname, String mgrno, String admrdept, String location, String deptno_K);
If you use this type of marker, all of the other markers in the SQL statement must also be of this type.
?n
These markers, where n represents a positive integer literal, match a method's parameters by position.
  • When used in an inline method, ?n matches the (n-1)th entry in the Object[ ] that contains the values from the Object... parameters
  • When used in an annotated method, ?n matches the nth parameter of the annotated method.
       @Select(sql="SELECT * FROM Employee WHERE salary>?1")
       Iterator<Customer> getCustomersInRegion(BigDecimal r);
You can use the same ordinal marker more than once in the SQL statement.
:name
These markers are a shortened version of ?1.name.
?n.name
These markers refer to method parameters ?n, which must refer to java.util.Map<String> objects or beans. name must refer to a property within a java.util.Map<String> object or a bean.
If the identified parameter is a java.util.Map<String>, the methods that pureQuery invokes depend on how the parameter is used:
  • Input parameter: pureQuery invokes the Map's Object get(String) method to acquire the value to pass to the SQL statement. The value of the String in the call to the get() method is the name that follows ?n.
  • Output parameter: pureQuery invokes the Map's Object put(String, Object) method to return the value that is associated with the named key. The value of the key String in the call to put() is the name that follows ?n.
  • Input parameter and output parameter: Before the SQL statement is run, pureQuery invokes the Map's Object get(String) method. After the SQL statement is run, pureQuery invokes the Map's Object put(String, Object) method.

Because the Strings that are used as keys all originate from the names in parameter markers (whether prefixed with : or ?n), the name is considered a Java-sourced identifier and is passed to get() and mutator methods in its original case.

If the identified parameter is a bean, then the resolution process is more complex.
  • If a parameter is used as an input parameter, pureQuery follows this process:
    1. The bean interface is examined for a public getXXX() method where the XXX portion of the identifier is generated from the name that follows ?n after first upper-casing the first character of that name. If such a method exists, it is used to acquire the value to pass to the SQL statement.
    2. If no getXXX() method is found, the bean is examined for a public property whose name matches the name that follows ?n. If such a property exists, it is accessed directly to acquire the value to pass to the SQL statement.
    3. If no public property is found, the bean interface is examined for an public Object get(String) method. If such a method exists, it is used to acquire the value to pass to the SQL statement, where the value passed for the String parameter is the name that follows ?n.
    4. If no get(String) method is found, an error is reported.
  • If a parameter is used as an output parameter, pureQuery follows this process:
    1. The bean interface is examined for an public setXXX() method where the XXX portion of the identifier is generated from the name that follows ?n after first upper-casing the first character of that name. If such a method exists, it is used to update the associated property of the bean parameter.
    2. If no setXXX() method is found, the bean is examined for a public property whose name matches the name that follows ?n. If such a property exists, it is modified directly to update the associated property of the bean.
    3. If no public property is found, the bean interface is examined for an public void set(String, Object) method. If such a method exists, it is used to update the associated property of the bean, where the value passed for the String parameter is the name that follows ?n.
    4. If no set(String, Object) method is found, an error is reported.
  • If a parameter is used both as an input parameter and an output parameter, the process of resolving the parameter described above for input is performed before the SQL statement is executed, and the process described above for output is preformed after the SQL statement is executed.

Again, because the Strings that are used as keys all originate from the names in parameter markers (whether prefixed with : or ?n), the name is considered a Java-sourced identifier and is passed to the get(String) and set(String) methods in its original case.

Maps and beans are not used only as a source of parameters but can also be constructed from rows selected by a query. When pureQuery constructs Maps or beans from rows, the String values used for the keys in the get(String) methods, set(String, Object) methods, and put(String, Object) methods are SQL-sourced identifiers (from the labels of selected columns) and are in lowercase. If you implement or use a Bean's optional get(String) methods or set(String, Object) methods, or a Map's get(String) and put(String, Object) methods, you must be aware of the case of the identifiers that are used as keys.


Feedback