Skip to main content

6.2 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}

Basic 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 Evaluation​

Named arguments use OGNL for nested properties and array/collection indexes. Wrap complex expressions in #{...} to avoid confusing spaces or operators with SQL boundaries. Expressions must be application-defined, not arbitrary user-supplied OGNL text.

Nested Properties​

Example: Extract a nested value
{
  "p": {
    "name": "Dave",
    "cfg_id": {
      "array": [
        {"age": 10},
        {"age": 40}
      ]
    }
  }
}

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

Syntax 1: Colon syntax
select * from user_table where age > :p.cfg_id.array[1].age order by id
Syntax 2: Curly brace syntax
select * from user_table where age > #{p.cfg_id.array[1].age} order by id

Array and Collection Indexing​

OGNL expressions can access List and array elements directly via square brackets [index].

Example: Collection index access
Map<String, Object> params = new HashMap<>();
params.put("ages", Arrays.asList(30, 35, 40));
params.put("names", new String[] { "Alice", "Bob" });

// Using colon syntax
jdbcTemplate.queryForList(
    "select * from users where age = :ages[0] and name = :names[0]", params);

// Using curly brace syntax
jdbcTemplate.queryForList(
    "select * from users where age = #{ages[0]} and name = #{names[0]}", params);

Argument Reuse​

The same named argument can appear multiple times in SQL. dbVisitor automatically binds it to the same value.

Example: Using the same argument name twice
insert into users (id, name, email) values (#{id}, #{name}, #{name})

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​

A space after a field colon separates it from a named parameter:

{"name": :name}

The first colon is literal; :name binds a value. You can also use {"name":#{name}} or {"name":?} without adding a space.

Use \:name or \&name for literal :name or &name. See Escaping Parameter Markers for rules and examples.

Additional special handling to note:

  • PostgreSQL type cast :: is not recognized as an argument, so you can safely use column::integer syntax.
  • Quoted content Argument syntax inside single quotes '...' and double quotes "..." is skipped.
  • SQL comments Argument syntax inside /* ... */ and -- ... is skipped.
  • Escaping Use \#{...}, \${...}, \@{...} to escape them as plain text.