Skip to main content

Multiple Results

Hint

This article is generated by AI translation.

JdbcTemplate.multipleExecute solves the problem of SQL statements producing multiple result sets that are hard to 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.