Java Reference
In-Depth Information
Next, it sets the parameter to 1 (from the Integer passed to
queryForObject()
as
the second parameter above), and finally executes the prepared statement. It
then takes the resulting row, maps it to an object, and returns it.
While this may seem like low-level information, it is important to understand
what is happening here. One of the most frequently asked questions regarding
i
BATIS
is: “How do I use
LIKE
in my
WHERE
clauses?” Looking at the previous state-
ment, it is obvious why the parameter that is coming in has to have the wildcards
in it, and why they cannot be inserted into the
SQL
statement easily. There are
three possible solutions to that dilemma:
The value of the parameter passed in has to have the
SQL
wildcard charac-
ters in it.
■
The text to search for has to be part of a
SQL
expression (e.g.,
'%'
||
#value#
||
'%'
) that can be parameterized.
■
The substitution syntax (which is the next topic, in section 4.2.2) has to be
used instead.
■
4.2.2
Using inline parameters with the $ placeholders
Another way to use inline parameters is with the substitution ($) syntax, which is
used to insert a value directly into the
SQL
before it is turned into a parameterized
statement.
Use this approach with caution, as it may leave you open to
SQL
injection, and
it may cause performance
problems if overused.
This is one approach to handling
LIKE
operators. Here is an example:
<select id="getByLikeCity" resultClass="Account">
select
accountId,
username,
password,
firstName,
lastName,
address1,
address2,
city,
state,
postalCode,
country
from Account
where city like '%$value$%'
</select>
The difference between this statement and the last one is how i
BATIS
handles the
parameter passed into the statement. This statement is called the same way:
