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.
- 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:
- 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.
- 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.
- 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.
- If no get(String) method is found, an error is
reported.
- If a parameter is used as an output parameter, pureQuery follows
this process:
- 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.
- 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.
- 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.
- 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.