Hint

This article is generated by AI translation.

Multiple Results

The multipleExecute series of methods in JdbcTemplate solves the problem of SQL statements producing multiple values that are hard to identify and retrieve.

When It Happens

• Sending multiple unsplit SQL statements in a single request.
  Example: send the entire block to MySQL

  set @userName = convert(? USING utf8);
  select * from test_user where name  = @userName;
  select * from test_user where name != @userName;

  info

  Some databases (e.g., MySQL) require enabling multi-statement support, such as allowMultiQueries=true in the JDBC URL.

• Stored procedures returning multiple result sets, intentionally or not.
  Example: MySQL procedure yielding two result sets

  create procedure proc_multi_result(in userName varchar(200))
  begin
    select * from test_user where name  = @userName;
    select * from test_user where name != @userName;
  end;

• Other situations that produce multiple result sets.

Usage

One argument, two result sets: matches vs mismatches

String multipleSql = " set @userName = convert(? USING utf8);" +
                     " select * from test_user where name  = @userName;" +
                     " select * from test_user where name != @userName;";
Map<String, Object> result = jdbc.multipleExecute(multipleSql, "muhammad");

• See Arguments for all supported binding styles.

Return Value

• By default, each result set is represented as List/Map. The example above yields a List containing two Map entries.
• You can embed rules in SQL to control how each result set is handled.

1. Annotate statements with @{resultSet} rules

➊ set @userName = convert(? USING utf8);           ➋ @{resultUpdate,name=upd}
➌ select * from test_user where name  = @userName; ➍ @{resultSet,name=res1,javaType=net.demo.dto.User}
➎ select * from test_user where name != @userName; ➏ @{resultSet,name=res2,javaType=net.demo.dto.User}

Explanation:

• ➊ uses rule ➋ to label the update count as upd.
• ➌ uses rule ➍ to label the first result set res1 and map rows to net.demo.dto.User.
• ➎ uses rule ➏ to label the second result set res2 and map rows to net.demo.dto.User.

2. Execute annotated SQL and fetch typed results

String query = "set @userName = convert(? USING utf8);           @{resultUpdate,name=upd}" +
               "select * from test_user where name  = @userName; @{resultSet,name=res1,javaType=net.demo.dto.User}" +
               "select * from test_user where name != @userName; @{resultSet,name=res2,javaType=net.demo.dto.User}";
Map<String, Object> result = jdbcTemplate.multipleExecute(query, "muhammad");

List<User> result1 = (List<User>) result.get("res1"); // statement ➌
List<User> result2 = (List<User>) result.get("res2"); // statement ➎

info

See RESULT rule for more on @{resultSet,...}.

info

• multipleExecute returns a LinkedCaseInsensitiveMap or LinkedHashMap to preserve statement order.
• See Map Case Sensitivity for details.