Hint

This article is generated by AI translation.

Named Arguments

Use :name, &name, or #{...} to name arguments in SQL.

select * from users where id > :id   and name = :name
select * from users where id > &id   and name = &name
select * from users where id > #{id} and name = #{name}

Usage

Example 1: Use a Map as the argument container

Map<String, Object> args = CollectionUtils.asMap(
        "id", 2,
        "name", "Dave"
);
jdbcTemplate.queryForList("select * from users where id > :id and name = :name", args);

Example 2: Use a Bean as the argument container

public class User {
  private int     id;
  private String  name;

  public User(int id, String name) {
    this.id = id;
    this.name = name;
  }
  // getters and setters omitted
}

User args = new User(2, "Dave");
jdbcTemplate.queryForList("select * from users where id > :id and name = :name", args);

OGNL Extraction

OGNL expressions can access object properties and methods to extract more complex arguments.

Example: Extract a nested value

{
  "p": {
    "name": "Dave",
    "cfg_id": {
      "array": [
        {"age": 10},
        {"age": 40}  <<< use this value
      ]
    }
  }
}

Use the OGNL expression p.cfg_id.array[1].age to extract the argument.

Form 1

select * from user_table where age > :p.cfg_id.array[1].age order by id

Form 2

select * from user_table where age > #{p.cfg_id.array[1].age} order by id

Argument Options

Example 1: Specify a TypeHandler

select * from users where
  id   > #{id,typeHandler=net.hasor.dbvisitor.types.handler.number.LongTypeHandler}
  and
  name = #{name,typeHandler=net.hasor.dbvisitor.types.handler.string.StringTypeHandler}

Example 2: Receive a stored procedure OUT argument

{call proc_bigint(#{out,mode=out,jdbcType=bigint})}

More info

• The :name and &name syntaxes do not support argument options.
• For more argument options, see Argument Options.

Ignore Rule

If a colon : is followed immediately by whitespace (space, newline, tab, etc.), dbVisitor ignores argument parsing for that colon and treats it as a normal character. This makes it easy to write JSON or MongoDB-like queries directly in SQL without escaping colons.

-- The colon is followed by a space, so it is not treated as an argument
select * from table where config = '{ "key": "value" }'